How to ignore zero values when plotting a graph
 

Using Excel 2003. I have a data range for a graph. The values in the cells
are the results of a simple If function - If(m280,n28,0). The results are
taken from a larger data input exercise. But, the graph line (a simple
graph!) plots the FALSE value (0) when I would like there to really be no
value & hence no plotted point if the result is FALSE.

Answer: How to ignore zero values when plotting a graph
 

To ignore zero values when plotting a graph in Excel 2003, you can follow these steps:

1. Select the chart that you want to modify.
2. Right-click on any data point in the chart and select "Chart Options" from the context menu.
3. In the "Chart Options" dialog box, click on the "Data Labels" tab.
4. Check the box next to "Value" to display the values for each data point.
5. Click on the "Number" button to open the "Format Cells" dialog box.
6. In the "Number" tab, select "Custom" from the "Category" list.
7. In the "Type" field, enter the following custom number format: 0;-0;;@
8. Click "OK" to close the "Format Cells" dialog box.
9. Click "OK" to close the "Chart Options" dialog box.

This custom number format will display positive values as usual, but negative values will be displayed as blank cells. This means that any data points with a value of 0 will not be displayed on the chart.

Alternatively, you can modify your original formula to return a blank cell instead of 0 when the result is false. To do this, you can use the following formula:

This formula will return the value of N28 if M28 is greater than 0, and a blank cell if M28 is less than or equal to 0. This will ensure that any data points with a value of 0 will not be displayed on the chart.

Phil -

Change this:

If(m280,n28,0)

to this:

If(m280,n28,NA())

This results in the ugly #N/A error in the cell, but it makes the chart
ignore the point. Debra Dalgleish shows how to hide the ugliness with
conditional formatting:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Phil Lavis wrote:

Using Excel 2003. I have a data range for a graph. The values in the cells
are the results of a simple If function - If(m280,n28,0). The results are
taken from a larger data input exercise. But, the graph line (a simple
graph!) plots the FALSE value (0) when I would like there to really be no
value & hence no plotted point if the result is FALSE.

Hi Jon,

Using a standard line chart, I seem to be getting an interpolated value with
NA() as opposed to a gap in the line. Could I be missing a setting somewhere?

For example, my original data is on the left side below and charted data is
on the right. Assuming a blank column between the two sets with the data
starting in cell A1, I've added this formula to cell E1 and copied down the
column:

=IF(B10,B1,NA())

a 6 a 6
b 4 b 4
c 0 c #N/A
d 5 d 5
e 0 e #N/A
f 3 f 3

Excel seems to interpolate the line in column E rather than leaving a gap.

Tushar Mehta has this information on his site - does this still apply?

http://www.tushar-mehta.com/excel/so...discontinuity/

Thanks.

John Mansfield


"Jon Peltier" wrote:

Phil -

Change this:

If(m280,n28,0)

to this:

If(m280,n28,NA())

This results in the ugly #N/A error in the cell, but it makes the chart
ignore the point. Debra Dalgleish shows how to hide the ugliness with
conditional formatting:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Phil Lavis wrote:

Using Excel 2003. I have a data range for a graph. The values in the cells
are the results of a simple If function - If(m280,n28,0). The results are
taken from a larger data input exercise. But, the graph line (a simple
graph!) plots the FALSE value (0) when I would like there to really be no
value & hence no plotted point if the result is FALSE.

No, you are not missing anything. XL will only interpolate over NA()s.
It won't create gaps. To get gaps, you have to use a programmatic
solution such as
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html

However, because of a bug introduced with XL2002 (2000?) and not yet
fixed means even the code doesn't work with a line chart, though it
continues to work just fine with a XY Scatter chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi Jon,

Using a standard line chart, I seem to be getting an interpolated value with
NA() as opposed to a gap in the line. Could I be missing a setting somewhere?

For example, my original data is on the left side below and charted data is
on the right. Assuming a blank column between the two sets with the data
starting in cell A1, I've added this formula to cell E1 and copied down the
column:

=IF(B10,B1,NA())

a 6 a 6
b 4 b 4
c 0 c #N/A
d 5 d 5
e 0 e #N/A
f 3 f 3

Excel seems to interpolate the line in column E rather than leaving a gap.

Tushar Mehta has this information on his site - does this still apply?

http://www.tushar-mehta.com/excel/so...discontinuity/

Thanks.

John Mansfield


"Jon Peltier" wrote:

Phil -

Change this:

If(m280,n28,0)

to this:

If(m280,n28,NA())

This results in the ugly #N/A error in the cell, but it makes the chart
ignore the point. Debra Dalgleish shows how to hide the ugliness with
conditional formatting:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Phil Lavis wrote:

Using Excel 2003. I have a data range for a graph. The values in the cells
are the results of a simple If function - If(m280,n28,0). The results are
taken from a larger data input exercise. But, the graph line (a simple
graph!) plots the FALSE value (0) when I would like there to really be no
value & hence no plotted point if the result is FALSE.