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


--
Shhhh
------------------------------------------------------------------------
Shhhh's Profile: http://www.excelforum.com/member.php...o&userid=28094
View this thread: http://www.excelforum.com/showthread...hreadid=475985

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


--
Shhhh
------------------------------------------------------------------------
Shhhh's Profile: http://www.excelforum.com/member.php...o&userid=28094
View this thread: http://www.excelforum.com/showthread...hreadid=475985

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


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

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??
:confused:


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


--
Shhhh
------------------------------------------------------------------------
Shhhh's Profile: http://www.excelforum.com/member.php...o&userid=28094
View this thread: http://www.excelforum.com/showthread...hreadid=475985

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