MS Excel does not interpolate empty cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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?!
 
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/
_______
 
Back
Top