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.

On Feb 15, 9:35*am, 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.

What is the formula you are using to cause the error, I don't use
xl'07 but I know it has a function called,
=iferror()
check it out...
http://exceltip.com/st/IFERROR_Funct...007_/1372.html

= IFERROR (yourFormula, "")

Hi,

Next good row after which NA? The first NA, the last NA? Is a blank "good
data", or does it have to be a number or text or eitner or a data or...

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

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.

Here's an example:
1: 3
2:NA
3:NA
4:NA
5:6
6:12
What I actually want to do is interpolate rows 2-4 based on values in a
different column. I think I can do the interpolation part, if I could just
get past the NAs.The problem is that in row 2, I can't figure out how to look
for row 5 to get the next good data point.

The NAs were my creation, so if it would make the function easier, I could
make all the NAs be "" or "BAD". All the other data are numbers.

"Shane Devenshire" wrote:

Hi,

Next good row after which NA? The first NA, the last NA? Is a blank "good
data", or does it have to be a number or text or eitner or a data or...

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

On Sun, 15 Feb 2009 08:35:06 -0800, 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.


=LOOKUP(2,1/(LEN(A:A)0),A:A)

seems to ignore the NA values
--ron

On Sun, 15 Feb 2009 14:39:21 -0500, Ron Rosenfeld
wrote:

On Sun, 15 Feb 2009 08:35:06 -0800, 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.


=LOOKUP(2,1/(LEN(A:A)0),A:A)

seems to ignore the NA values
--ron

Please ignore. I misread your requirements.
--ron

On Sun, 15 Feb 2009 08:35:06 -0800, 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.

Could you use an Advanced Filter, and copy the good values to another range?

For example, with a label in A5 and your data in A6:An

Set up a two row (single column) criteria range someplace
Row1: Blank
Row2: =NOT(ISNA(A6))

(This assumes your NA is the Excel error value of #N/A)

Then select Data/Advanced Filter
List Range: A5:An
Criteria Range: Your two (2) row range as above
Select "Copy to Another Location"
Copy to: desired destination

<OK



--ron

This can get rid of the NAs, but I can do that myself since I put them there
in the first place. What this still won't do is tell me (at any given row
with an NA) what is the next valid data value later down the column.

"Ron Rosenfeld" wrote:

On Sun, 15 Feb 2009 08:35:06 -0800, 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.

Could you use an Advanced Filter, and copy the good values to another range?

For example, with a label in A5 and your data in A6:An

Set up a two row (single column) criteria range someplace
Row1: Blank
Row2: =NOT(ISNA(A6))

(This assumes your NA is the Excel error value of #N/A)

Then select Data/Advanced Filter
List Range: A5:An
Criteria Range: Your two (2) row range as above
Select "Copy to Another Location"
Copy to: desired destination

<OK



--ron

On Sun, 15 Feb 2009 13:00:31 -0800, rocket
wrote:

This can get rid of the NAs, but I can do that myself since I put them there
in the first place. What this still won't do is tell me (at any given row
with an NA) what is the next valid data value later down the column.

Well, I've not seen any information on exactly "how" you want to be "told" this
information. So, like others, I've had to guess.

Seems we've all been guessing wrong.

How about you be more specific as to what you want the results to look like. In
other words, in addition to the data sample you posted, post an example of the
results of the "tell me" operation.
--ron

If your data is in D3:D10, use the following array formula. It will
return the value in the row below that last #N/A error value. Change
the references to D3 and D3:D10 to your cell references.

=OFFSET(D3,MAX(ISNA(D3:D10)*(ROW(D3:D10)))+1-ROW(D3),0,1,1)

Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 15 Feb 2009 08:35:06 -0800, 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.

On Feb 16, 7:38*am, Chip Pearson wrote:
If your data is in D3:D10, use the following array formula. It will
return the value in the row below that last #N/A error value. Change
the references to D3 and D3:D10 to your cell references.

=OFFSET(D3,MAX(ISNA(D3:D10)*(ROW(D3:D10)))+1-ROW(D3),0,1,1)

The way I read rocket's OP is that he/she essentially wants the first
non-#N/A?

On Sun, 15 Feb 2009 08:35:06 -0800, 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.

In which case, perhaps (CSE):

=INDEX(D3:D10,MATCH(FALSE,ISNA(D3:D10),0))

Apologies if I've read it wrong.

Cheers
A

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.

THANK YOU! The responses from Shane Devenshire and Chip Pearson kept giving
me the last #N/A, but your wording of my problem is much clearer. Yes, I need
the FIRST non-NA, and I believe I can make it work using your recommendation
of INDEX and MATCH. Thanks again.


" wrote:

On Feb 16, 7:38 am, Chip Pearson wrote:
If your data is in D3:D10, use the following array formula. It will
return the value in the row below that last #N/A error value. Change
the references to D3 and D3:D10 to your cell references.

=OFFSET(D3,MAX(ISNA(D3:D10)*(ROW(D3:D10)))+1-ROW(D3),0,1,1)

The way I read rocket's OP is that he/she essentially wants the first
non-#N/A?

On Sun, 15 Feb 2009 08:35:06 -0800, 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.

In which case, perhaps (CSE):

=INDEX(D3:D10,MATCH(FALSE,ISNA(D3:D10),0))

Apologies if I've read it wrong.

Cheers
A