ExpertLotto.com

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

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

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

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, take a look and let me know if this is what you need.

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,

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=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.

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

[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

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:

Ragwi, Bob

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

Thanks

[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})

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.

[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