Formula returns empty; chart plots zero; I want to skip
 

I am using formulas to populate the source data area for a chart. I am using
percentages, so when the total (divide by) is zero, I want to have an empty
cell. This will distinguish it from a real zero where the numerator is zero.
I am doing the division in another cell as =IF A2=0,"",A1/A2. In the cell I
am plotting from (source data), I am using =Sheet1!A3, where Sheet1!A3 is
where the previous formula is.
When I look at the cell in the source data, it is empty. If I copy and paste
special values, it still looks empty, but plots a zero.
If I delete the cell, then it leaves a gap the way I have it set up in the
Options.
How can I get this cell with the formula to skip in the chart instead of
plotting a zero?

Formula returns empty; chart plots zero; I want to skip
 

On Tue, 6 Feb 2007, in microsoft.public.excel.charting,
bluegar said:
How can I get this cell with the formula to skip in the chart instead of
plotting a zero?

The short answer is that we would all like to be able to do that, but
Microsoft sez "tough luck!" There is no substitute for an actually blank
cell.

The frustrating thing is that functions like AVERAGE() are completely
capable of evaluating FALSE as "do not count this point" and not as
zero, so that AVERAGE({2,4,FALSE,6}) is 4, not 3. If only chart series
had been designed to do the same thing, or even designed to offer that
as an option in the Tools.. Options.. Chart dialogue.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Formula returns empty; chart plots zero; I want to skip
 

If the formula returns NA() instead of "", there will be no point plotted in
a line or XY series. You don't get an actual gap; if the series has lines
connecting the points, a line segment connects the points on either side of
the gap. There are workarounds, which involve raw poultry parts and chants
at full moon.

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


"Del Cotter" wrote in message
...
On Tue, 6 Feb 2007, in microsoft.public.excel.charting,
bluegar said:
How can I get this cell with the formula to skip in the chart instead of
plotting a zero?

The short answer is that we would all like to be able to do that, but
Microsoft sez "tough luck!" There is no substitute for an actually blank
cell.

The frustrating thing is that functions like AVERAGE() are completely
capable of evaluating FALSE as "do not count this point" and not as zero,
so that AVERAGE({2,4,FALSE,6}) is 4, not 3. If only chart series had been
designed to do the same thing, or even designed to offer that as an option
in the Tools.. Options.. Chart dialogue.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

Formula returns empty; chart plots zero; I want to skip
 

I am working with a stacked column chart, & when I use the NA()

It actually adds #NA to the graph.

Am I SOL? or is there a way to get rid of the NA (or 0) values

"Jon Peltier" wrote:

If the formula returns NA() instead of "", there will be no point plotted in
a line or XY series. You don't get an actual gap; if the series has lines
connecting the points, a line segment connects the points on either side of
the gap. There are workarounds, which involve raw poultry parts and chants
at full moon.

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


"Del Cotter" wrote in message
...
On Tue, 6 Feb 2007, in microsoft.public.excel.charting,
bluegar said:
How can I get this cell with the formula to skip in the chart instead of
plotting a zero?

The short answer is that we would all like to be able to do that, but
Microsoft sez "tough luck!" There is no substitute for an actually blank
cell.

The frustrating thing is that functions like AVERAGE() are completely
capable of evaluating FALSE as "do not count this point" and not as zero,
so that AVERAGE({2,4,FALSE,6}) is 4, not 3. If only chart series had been
designed to do the same thing, or even designed to offer that as an option
in the Tools.. Options.. Chart dialogue.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

Formula returns empty; chart plots zero; I want to skip
 

It does? So it does; that's annoying. Maybe there's something you can do
with custom number formats, although a couple of minutes trying didn't
produce anything for me. Here's Jon's format hints page:

http://peltiertech.com/Excel/NumberFormats.html

Otherwise you may have to just manually select the one label by single
clicking twice, then deleting.

On Mon, 19 Mar 2007, in microsoft.public.excel.charting,
tmirelle said:

I am working with a stacked column chart, & when I use the NA()

It actually adds #NA to the graph.

Am I SOL? or is there a way to get rid of the NA (or 0) values

"Jon Peltier" wrote:

If the formula returns NA() instead of "", there will be no point plotted in
a line or XY series. You don't get an actual gap; if the series has lines
connecting the points, a line segment connects the points on either side of
the gap. There are workarounds, which involve raw poultry parts and chants
at full moon.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Formula returns empty; chart plots zero; I want to skip
 

Found the solution in case it is helpful to others...

set data label format to:

General;;;

Thanks everyone for your help!

"Del Cotter" wrote:

It does? So it does; that's annoying. Maybe there's something you can do
with custom number formats, although a couple of minutes trying didn't
produce anything for me. Here's Jon's format hints page:

http://peltiertech.com/Excel/NumberFormats.html

Otherwise you may have to just manually select the one label by single
clicking twice, then deleting.

On Mon, 19 Mar 2007, in microsoft.public.excel.charting,
tmirelle said:

I am working with a stacked column chart, & when I use the NA()

It actually adds #NA to the graph.

Am I SOL? or is there a way to get rid of the NA (or 0) values

"Jon Peltier" wrote:

If the formula returns NA() instead of "", there will be no point plotted in
a line or XY series. You don't get an actual gap; if the series has lines
connecting the points, a line segment connects the points on either side of
the gap. There are workarounds, which involve raw poultry parts and chants
at full moon.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Formula returns empty; chart plots zero; I want to skip
 

On Tue, 27 Mar 2007, in microsoft.public.excel.charting,
tmirelle said:
I am working with a stacked column chart, & when I use the NA() It
actually adds #NA to the graph. Am I SOL? or is there a way to get
rid of the NA (or 0) values

"Del Cotter" wrote:
It does? So it does; that's annoying. Maybe there's something you can do
with custom number formats, although a couple of minutes trying didn't
produce anything for me.

Found the solution in case it is helpful to others...

set data label format to:

General;;;

[*slaps forehead*] Of course!

Thanks for coming back with that.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.