Excel 2002 Zero Values in Charts

[Rob E]

Can anyone help please?

I have a column in my woksheet that contains formulae that refers to blank
cells. Therefore the result is zero in each cell in my column. When I use
the data in a chart, I set Tools, Options, Chart tab - plot empty cells as
"Not plotted", but it still plots the cells as zero and distorts my chart.

Is there any way to get is to plot the cells that have a formula where the
result is zero, as "not plotted"?

Any help would be appreciated.

 

[Andy Pope]

Hi,

As far as a chart is concerned a cell with a formula in it is not an
empty cell. Even if the result of the formula appears to be empty.

Rather than the formula returning zero or "" try NA().

The chart will then not plot a point for that cell. It will however
interpolate between the valid point either side. So you will not get a
break in the line, if you are plotting lines.

Cheers
Andy

 

[ggovier]

I'm posting this in several chains...
Tools -> Options -> Chart -> Plot empty cells as: Not plotted (leave gaps).
Should work for "" cells don't you think, it doesn't on Excel XP 2002.
GG