Don't plot non-numeric cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Catenary - 13 May 2008 00:38 GMT

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?

Reply to this Message

Jon Peltier - 13 May 2008 00:53 GMT

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

> Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps)
> works
[quoted text clipped - 10 lines]
> throughout 25,000 values) is there another way to leave gaps in the line
> chart?

Reply to this Message

Catenary - 13 May 2008 01:04 GMT

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

> #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
[quoted text clipped - 28 lines]
> > throughout 25,000 values) is there another way to leave gaps in the line
> > chart?

Reply to this Message