Second To Last Value Greater Than Zero In A Column

[anwaee2]

I need help again. I have a range of values in range B6:B42. There are some blank cells and the values are not ascending or descending. They are mixed. I need to find the second to last value that is greater than zero in that column ignoring any of the blank cells. Sometimes there might be two or three blank cells between the values. I am not opposed to using a helper column if necessary.
Thanks to all in advance. This forum has been the best and very appreciatived.

 

[James006]

Hi,

You can following Array Formula :

=Index(B6:B42,Large(If(B6:B42<>"",Row(B6:B42)),2))

Hope this will help

 

[anwaee2]

Thank you for your reply. Unfortunately I get a Zero with this formula. The same result I have gotten with other formulas too. Below is a small sample of my column "B". The answer I am would like to get is 184, the second value greater than zero from the bottom.
[TABLE="width: 66"]
<colgroup><col width="66" style="width: 66pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=963634]#963634[/URL] , align: center"]Weight[/TD]
[/TR]
[TR]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]189.0[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]187.0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]188.0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]185.0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]184.0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]183.0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]184.0[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]180.0[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Try

Excel 2013/2016
	B	C
5	Weight	184
6
7	189
8
9	187
10	188
11	185
12	184
13	183
14	184
15
16	180
Sheet3

Cell Formulas
Range	Formula
C5	{=INDEX(B6:B42,LARGE(IF(B6:B42<>"",ROW(B6:B42)-ROW(B6)+1),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

Hi,

I like the INDEX formula above better, just throwing this in for fun.

Normally entered:

Book1
	B	C
5	Weight
6		184
7	189
8
9	187
10
11
12	188
13	185
14	184
15
16	183
17	184
18
19
20
21
22	180
23
Sheet473

Cell Formulas
Range	Formula
C6	=LOOKUP(2,1/INDIRECT("B6:B"&LOOKUP(2,1/B6:B42,ROW(B6:B42))-1),B6:B42)

 

[anwaee2]

Thanks to Fluff and jtakw, both works exactly as I need. Once again this forum comes through. As I said before, this forum is the best for expertise help.

 

[jtakw]

You're welcome, glad to help.