| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip the Zeros in a chart
How can I have the chart skip Zero values?
I am using the formula below to leave the cell blank for values that are zero, but it still shows up in the chart messing up the line. Is there a chart setting for this or do I need to change the formula? =IF(N454=0,"",N454*4+150000) -- Thank you! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip the Zeros in a chart
In Excel 2003 and older, select the chart, Tools, Options, Chart, Plot empty
cells as: Not plotted (leave gaps), or maybe you might prefer Interpolated. In Excel 2007 search "Not plotted" in help Regards, Peter T "Doug" wrote in message ... How can I have the chart skip Zero values? I am using the formula below to leave the cell blank for values that are zero, but it still shows up in the chart messing up the line. Is there a chart setting for this or do I need to change the formula? =IF(N454=0,"",N454*4+150000) -- Thank you! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip the Zeros in a chart
I have 2007. I followed the instructions, but it didn't work. If I clear the
empty formula values, the graph looks fine. I just tried having it return a zero and it is still not working =IF(N454=0,"0",N454*4+150000) -- "Peter T" wrote: In Excel 2003 and older, select the chart, Tools, Options, Chart, Plot empty cells as: Not plotted (leave gaps), or maybe you might prefer Interpolated. In Excel 2007 search "Not plotted" in help Regards, Peter T "Doug" wrote in message ... How can I have the chart skip Zero values? I am using the formula below to leave the cell blank for values that are zero, but it still shows up in the chart messing up the line. Is there a chart setting for this or do I need to change the formula? =IF(N454=0,"",N454*4+150000) -- Thank you! . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip the Zeros in a chart
Not Plotted and Interpolated are only applied to actual blank cells, not
cells which have formulas that return "". The best you can do is change "" in the formula to NA(). This looks like #N/A in a cell, which looks ugly, but in a line or XY chart is not plotted. Instead, the connecting line is interpolated across the space where the point would be plotted. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Peter T wrote: In Excel 2003 and older, select the chart, Tools, Options, Chart, Plot empty cells as: Not plotted (leave gaps), or maybe you might prefer Interpolated. In Excel 2007 search "Not plotted" in help Regards, Peter T "Doug" wrote in message ... How can I have the chart skip Zero values? I am using the formula below to leave the cell blank for values that are zero, but it still shows up in the chart messing up the line. Is there a chart setting for this or do I need to change the formula? =IF(N454=0,"",N454*4+150000) -- Thank you! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip the Zeros in a chart
Thanks for posting that, I didn't read the question properly.
Regards, Peter T "Jon Peltier" wrote in message ... Not Plotted and Interpolated are only applied to actual blank cells, not cells which have formulas that return "". The best you can do is change "" in the formula to NA(). This looks like #N/A in a cell, which looks ugly, but in a line or XY chart is not plotted. Instead, the connecting line is interpolated across the space where the point would be plotted. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Peter T wrote: In Excel 2003 and older, select the chart, Tools, Options, Chart, Plot empty cells as: Not plotted (leave gaps), or maybe you might prefer Interpolated. In Excel 2007 search "Not plotted" in help Regards, Peter T "Doug" wrote in message ... How can I have the chart skip Zero values? I am using the formula below to leave the cell blank for values that are zero, but it still shows up in the chart messing up the line. Is there a chart setting for this or do I need to change the formula? =IF(N454=0,"",N454*4+150000) -- Thank you! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| skip & hit chart for lotto | Excel Programming | |||
| how do you skip empty cells or zeros when calculating the average | Excel Programming | |||
| do not show zeros in pivot chart (pie chart) | Charts and Charting in Excel | |||
| Formula returns empty; chart plots zero; I want to skip | Charts and Charting in Excel | |||
| Skip some columns when creating a chart | Charts and Charting in Excel | |||
Linear Mode
