Ignoring 0

[Shhhh]

Hello all,

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

Thanks,
Shhhh

[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" wrote:

>
> Hello all,
>
> Is it possible to create a chart using an entire column, but have the
> chart
> ignore all 0 values? i.e. simply not plot the 0 values.
>
> Thanks,
> Shhhh
>
>
> --
> Shhhh
> ------------------------------------------------------------------------
> Shhhh's Profile: http://www.excelforum.com/member.php...o&userid=28094
> View this thread: http://www.excelforum.com/showthread...hreadid=475985
>
>

[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/

>
> Please folks... I'm desparate... any other suggestions out there??
>
>
>
> Thanks,
> Shhhh
>
>
> --
> Shhhh
> ------------------------------------------------------------------------
> Shhhh's Profile:
> http://www.excelforum.com/member.php...o&userid=28094
> View this thread: http://www.excelforum.com/showthread...hreadid=475985
>

[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/

>
> 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
>
>
> --
> Shhhh
> ------------------------------------------------------------------------
> Shhhh's Profile:
> http://www.excelforum.com/member.php...o&userid=28094
> View this thread: http://www.excelforum.com/showthread...hreadid=475985
>

[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/
_______


Barb Reinhardt wrote:

> 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" wrote:
>
>
>>Hello all,
>>
>>Is it possible to create a chart using an entire column, but have the
>>chart
>>ignore all 0 values? i.e. simply not plot the 0 values.
>>
>>Thanks,
>>Shhhh
>>
>>
>>--
>>Shhhh
>>------------------------------------------------------------------------
>>Shhhh's Profile: http://www.excelforum.com/member.php...o&userid=28094
>>View this thread: http://www.excelforum.com/showthread...hreadid=475985
>>
>>

[Nigel]

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

"Shhhh" wrote:

>
> Hello all,
>
> Is it possible to create a chart using an entire column, but have the
> chart
> ignore all 0 values? i.e. simply not plot the 0 values.
>
> Thanks,
> Shhhh
>
>
> --
> Shhhh
> ------------------------------------------------------------------------
> Shhhh's Profile: http://www.excelforum.com/member.php...o&userid=28094
> View this thread: http://www.excelforum.com/showthread...hreadid=475985
>
>