I have a column of between 10 to 50 cells which contain a single number - I need to find the first instance of a 9 from the bottom
I use this formula in column B with a changing COUNTIF function which works well =
A B
4 =IF(AND(A2=9,COUNTIF(A2:A,"=9")=1 , A2 , ""))
6 =IF(AND(A2=9,COUNTIF(A3:A,"=9")=1 , A3 , ""))
7 =IF(AND(A2=9,COUNTIF(A4:A,"=9")=1 , A4 , ""))
6
8
6
9
1
0
3
5
8
9 = 9
This finds the 9 at the very bottom of the column - but now I need a formula which will "ignore" the bottom 10% and look further up to find the second 9 value in the middle...
Would be grateful for any help!
I use this formula in column B with a changing COUNTIF function which works well =
A B
4 =IF(AND(A2=9,COUNTIF(A2:A,"=9")=1 , A2 , ""))
6 =IF(AND(A2=9,COUNTIF(A3:A,"=9")=1 , A3 , ""))
7 =IF(AND(A2=9,COUNTIF(A4:A,"=9")=1 , A4 , ""))
6
8
6
9
1
0
3
5
8
9 = 9
This finds the 9 at the very bottom of the column - but now I need a formula which will "ignore" the bottom 10% and look further up to find the second 9 value in the middle...
Would be grateful for any help!