Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 7
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average non-adjacent cells & exclude zeros Danni2004 Excel Discussion (Misc queries) 3 September 18th 07 04:00 PM
In series of 10 digit #s, how can I exclude 1st 3 digits in each? readystate Excel Worksheet Functions 3 June 8th 07 02:59 AM
Calc RSQ and exclude zeros Bruce Excel Worksheet Functions 3 January 19th 06 02:01 PM
EXCLUDE ADDRESSES IN A SERIES SUM viktaur Excel Worksheet Functions 1 March 9th 05 05:20 PM
Use the "mouse over" event to exclude a data point from a series Randall Hiltz Charts and Charting in Excel 10 January 19th 05 07:20 PM


All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"