MS Excel does not interpolate empty cells

[Guest]

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.

 

[Jon Peltier]

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

 

[Guest]

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]

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/
_______