scatterplot of data with gaps

[Guest]

Hi,

I'm trying to plot a lot of data with gaps in the columns (empty cells) and
would like to be able to plot the series with points omitted if either x or y
are zero. It seems that Excel, rather than assume that a blank cell is zero,
stops plotting there... Can anyone help me with this?

Even assuming zero values would be better...

Thanks,

Dave

 

[Jon Peltier]

Dave -

In a scatter chart, if X or Y are zero, there is no point plotted for
that data pair. Shouldn't be zero unless you go to Tools menu > Options

Chart tab, and specifically choose to plot empty cells as zero. Of

course, if what you're calling blank is really a formula that returns
"", Excel thinks it's a bit of text, and treats it as zero.

I'm not sure what you mean by "stops plotting there", unless you're
letting Excel auto-select your data range, and both X and Y are missing.
Excel autoselects until it reaches what looks like the end of a given
range. If only X or Y are blank, it selects further, but if both are
balnk, it ends the selection. You can override this wild guess of
Excel's by selecting the whole of the data area manually.

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