Exclude zeros from series

  • Thread starter Thread starter Arch Stanton
  • Start date Start date
A

Arch Stanton

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.
 
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
 
A perfect solution to my problem less than ten minutes after I asked.
Thanks, my friend!

AS
 
Back
Top