How do I get an Excel chart to ingore zero values created by funct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using SUM functions to calculate monthly sales totals. Currently most of
the results are zeros. How do I include the entire data range in my chart (so
I don't have to redo it every month) but make sure that the chart doesn't
include any zero values? I've tried hiding all zeros in my worksheet, but the
chart doesn't recognise these as blank cells. I'm using Excel 2003
 
Drew:
I repeat my answer to another question here again.

There is an elegant solution, called "dynamic named range", that works
with the OFFSET command. Search the web with this jargon, or check out Jon
Peltier's website; it contains links to many others where you find
instructions how to do it.
[http://peltiertech.com/Excel/Charts/DynamicChartLinks.html]
HTH,
Henk
 
If it's a line or XY chart, and the zeros are all at the end of the range,
change your formula so it returns #N/A instead of 0:

=IF(SUM(A1:A10)=0,NA(),SUM(A1:A10))

The #N/A errors are ugly in the sheet, but can be hidden using conditional
formatting. The chart doesn't show these values.

- Jon
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2003 Excel Chart with zero data 5
Hiding zeros in dynamic charts 3
How do I not plot zero values that result from a formula in Excel? 1
How do I stop chart from plotting null values as zeros in excel 20 3
Chart values for blanks show as zero (Excel 2003) 2
Hide Cells with Zero Value in Excel 2007 while using line chart ty 4
Not Charting Zero Values 1
How not to chart zero value fields 1

Back
Top