Refer to adjacent cells as variables in formula
 

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

Refer to adjacent cells as variables in formula
 

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

--
Gary''s Student - gsnu200795


"Dominic W" wrote:

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

Refer to adjacent cells as variables in formula
 

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 wrote in message
...
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

Refer to adjacent cells as variables in formula
 

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


"Rick Rothstein (MVP - VB)" wrote:

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 wrote in message ...

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

Refer to adjacent cells as variables in formula
 

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.


"Dominic W" wrote:

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


"Rick Rothstein (MVP - VB)" wrote:

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 wrote in message ...

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