Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps) works
only when the cell is actually empty. The cell is empty when raw data is
missing, but the raw data has to be subtracted from a constant value. I'm
using =IF(ISNUMBER(B4), B$1-B4,NA()) which returns #N/A when B4 is blank.
The cell is not empty and consequently is plotted as an interpolated value
between B3 and B5.

I've tried copying and pasting values only and using "" instead of NA() to
no avail.

Short of clearing the contents manually (5% missing data points scattered
throughout 25,000 values) is there another way to leave gaps in the line
chart?

#N/A allows interpolation in a line or XY chart. Only a true blank allows a
gap. "" is text, and so is treated as a data point with value zero, thee
least useful of all.

You could select the data range, use Go To (F5 shortcut key), click Special,
then choose Formulas, the Errors option. This changes the selection to all
errors from formulas in that range. Press Delete to clear these cells.

Of course, if you need to change your data, you'll need to reinstate the
formulas.

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


"Catenary" wrote in message
...
Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps)
works
only when the cell is actually empty. The cell is empty when raw data is
missing, but the raw data has to be subtracted from a constant value. I'm
using =IF(ISNUMBER(B4), B$1-B4,NA()) which returns #N/A when B4 is blank.
The cell is not empty and consequently is plotted as an interpolated value
between B3 and B5.

I've tried copying and pasting values only and using "" instead of NA() to
no avail.

Short of clearing the contents manually (5% missing data points scattered
throughout 25,000 values) is there another way to leave gaps in the line
chart?

Excellent! It sure beats clearing over 1,000 non-contiguous cells by hand.
Thank you.

"Jon Peltier" wrote:

#N/A allows interpolation in a line or XY chart. Only a true blank allows a
gap. "" is text, and so is treated as a data point with value zero, thee
least useful of all.

You could select the data range, use Go To (F5 shortcut key), click Special,
then choose Formulas, the Errors option. This changes the selection to all
errors from formulas in that range. Press Delete to clear these cells.

Of course, if you need to change your data, you'll need to reinstate the
formulas.

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


"Catenary" wrote in message
...
Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps)
works
only when the cell is actually empty. The cell is empty when raw data is
missing, but the raw data has to be subtracted from a constant value. I'm
using =IF(ISNUMBER(B4), B$1-B4,NA()) which returns #N/A when B4 is blank.
The cell is not empty and consequently is plotted as an interpolated value
between B3 and B5.

I've tried copying and pasting values only and using "" instead of NA() to
no avail.

Short of clearing the contents manually (5% missing data points scattered
throughout 25,000 values) is there another way to leave gaps in the line
chart?