Ignoring 0

[Shhhh]

Hello all,

Is it possible to create a chart using an entire column, but have th
chart
ignore all 0 values? i.e. simply not plot the 0 values.

Thanks,
Shhh

 

[Barb Reinhardt]

Are your zero values the result of a formula?

Let's say you have
A1=B1+C1 which equals Zero

Change your formula to the following:
=if(b1+c1=0,NA(),b1+c1)

 

[Shhhh]

Thanks for your reply Barb,

the "Y" axis is dates... and the "X" axis is the column with data on
it. I have the chart set up for the whole year and every day that days
"0" value is replaced with a number.

So basically my chart shows data and then all of a sudden falls all the
way to 0 from tomorrows date on.

Any other suggestions?

Thanks again,
Shhhh

 

[Shhhh]

Please folks... I'm desparate... any other suggestions out there??



Thanks,
Shhhh

 

[Ed Ferrero]

Hi Shhhh,

Have you tried selecting the chart and using the menu command
Tools-Options-Chart and plot empty cells - not plotted?

Ed Ferrero
http://edferrero.m6.net/

 

[Shhhh]

Ed,

Thanks for your reply... yes I tried all three options available and
still whenever the data has a 0 the chart just falls off a cliff.

Any other thoughts?


Thanks again,
Shhhh

 

[Ed Ferrero]

What type of chart are you using? Area? Scatter? Line?

Ed Ferrero
http://edferrero.m6.net/

 

[Jon Peltier]

Or more generally, if the zeros are in column B, insert a new column C
and use this formula:

=IF(B2=0,NA(),B2)

Now make the chart with column C instead of B.

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

 

[Nigel]

You could always write a macro that replace all the cells with a value of 0