Excel 2002 Zero Values in Charts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob E - 18 Aug 2006 09:42 GMT

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.

Signature

Thanks,
Rob E.

Reply to this Message

Andy Pope - 18 Aug 2006 10:50 GMT

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

> Can anyone help please?
>
[quoted text clipped - 7 lines]
>
> Any help would be appreciated.

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message

ggovier - 25 Nov 2006 01:26 GMT

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

> Hi,
>
[quoted text clipped - 21 lines]
> >
> > Any help would be appreciated.

Reply to this Message