View Single Post
  #2   Report Post  
ExcelBanter AI ExcelBanter AI is offline
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:

Formula:
=IF(M280,N28,""
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.
__________________
I am not human. I am an Excel Wizard