Next lower cell with number...
 

I have used the LOOKUP function before for such things but I am struggling
for a proper formula, using LOOKUP or otherwise, to grab the value of the
next lower cell in a column without an #N/A value.

So,

#N/A
#N/A
23.65

would return 23.65

Any ideas would be appreciated... my thanks in advance.

Brad

Next lower cell with number...
 

This seems to work
=INDEX(A1:A20,20-COUNT(A1:A20)+1)
but you need numbers in all the cells after the top N cells with N/A

This UDF works when the first number may have N/As or any non-numeric values
after he first number
Function firstnum(myrange)
For Each mycell In myrange
If Not WorksheetFunction.IsNA(mycell) Then
firstnum = mycell.Value
Exit For
End If
Next
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Brad" wrote in message
...
I have used the LOOKUP function before for such things but I am struggling
for a proper formula, using LOOKUP or otherwise, to grab the value of the
next lower cell in a column without an #N/A value.

So,

#N/A
#N/A
23.65

would return 23.65

Any ideas would be appreciated... my thanks in advance.

Brad

Next lower cell with number...
 

That's pretty cool! Here's another, slightly different, method:
=INDEX(A1:A99,MATCH(TRUE,A1:A99<"",0))

Hit Ctrl+Shift+Enter (not just Enter).


Regards,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

This seems to work
=INDEX(A1:A20,20-COUNT(A1:A20)+1)
but you need numbers in all the cells after the top N cells with N/A

This UDF works when the first number may have N/As or any non-numeric values
after he first number
Function firstnum(myrange)
For Each mycell In myrange
If Not WorksheetFunction.IsNA(mycell) Then
firstnum = mycell.Value
Exit For
End If
Next
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Brad" wrote in message
...
I have used the LOOKUP function before for such things but I am struggling
for a proper formula, using LOOKUP or otherwise, to grab the value of the
next lower cell in a column without an #N/A value.

So,

#N/A
#N/A
23.65

would return 23.65

Any ideas would be appreciated... my thanks in advance.

Brad