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

[bluegar]

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?

 

[Del Cotter]

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.

 

[Jon Peltier]

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

 

[tmirelle]

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]

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.

 

[tmirelle]

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

set data label format to:

General;;;

Thanks everyone for your help!

 

[Del Cotter]

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.