Ignoring 0

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

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

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

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


Thanks,
Shhhh

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
>

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

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
>

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

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