find first non error value in a column

M

Mika

Hi,

I have column filled either with #N/A or a number. I need a formula to
"extract" the first numeric value in that column.
example:

#N/A
#N/A
#N/A
15
#N/A
#N/A
3
#N/A
the answer must be 15.

Thanks for your time.
Mika
 
T

T. Valko

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(COUNT(A1:A8),INDEX(A1:A8,MATCH(TRUE,ISNUMBER(A1:A8),0)),"")

Biff
 
D

Dave Peterson

One way:
=LOOKUP(2,1/ISNUMBER(A1:A100),A1:A100)

Adjust the range to match, but don't use the whole column.
 
T

T. Valko

That will return the *last* numeric value.

Biff

Dave Peterson said:
One way:
=LOOKUP(2,1/ISNUMBER(A1:A100),A1:A100)

Adjust the range to match, but don't use the whole column.
Click to expand...
 
Top