find first non error value in a column

[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. 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

 

[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. Valko]

That will return the *last* numeric value.

Biff

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

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

 

[Dave Peterson]

Yep. You're right.

That will return the *last* numeric value.

Biff

 

[Mika]

Thanks Biff and Dave

Done it!

Mika