Excel Help needed

Excel Help needed

Postby Red Devil » Sat Jan 12, 2008 2:42 am

Sorry forgot to attach file

Can anybody help me with a function in Excel, I am having problems with the Match function.

See attached part of a spreadsheet I use with the segment filter, I am trying to use the Match filter in TAB Segment1, in the cells highlighted in "Lilac".

The 1st line works OK (Z32 & AA32) however, when I copy down matches are missed, eg, AA33 misses the fact that V3 should return a value of "1", AA34 misses a return value of "2".

Can anybody see what I am doing wrong,? :wand: :wand:

Red devil
You do not have the required permissions to view the files attached to this post.
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby Bobijohn » Sat Jan 12, 2008 12:53 pm

Hi Red Devil

Not an expert at this but it looks loike you are dividing trying to divide by zero.

Google "Excel division by zero" for a couple of ways to handle the problem. I usually use an if statement so that if the divisor is zero the answer returned is zero.

Hope this helps

bj
Bobijohn
 
Posts: 212
Joined: Tue Feb 10, 2009 3:27 pm

Postby Bobijohn » Sat Jan 12, 2008 1:29 pm

Hi Red Devil

Ooops, Sorry. Saw the # signs and responded to that.

If you just want to count the instances that a number occurs then I would suggest using the "countif" function. It does not try to return the next closest function when an exact match is not found. Just easier to use.

Just trying to help.

bj
Bobijohn
 
Posts: 212
Joined: Tue Feb 10, 2009 3:27 pm

Postby Red Devil » Sat Jan 12, 2008 6:34 pm

BJ,

I'm trying to find the last occurence in a list, (skip). Normally I can get the match function to work but sometimes like in this case it misses out occurrences.

Thanks anyway

Red Devil
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Skips Formula

Postby CARBOB » Sat Jan 12, 2008 8:09 pm

Red Devil, take a look and let me know if this is what you need.
You do not have the required permissions to view the files attached to this post.
CARBOB
 
Posts: 874
Joined: Thu Jun 29, 2006 10:34 pm

Postby CARBOB » Sun Jan 13, 2008 1:35 am

Red Devil, disregard my previous post. I re-read your initial post and realize that the formula was not counting correct and neither is mine. There is something not right with the column. The formula is skipping the first 15 rows in that column. Column T works right.
CARBOB
 
Posts: 874
Joined: Thu Jun 29, 2006 10:34 pm

Postby Red Devil » Sun Jan 13, 2008 2:24 am

Carbob,

Yes it's strange, I find sometimes that the match function in Excel misses or disregards some matches but, normally it is due to the column that you are referring to is the result of another function or formula, but in this case you can see that I have actually inputted the "value" manually. A friend of mine who is a bit of a whizz with Excel says that he has seen this before and has no answer for it??? :wand:

Sysp34 you are a bit of an Excel whizz, have you any ideas??

Red Devil
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby CARBOB » Sun Jan 13, 2008 3:12 pm

[quote=Red Devil]
Carbob,

Yes it's strange, I find sometimes that the match function in Excel misses or disregards some matches but, normally it is due to the column that you are referring to is the result of another function or formula, but in this case you can see that I have actually inputted the "value" manually. A friend of mine who is a bit of a whizz with Excel says that he has seen this before and has no answer for it??? :wand:

Sysp34 you are a bit of an Excel whizz, have you any ideas??

Red Devil
[/quote]

He's a lot more knowledgeable than I!!! I've attached the spreadsheet I use to track Segment filters. Feel free to ask questions.
You do not have the required permissions to view the files attached to this post.
CARBOB
 
Posts: 874
Joined: Thu Jun 29, 2006 10:34 pm

Postby Red Devil » Sun Jan 13, 2008 5:52 pm

Bob,

I'm OK with the basic Excel functions, compounding them however, I begin to get a bit lost. It's interesting though I will look at some of the formulas in your sheet and see if I can work out how they operate.

The SUMPRODUCT function you use is good for calculating repeats form 1 row to another. Where do you find out the bit about adding the -- and =. The Help with Excel does not talk about it.

Thanks

Red Devil
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby sysp34 » Sun Jan 13, 2008 7:27 pm

[quote=Red Devil:1200098554]
Sorry forgot to attach file

Can anybody help me with a function in Excel, I am having problems with the Match function.

See attached part of a spreadsheet I use with the segment filter, I am trying to use the Match filter in TAB Segment1, in the cells highlighted in "Lilac".

The 1st line works OK (Z32 & AA32) however, when I copy down matches are missed, eg, AA33 misses the fact that V3 should return a value of "1", AA34 misses a return value of "2".

Can anybody see what I am doing wrong,? :wand: :wand:

Red devil
[/quote]

hello RD

it seem the problem on col. V i assume you input manually or copy and paste in value format

and on cell z32:z38 you input a number format as a result you will get a "wrong" skip numbers

Rpt
0 29
1 16
2 15
3 19
4 17
5 20
6 106

change value in cell z32:z38 with text format (right click - format cell -> text) and you get this result

Rpt
0 #N/A
1 2
2 1
3 7
4 3
5 4
6 #N/A

is it what you're looking for? also if you want to see 0 value which is lastest skip change formula =MATCH(Z32,V:V,0)-1 with =MATCH(Z32,V:V,0)-2 the result become like this
Rpt
0 #N/A
1 1
2 0
3 6
4 2
5 3
6 #N/A
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am

Postby Red Devil » Sun Jan 13, 2008 8:39 pm

I've tried changing the cell format as you suggested but it still does not change. In fact I've tried all combinations of cell format on all cells and still can't get it right. :wand:
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby Red Devil » Sun Jan 13, 2008 9:21 pm

Ragwi, Bob

It's working now, I use the SUMPRODUCT function to enter the column "Rpts" and now it works OK.

Thanks
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby sysp34 » Sun Jan 13, 2008 9:21 pm

[quote=Red Devil]
I've tried changing the cell format as you suggested but it still does not change. In fact I've tried all combinations of cell format on all cells and still can't get it right. :wand:
[/quote]

RD

is it like this spreadsheet? (from your's spreadsheet})
You do not have the required permissions to view the files attached to this post.
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am

Postby Red Devil » Sun Jan 13, 2008 9:25 pm

Yes I formatted exactly like that but it did not work, I have had this in the past with other sheets, the only way I could fix them was to rebuild them from scratch.
Red Devil
 
Posts: 348
Joined: Tue Apr 25, 2006 3:26 pm

Postby sysp34 » Sun Jan 13, 2008 9:26 pm

[quote=Red Devil]
Ragwi, Bob

It's working now, I use the SUMPRODUCT function to enter the column "Rpts" and now it works OK.

Thanks
[/quote]

RD

i think there are different cell format on column Rpts, and glad sumproduct solve your problem, and usually i'll try to create in common formula and thank to CARBOB who's enlighten me how to use common formula :-)
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am

Next

Return to Tips + Tricks

Who is online

Users browsing this forum: No registered users and 1 guest

cron