Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort alphabebically ignoring preceeding symbols or numbers | Excel Worksheet Functions | |||
alphabetical sort while ignoring preceeding numbers/symbols | Excel Discussion (Misc queries) | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Ignoring Initial Articles in Field Sort | Excel Discussion (Misc queries) | |||
Averaging noncontiguous numbers ignoring zeros? | Excel Worksheet Functions |