2 Under 2, 3 Under 3, 4 Under 4, Etc in Range with Blanks

[rob51852]

Hello


I have a range of data in O5:O45 with some cells being blank.


Is it possible to come up with a formula that ignores and blank cells and looks for instances where there are 2 values under 2, 3 under 3, 4 under 4 and 5 under 5 and return a 2, 3, 4 or 5 as appropriate?


Thank you

 

[oldbrewer]

1, 1, blank, 2,2,1,2,2,2,2 gives 2 under 2 in first 2, 3 under 3 in first 4 incl blank, 3 under 3 in first 5 etc

how do you want them marked, in another column, coloured ?

 

[FormR]

Is it possible to come up with a formula that ignores and blank cells and looks for instances where there are 2 values under 2, 3 under 3, 4 under 4 and 5 under 5 and return a 2, 3, 4 or 5 as appropriate?

Hi, if I've correctly understood, here is one option you can try:

=INDEX({5,4,3,2},MATCH(TRUE,(COUNTIFS(O5:O45,{"<5","<4","<3","<2"})>{4,3,2,1}),0))

 

[rob51852]

Thanks, that works perfectly