How can I make a Chart data series treat blanks as "Empty" cells
 

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

In article ,
says...
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

Replace the "" with NA(). I don't know the effect on the trendline and
if any whether it will be acceptable to you. It's something you will
have to check on and decide for yourself.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

That is certainly an improvement, thanks a lot.

Dan

"Tushar Mehta" wrote:

In article ,
says...
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

Replace the "" with NA(). I don't know the effect on the trendline and
if any whether it will be acceptable to you. It's something you will
have to check on and decide for yourself.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Have you tried hiding the offending rows ?

Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=400300

There are several thousand of them scattered about and they change with each
itereation so there would have to be an automatic way to do it.

Thanks for the reply

"Jon Quixley" wrote:


Have you tried hiding the offending rows ?

Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=400300

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

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

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

You are welcome.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
That is certainly an improvement, thanks a lot.

Dan

{snip}

How can I make a Chart data series treat blanks as "Empty" cells
 

Have you found the answer yet?
I am trying to plot a data series also through months. If the month is not
here yet, the cell is calculated to be blank, but the chart is plotting it as
zero. Is this similar to your situation? How did you fix it?
Thanks

"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

How can I make a Chart data series treat blanks as "Empty" cells
 

Option 1: Replace the "" in your formula with NA().

Option 2: If you don't like the ugly #N/A or it messes up downstream
calculations, use another column in which you have the NA() instead of
the "". Plot this new column but use the original for other work.

Option 3: Adapt the ideas behind
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html
If you use COUNT() instead of COUNTA(), the solution will include only
those cells with numbers and exclude the ""s.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Have you found the answer yet?
I am trying to plot a data series also through months. If the month is not
here yet, the cell is calculated to be blank, but the chart is plotting it as
zero. Is this similar to your situation? How did you fix it?
Thanks

"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

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

How can I make a Chart data series treat blanks as "Empty" cel
 

Don't make a pivot chart, make a regular chart. Select a blank cell outside
the pivot table, start the chart wizard. In step 2, go to the Series tab,
and one-by-one add and populate the series you want.

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


"psipg" wrote in message
...
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