View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.charting
psipg psipg is offline
external usenet poster
 
Posts: 5
Default How can I make a Chart data series treat blanks as "Empty" cel

Jon, I'm having the same types of trouble here, but I tried using an xy
chart... It won't let me because my data source is a pivot table. I don't
have formula's with blanks or N/As but I am summarizing data that it linked
to other sheets, and it reads that formula as a vaule... Is there anything
else I can do to get it to ignore the zero in my chart?

"Jon Peltier" wrote:

I guess I'd try filtering the data, extracting the Category 5s to a new
sheet and analyzing that subset. Another option would be a pivot table,
because you can sort the storms by cateogyr, and group the dates by
month or year. I'd also put the data onto an XY chart.

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


XLADLK wrote:

Hmm, that may be why the trendline isn't fitting just real well but I don't
know of a better way. It's like plotting the number of people injured by
tornados each month for the last 20 years and then running a trendline
through only the Cat 5's. The next iteration might be only Cat 3 and above.
A given month may or may not have a value depending on your criteria. See my
problem? Suggestions are welcome.

Thanks

"Jon Peltier" wrote:


Removing those rows is not an option as I have to retain the x-axis
value as place holder on the chart.

So you're making a line chart and not an XY chart? This may play havoc
with your trendline formula, since Excel calculates line chart
categories as 1, 2, 3, etc.

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

XLADLK wrote:


My Data Series contains blanks as the result of formula calculations. The
location and number of blanks can change with each recalculation as input
criteria change. I can make my Chart ignore an "Empty" cell easy enough but
my cells still contain the formula which created the blank ("") cell and
therefore, not truely empty. I want to plot the series, about 7000 values,
without the blanks showing up as zeros and ruining my trendline fit.
Removing those rows is not an option as I have to retain the x-axis value as
place holder on the chart.

Thanks for your help; nothing is as simple as it seems when you start

Dan