Count last 3 results only

[junada0]

I have a table of results and would like to check the last 3 known positions across a row of data excluding a few columns and provide an answer on the results if finishing in 1st or 2nd place.

Sample spreadsheet enclosed

Answer / formula required in column "Q"
Look for the last 3 results in each row counting backwards and excluding columns "H" and "P"
and if either a 1st or 2nd place then show "0" any result which is 3 or higher in last 3 results then show "1"

Thanks in anticipation.

Junada0

[bebo021999]

Let see row #5, the last 3 results finished at 9th position, why it is showed "0"?

[MarvinP]

Hi Juanada

In Q3 put this formula and pull down.

=IF(LEN(SUBSTITUTE(SUBSTITUTE(RIGHT(A3&B3&C3&D3&E3&F3&G3&I3&J3&K3&L3&M3&N3&O3,3),1,""),2,""))=3,1,0)

[Lemice]

Hello,

I managed to come up with an Array formula which is painfully long and not elegant at all ...

Please Login or Register  to view this content.

I have also attached your sample file with it

Edit: To avoid having #NUM! error when you have less than 3 values, use this one instead

Please Login or Register  to view this content.

Thus it will always be 0 when there are less than 3 numbers to check.
This one also works in all other cases that the formula above can.

[junada0]

Hi,

Each row from #3 to #8 are player results, not sure how you read 9th position

The last 3 results in row#5 are 3,5 and 2 so answer should be "0" as a second place was scored in the players last 3 results.

If any player scores a 1st or 2nd place in the last 3 results then answer should be "1"

Hope that helps.

[Lemice]

Marvin, I really really like your solution, it was a good one.

[martindwilson]

a longish non array aproach

Please Login or Register  to view this content.

marvinp
=IF(LEN(SUBSTITUTE(SUBSTITUTE(RIGHT(A3&B3&C3&D3&E3&F3&G3&I3&J3&K3&L3&M3&N3&O3,3),1,""),2,""))=3,1,0) falls over if last 3 scores are say 11,6,7

[MarvinP]

Thanks - It will only work if there are single digit numbers in the cells. Because the possible places are 1 to 8 then it should work. I looked at possible formulas of Lookup, Index and Array and all seemed to hard. I went with a string/text function because the Right last 3 characters seemed to work for this problem. I wonder what happens if there are only two numbers or perhaps no numbers? Will my formula or yours still work?

[Lemice]

My formula will work as long as all of the values are number, and there are more than 2 (I should put an IFERROR in front of it).
But really, I like the logic in your formula, really simple and clear!

[martindwilson]

maybe just
nope not that

[junada0]

Hii Guys,

Really appreciate all your help, the array solution really works for me as some of the results could be higher than 8, I did not diplay any higher results as in trying to keep the question simple, sorry for any confusion and again many thanks.

Junada0

[bebo021999]

Hi guys,
Alternative array option may be shorter alot:

Please Login or Register  to view this content.

[junada0]

Hi bebo,

I copied your solution to cell Q3 but got an error "#value!", not sure where I went wrong, would also like to understand how the formula works in case i need to change any values when I copy to anothe sheet with different cell positions and maybe extra columns etc.

Junada0

[bebo021999]

This is an array formula and must be confirmed by Ctrl-Shift-Enter.
Sharing my idea of bulding formula:
1) IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0): ={1,2,3,4,5,6,7,0,8,9,10,11,12,13,14}: returns weekdays which showed in row #2, where "count" says 0
2) (a) IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0)*($A3:$O3>0):{1,2,3,0,0,0,0,0,8,9,10,11,12,0,14}: returns weekdays where results of line #3 >0
3) LARGE (a,{1,2,3}):{14,12,11}: returns 3 largest weekdays (from the right, offcourse)
4)b) LOOKUP(LARGE (a,{1,2,3}),$A$2:$O$2,A3:O3)={7,6,8}: returns 3 scores (of 3 largest weekdays)
5)LOOKUP(LARGE (a,{1,2,3}),$A$2:$O$2,A3:O3)>2 = {1,1,1}. If all>2 (=all=1), SUM =3=> results 1
The final:
=(SUM(1*(LOOKUP(LARGE(IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0)*($A3:$O3>0),{1,2,3}),$A$2:$O$2,A3:O3)>2))=3)*1

Hope this helps!

[junada0]

Great, thanks for the explanation, this helps me understand more.

Thank you.