Pivot Table/Chart Conundrum

[MartinL]

How can I get my Pivot chart to see 0 as Blank/nul/empty cell?

My initial sheet has a lookup formula in it and under certain situations the lookup will return an empty cell (or it should).

My overlaying pivot table interprets this as a 0 (I'm guessing this is because the PT is looking at a formula in a cell??)

The Pivot chart sees 0 and sends my line crashing down, not what I want.

What I want is a gap where the cell is empty. If of course the cell is ZERO then the line can dip...

I have tried to find a way of using if statements in my initial sheet ie
if(lookupxxxxx = "",nul,lookupxxxxx

but that does work.

Just for the record, If I remove the formula from the cell, but include the cell in the pivot table range, the chart does show a gap.

So do I do this tweeking in the Pivot Chart or the Pivot Table or do something completely different

Ow my head hurts....

Martin

 

[MartinL]

Et al.,

If like me you are looking for a solution to this I found this:

http://www.exceltip.com/st/Adjusting_a_Formula_to_Return_a_Blank,_Rather_than_a_Zero,_When_Operating_on_Empty_Cells/894.html

Even if it looks blank, a cell with a formula isn't, it returns "",
which Excel plots as zero, like any text string. Replace the "" in the
formula with NA(), which returns an #N/A error in the cell. It looks
ugly in the sheet, but is interpolated over by the chart. Use
conditional formatting to hide the error in the worksheet. If the chart
has lines connecting the data, the line will go from the point before
#N/A to the point after; you can't make the line stop, to leave a gap.
Tushar Mehta has an addin on his web site that removes the formula with
#N/A by a blank cell (http://tushar-mehta.com), which helps the chart,
but removes the dynamic nature of a cell with a formula. We need MS to
invent a BLANK() or NULL() worksheet function.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html