Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Exclude zeros from series
I am using VBA in Excel 2003 to create charts using columns of data on a
spreadsheet as the chart source. I have a dataset of numbers that range in the hundreds, but occasionally one of the data points is zero. When I plot my data, the zero values make my data plot take occasional dips down to the X axis, which looks peculiar and distracts my users. I don't need the zero-value data; those data points are useless to my users. Is there any way to tell VBA to use ranges of data for the source of a chart but to ignore zero values? I checked this thread for this question, and I guess I can use the =NA() function, but I don't want that to appear on my spreadsheet (my users won't know that's a zero value). Thanks for any help. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Exclude zeros from series
Two points
1) In Tolls | Option Charts there is an setting for how missing data is to be treated fro the currently selected chart. 2) You could use Conditional Formatting to hide #N/A but making the font the same colour as the cell background best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Arch Stanton" wrote in message . .. I am using VBA in Excel 2003 to create charts using columns of data on a spreadsheet as the chart source. I have a dataset of numbers that range in the hundreds, but occasionally one of the data points is zero. When I plot my data, the zero values make my data plot take occasional dips down to the X axis, which looks peculiar and distracts my users. I don't need the zero-value data; those data points are useless to my users. Is there any way to tell VBA to use ranges of data for the source of a chart but to ignore zero values? I checked this thread for this question, and I guess I can use the =NA() function, but I don't want that to appear on my spreadsheet (my users won't know that's a zero value). Thanks for any help. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Exclude zeros from series
A perfect solution to my problem less than ten minutes after I asked. Thanks, my friend! AS Bernard Liengme wrote: Two points 1) In Tolls | Option Charts there is an setting for how missing data is to be treated fro the currently selected chart. 2) You could use Conditional Formatting to hide #N/A but making the font the same colour as the cell background best wishes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average non-adjacent cells & exclude zeros | Excel Discussion (Misc queries) | |||
In series of 10 digit #s, how can I exclude 1st 3 digits in each? | Excel Worksheet Functions | |||
Calc RSQ and exclude zeros | Excel Worksheet Functions | |||
EXCLUDE ADDRESSES IN A SERIES SUM | Excel Worksheet Functions | |||
Use the "mouse over" event to exclude a data point from a series | Charts and Charting in Excel |