Count last 3 results only

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

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)

Hello,

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

=--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),1))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),2))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),3))>=3)

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

=IFERROR(--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),1))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),2))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),3))>=3),0)

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.

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

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?

a longish non array aproach

Formula:

=IF(AND(INDEX(A3:O3,LARGE(INDEX((A3:O3<>"")*(MOD(COLUMN(A3:O3),8)<>0)*COLUMN(A3:O3),0),1))>2,INDEX(A3:O3,LARGE(INDEX((A3:O3<>"")*(MOD(COLUMN(A3:O3),8)<>0)*COLUMN(A3:O3),0),2))>2,INDEX(A3:O3,LARGE(INDEX((A3:O3<>"")*(MOD(COLUMN(A3:O3),8)<>0)*COLUMN(A3:O3),0),3))>2),1,0)

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

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!

maybe just
nope not that

Hi guys,
Alternative array option may be shorter alot:

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

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!