Refer to adjacent cells as variables in formula

[Dominic W]

In a large column of data some cells randomly have entries of "no data"-
(caused by a comms failure at that time).
I'd like to interpolate data for these cells by filling them with actual
values in cells either above or below them.
Can I create a formula that refers to cells either immediately above or
below the cells?
My problem is that I don't know how to refer to a cell as 'the cell above
this one' or 'the cell below this one', as a variable instead of as a
specific cell location.

If I do find out how to refer to cells like this I suppose that I'll have to
make the full formula an 'If' statement- the logic being: "If cell xx ="No
Data" then cellxx = cell above cellxx"

Thanks very much for any help, Dominic
Excel version: Office 2000

 

[Gary''s Student]

Pick a cell and enter:

=INDEX($1:$65536,ROW()-1,COLUMN()) for the value in the row above

and

=INDEX($1:$65536,ROW()+1,COLUMN()) for the value in the row below

You could use a macro to find the blanks and fill them in

 

[Rick Rothstein \(MVP - VB\)]

You can't do what you want using worksheet formulas because a cell cannot
contain both text ("No Data") and a formula at the same time. You will need
to use a macro to do what you want. What do you want to happen if you have
several contiguous "No Data" cells in your column (think, say, 100 one after
the other in your column)... copy the same data in each? Also, which do you
prefer for the copy... from the cell above or from the cell below? And, so
we can target our code to your exact needs, which column is your data in?

Rick

 

[Dominic W]

Thanks to Rick and Gary''s Student for replies so far. I may end up doing
this manually depending on how many cells need correcting due to time
restraints but if I need more help will get back. Rick is right to comment
that many adjacent cells may have 'No Data' but in fact generally they are
often isolated or otherwise two or maybe three maximum cells adjacent in the
column only.

Dominic


:

You can't do what you want using worksheet formulas because a cell cannot
contain both text ("No Data") and a formula at the same time. You will need
to use a macro to do what you want. What do you want to happen if you have
several contiguous "No Data" cells in your column (think, say, 100 one after
the other in your column)... copy the same data in each? Also, which do you
prefer for the copy... from the cell above or from the cell below?
And, so we can target our code to your exact needs, which column is your
data in?

Rick

"Dominic W" <Dominic (e-mail address removed)> wrote in message news[email protected]...

In a large column of data some cells randomly have entries of "no data"-
(caused by a comms failure at that time).
I'd like to interpolate data for these cells by filling them with actual
values in cells either above or below them.
Can I create a formula that refers to cells either immediately above or
below the cells?
My problem is that I don't know how to refer to a cell as 'the cell above
this one' or 'the cell below this one', as a variable instead of as a
specific cell location.

If I do find out how to refer to cells like this I suppose that I'll have to
make the full formula an 'If' statement- the logic being: "If cell xx ="No
Data" then cellxx = cell above cellxx"

Thanks very much for any help, Dominic
Excel version: Office 2000

 

[Lori]

To fill the missing values based on cells above and below, try this...

Under Tools Options, in the General tab choose R1C1 Refence Style and in the
calculation tab choose iteration with max change 0.

Then do Edit Replace "No Data" with "=average(r[-1]c,r[1]c)"

Paste values and restore the option settings after.