Here is one example, an array entered formula that returns the first non-na
after the last na:

=OFFSET(J1,MAX(ISNA(J3:J17)*ROW(J3:J17)),)

Being an array you must enter it using Shift+Ctrl+Enter
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"rocket" wrote:

I have a column in my worksheet with many #N/A entries. I'm looking for a
function which will return the next "good" (non #N/A) data value from a later
row in that column. I want to save that to the row with the bad data, so I
cannot filter out all the NA rows. I've got Excel-2007.

This is a clever way to find all the rows that have an NA, but I think the
MAX function will return the last row with an NA, not the next one. I'll have
an intermediate array like (0,0,0,4,5,0,0,8,9,...). I either need to be able
to restrict the range (which is the problem that I don't know where the next
good data are) or use a "MIN but 0" logic. Almost there...

"Shane Devenshire" wrote:

Here is one example, an array entered formula that returns the first non-na
after the last na:

=OFFSET(J1,MAX(ISNA(J3:J17)*ROW(J3:J17)),)

Being an array you must enter it using Shift+Ctrl+Enter
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"rocket" wrote:

I have a column in my worksheet with many #N/A entries. I'm looking for a
function which will return the next "good" (non #N/A) data value from a later
row in that column. I want to save that to the row with the bad data, so I
cannot filter out all the NA rows. I've got Excel-2007.

Hi,

You should test before you come to a conclusion. The fact is that OFFSET
from J1 is automatically one row below the last NA. Because if the last row
is 15, 15 offset from J1 is J16!

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"rocket" wrote:

This is a clever way to find all the rows that have an NA, but I think the
MAX function will return the last row with an NA, not the next one. I'll have
an intermediate array like (0,0,0,4,5,0,0,8,9,...). I either need to be able
to restrict the range (which is the problem that I don't know where the next
good data are) or use a "MIN but 0" logic. Almost there...

"Shane Devenshire" wrote:

Here is one example, an array entered formula that returns the first non-na
after the last na:

=OFFSET(J1,MAX(ISNA(J3:J17)*ROW(J3:J17)),)

Being an array you must enter it using Shift+Ctrl+Enter
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"rocket" wrote:

I have a column in my worksheet with many #N/A entries. I'm looking for a
function which will return the next "good" (non #N/A) data value from a later
row in that column. I want to save that to the row with the bad data, so I
cannot filter out all the NA rows. I've got Excel-2007.