Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I get an Excel chart to ingore zero values created by funct
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I get an Excel chart to ingore zero values created by funct
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/...artLinks.html] HTH, Henk "BOBODD" wrote: 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do I get an Excel chart to ingore zero values created by funct
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "BOBODD" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Titles not showing in excel chart Window | Excel Discussion (Misc queries) | |||
Copy and paste Excel chart in Word -- font compresses | Charts and Charting in Excel | |||
how do i create a pie chart in excel with non-numerical values? | Charts and Charting in Excel | |||
Can I have more than 255 characters in Chart "Values" of Excel 2k? | Charts and Charting in Excel | |||
Excel 2003 crashes loading excel files created Excel 2000 | Excel Discussion (Misc queries) |