Next lower cell with number...

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

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
 
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
 
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---
 
Back
Top