Finding The "Second" Value From Bottom Of Column...

[Aitch]

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!

 

[Fluff]

If you want to find the penultimate 9, try
=IF(AND(A2=9,COUNTIF(A2:A$50,"9")=2 ), A2, "")

 

[Aitch]

If you want to find the penultimate 9, try
=IF(AND(A2=9,COUNTIF(A2:A$50,"9")=2 ), A2, "")

Thanks for the reply!

This finds the penultimate constantly - is there a way to make it show the 2nd to last, only if the 1st last is in the bottom 10% ?

 

[Fluff]

What do you mean by "the bottom 10%"?
Also what should happen if the last 9 is not in the bottom 10%?

 

[Aitch]

What do you mean by "the bottom 10%"?
Also what should happen if the last 9 is not in the bottom 10%?

I mean if there are 20 rows of values in column A - it ignores any 9's in the bottom 10%, so rows 19 and 20 are not considered

If the last 9 is not in the bottom 10%, then it functions as normal and highlights that one

Really appreciate your help Fluff!

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B
1
2	5
3	10
4	1
5	6
6	6
7	9
8	9
9	5
10	8
11	8
12	8
13	5
14	8
15	4
16	5
17	6
18	7
19	1
20	3
21	8
22	9	9
23	8
24	7
25	6
26	10
27	10
28	6
29	1
30	8
31	9
32
33
Main

Cell Formulas
Range	Formula
B2:B31	B2	=IF(AND(A2=9,COUNTIF(A2:INDEX($A$2:$A$50,COUNT($A$2:$A$50)*0.9),"9")=1,ROW()<=COUNT($A$2:$A$50)*0.9), A2, "")

 

[Aitch]

Awesome!

Am I doing something wrong in copying it down - it seems to break sometimes?

In a column of 10 like this:
1
9
1
9
1
1
1
9 9
1
1
It works perfectly showing the 8th 9

But if there is another 9 below it:
1
9
1
9
1
1
1
9
9
1
It shows nothing - instead of the 9th 9?

 

[Fluff]

How about

=IF(AND(A2=9,COUNTIF(A2:INDEX($A$2:$A$50,COUNT($A$2:$A$50)*0.9),9)=1,ROW(A1)<=COUNT($A$2:$A$50)*0.9), A2, "")