How to select the second non zero/blank value in a range?

[Addenum]

Here is an example row of data, with 8 values:

0,[blank],0,1,0,0,[blank],5

We are trying to find a formula that finds the second non zero or non blank value, which in this case is 5. (Ideally this should also be able to be configured to find the third or later non zero non blank value too).

Does anyone have any solution to this?

 

[Akashwani]

Hi and welcome to MrExcel.

This may not be the best solution, but, does this work for you?.....

Sample data and example result with a "Helper" column.

Excel Workbook
	A	B	C	D	E	F
1	Data	Helper			Result
2	0			2	5
3
4	0
5	1	1
6	0
7	0
8
9	5	2
10
Sheet6

The formula in B2 needs to be copied down.
You will need to change the cell references to suit your layout.

You can put any number you like in cell D2 to return the Nth number that is not zero of blank.

I hope that helps.

Ak

 

[apo]

Hi..

I know you asked for a formula.. but in case you decide you need a VBA solution...

Private Sub CommandButton1_Click()
Dim i As Long, Cnt As Long, LastCol As Long
LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
Cnt = 0
For i = 1 To LastCol
If Cells(1, i).Value <> 0 And Cells(1, i).Value <> "" Then
Cnt = Cnt + 1
End If
If Cnt = 2 Then
Cells(5, 2).Value = Cells(1, i).Value
End If
Next i
End Sub

Sheet1

*	A	B	C	D	E	F	G	H
1	0	*	0	1	0	0	*	5
2	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*
5	Result	5	*	*	*	*	*	*

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

btw.. anyone know why Excel Jeanie puts an asterix where blank spaces are for me but not for Akashwani?

 

[Akashwani]

Hi apo,

You get asterix when you have edited your comment. I delete and repaste my data to avoid the dreaded *.

Ak

 

[apo]

Thanks Akashwani.. that's been annoying me for a while now..

 

[raj2206]

Hey Thanks for writing:
I have your data in cells A1:A7, and in any other cell you can use the following formula:

=IF(SUM(--((A1:A7)>0)*ROW(A1:A7))=2,INDEX(A1:A7,LARGE(((A1:A7)>0)*ROW(A1:A7),1)),INDEX(A1:A7,LARGE(((A1:A7)>0)*ROW(A1:A7),2)))

Please make sure you press Control + Shift + Enter while entering the formula since this is an array operation.

Hope this helps.

Thanks/Raj