Exclude unused value in chart
 

I have date that includes subjective comments, but also dates and objective
data.
I'd like to have a chart that plots only the dates with data.
I can use autofilter to hide the rows with no objective data, but then I
can't see subjective comments. It seems to be a pain to have to activate
autofilter to view the graph, but then deactivate to see the whole data sheet.
Example below
Date # Units Weather
1/1/06 10 Sunny
1/15/06 12 Rainy
1/16/06 Sunny
1/20/06 3
1/22/06 Rainy
1/30/6 5 Snow

I'd like a graph that shows only the days with "# units" data available, but
want to view the above sheet otherwise.

Exclude unused value in chart
 

Hi Scott,

It is possible to write a macro to copy only the non-blank rows to another
range for the chart.
Another option is to include only the non-blank rows in the chart by
selecting them separately using the ctrl key with the mouse (or placing
commas after each selected range).

E.g. with the data in sheet1 c9:c14 and the dates in b9:b14 and creating a
line graph of the data with the dates as category x axis labels, the series
is:
(Sheet1!$C$9:$C$10,Sheet1!$C$12,Sheet1!$C$14)
and the labels a
(Sheet1!$B$9,Sheet1!$B$10,Sheet1!$B$12,Sheet1!$B$1 4)

Hope that is helpful
Anthony

"Scott" wrote:

I have date that includes subjective comments, but also dates and objective
data.
I'd like to have a chart that plots only the dates with data.
I can use autofilter to hide the rows with no objective data, but then I
can't see subjective comments. It seems to be a pain to have to activate
autofilter to view the graph, but then deactivate to see the whole data sheet.
Example below
Date # Units Weather
1/1/06 10 Sunny
1/15/06 12 Rainy
1/16/06 Sunny
1/20/06 3
1/22/06 Rainy
1/30/6 5 Snow

I'd like a graph that shows only the days with "# units" data available, but
want to view the above sheet otherwise.

Exclude unused value in chart
 

Scott -

In your formula that counts values, return NA() for zero, instead of "", or
instead of not displaying zero values.

=if(COUNT(range)=0,NA(),COUNT(range))

This puts #N/A in the cell instead of 0 or a pseudo blank ("" looks blank,
but Excel knows it's a short bit of text). If you don't like to display the
error, use conditional formatting to hide it:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Scott" wrote in message
...
I have date that includes subjective comments, but also dates and objective
data.
I'd like to have a chart that plots only the dates with data.
I can use autofilter to hide the rows with no objective data, but then I
can't see subjective comments. It seems to be a pain to have to activate
autofilter to view the graph, but then deactivate to see the whole data
sheet.
Example below
Date # Units Weather
1/1/06 10 Sunny
1/15/06 12 Rainy
1/16/06 Sunny
1/20/06 3
1/22/06 Rainy
1/30/6 5 Snow

I'd like a graph that shows only the days with "# units" data available,
but
want to view the above sheet otherwise.