MS Excel does not interpolate empty cells
 

Trying to plot a contour map of a film thickness. The substarte is round.
X/Y coordinates are not in a grid so the data table has several empty cells.
Trying to use 'Tool/Options...', 'Chart' tab and selecting the 'Interpolate'
radio button does nothing. The chart treats the empty cells as zero value.
If it would interpolate I would be happy.

I also tried this with a grid (square) X/Y table with a couple of empty
cells and again, it did not interploate.

MS Excel does not interpolate empty cells
 

If the cell contains a formula like =IF(A1=0,"",A1), then that cell is
neither empty nor blank. It contains a formula, and the formula returns a
string, "", which while rather short is still a value.

Change "" in the formula to NA(), which produces #N/A in the cell. While
somewhat ugly in the table, it does not appear in a line or XY chart. You
can conditionally format the cell to hide the error.

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"wlawler2303" wrote in message
...
Trying to plot a contour map of a film thickness. The substarte is round.
X/Y coordinates are not in a grid so the data table has several empty
cells.
Trying to use 'Tool/Options...', 'Chart' tab and selecting the
'Interpolate'
radio button does nothing. The chart treats the empty cells as zero
value.
If it would interpolate I would be happy.

I also tried this with a grid (square) X/Y table with a couple of empty
cells and again, it did not interploate.

MS Excel does not interpolate empty cells
 

chart In a similar case, I have elevation (or other points) at various cells
around a spreadsheet roughly corresponding the XY coordinates in a
rectangular grid. Some of the cells have heights but not all do. When I
create a surface the "empty" cells all act as zero. That doesn't surprize
me, but I'd like to try to overcome the problem and get a surface represented
by the "real" data.

1) Any way I can have Excel not include the zeros? Can I set them to NA()
to do this, with data in the known places? The intent would be to have it
create the surface/contours based only on the known values, ignoring the
empty cells.

2) Using, say, the Solver, can I get Excel to fill in the missing elevations
(empty cells) based on the surrounding known values? Of course, doing this
would require a surface fit in both X & Y directions, a non-trivial
undertaking. Maybe Excel has a function that might do the trick instead of
me having to write it?!

"Jon Peltier" wrote:

If the cell contains a formula like =IF(A1=0,"",A1), then that cell is
neither empty nor blank. It contains a formula, and the formula returns a
string, "", which while rather short is still a value.

Change "" in the formula to NA(), which produces #N/A in the cell. While
somewhat ugly in the table, it does not appear in a line or XY chart. You
can conditionally format the cell to hide the error.

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"wlawler2303" wrote in message
...
Trying to plot a contour map of a film thickness. The substarte is round.
X/Y coordinates are not in a grid so the data table has several empty
cells.
Trying to use 'Tool/Options...', 'Chart' tab and selecting the
'Interpolate'
radio button does nothing. The chart treats the empty cells as zero
value.
If it would interpolate I would be happy.

I also tried this with a grid (square) X/Y table with a couple of empty
cells and again, it did not interploate.

MS Excel does not interpolate empty cells
 

Whenever I've had to do this I've manually adjusted the values in the blank
cells myself. Only charts that use markers (line/XY/radar) interpolate over
#N/A.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"NormD" wrote in message
...
chart In a similar case, I have elevation (or other points) at various
cells
around a spreadsheet roughly corresponding the XY coordinates in a
rectangular grid. Some of the cells have heights but not all do. When I
create a surface the "empty" cells all act as zero. That doesn't surprize
me, but I'd like to try to overcome the problem and get a surface
represented
by the "real" data.

1) Any way I can have Excel not include the zeros? Can I set them to NA()
to do this, with data in the known places? The intent would be to have it
create the surface/contours based only on the known values, ignoring the
empty cells.

2) Using, say, the Solver, can I get Excel to fill in the missing
elevations
(empty cells) based on the surrounding known values? Of course, doing
this
would require a surface fit in both X & Y directions, a non-trivial
undertaking. Maybe Excel has a function that might do the trick instead
of
me having to write it?!

"Jon Peltier" wrote:

If the cell contains a formula like =IF(A1=0,"",A1), then that cell is
neither empty nor blank. It contains a formula, and the formula returns a
string, "", which while rather short is still a value.

Change "" in the formula to NA(), which produces #N/A in the cell. While
somewhat ugly in the table, it does not appear in a line or XY chart. You
can conditionally format the cell to hide the error.

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"wlawler2303" wrote in message
...
Trying to plot a contour map of a film thickness. The substarte is
round.
X/Y coordinates are not in a grid so the data table has several empty
cells.
Trying to use 'Tool/Options...', 'Chart' tab and selecting the
'Interpolate'
radio button does nothing. The chart treats the empty cells as zero
value.
If it would interpolate I would be happy.

I also tried this with a grid (square) X/Y table with a couple of empty
cells and again, it did not interploate.