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