How to find the first cell not an error

**BNCOXUK** · 11-01-2012, 08:00 AM

Hi, Sir,

A column of my data has all #N/A for the first few knows, and then followed by good numeric numbers. Can I ask how to find the first row with a proper value?

The column looks like:

#N/A
#N/A
#N/A
...
10.08 -> which row?
20.70

**wayneg** · 11-01-2012, 08:37 AM

there is a isna formula in excel

you could use something to highlight it

=IF(ISNA(A1),"",A1) or put it in to conditional formatting and make it highligh a different colour

**BNCOXUK** · 11-01-2012, 08:38 AM

Originally Posted by wayneg

there is a isna formula in excel

you could use something to highlight it

=IF(ISNA(A1),"",A1) or put it in to conditional formatting and make it highligh a different colour

Is there a better way to do it? I just want to identify which cell comes first with a non-error value?

**benishiryo** · 11-01-2012, 08:42 AM

hi BNCOXUK, what is the "..."? is it an actual text? i excluded it & used the 3 N/As & the 2 numbers. try copying this formula & paste into the formula bar:
=MIN(IF(NOT(ISNA(A1:A5)),ROW(A1:A5)))

press CTRL + SHIFT + ENTER to confirm. that's the row number. if you need the actual number, try:
=INDEX(A1:A5,MIN(IF(NOT(ISNA(A1:A5)),ROW(A1:A5))))

same thing. CTRL + SHIFT + ENTER

**Jakobshavn** · 11-01-2012, 08:54 AM

Say your data is in column A, use the array formula:

=MATCH(TRUE,ISNUMBER(A:A),0)

This must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

**BNCOXUK** · 11-01-2012, 09:06 AM

Originally Posted by Jakobshavn

Say your data is in column A, use the array formula:

=MATCH(TRUE,ISNUMBER(A:A),0)

This must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

Hi Gary, this really looks a magic. Well learned! Thanks a lot.