ExcelBanter - View Single Post - Diagram show blank as zero value when based on a formula

Thanks, Andy
Lisa

"Andy Pope" wrote:

Hi,

Hopefully this will shed some light.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

Data_Lisa wrote:
Yes, but I can't have the line to leave gaps. It plots the line as if I have
interpolated chosed even if my Active chart have "Not plotted (leave gaps)
chosed in Tools Options... Do you know why?

"David Biddulph" wrote:

"Data_Lisa" wrote in message
...

When a chart is based on a formula e.g. =IF(A1=0;"";A1*B1) then the chart
will show the blank value "" as zero even if you whant to plot gaps or
plot
the empty cell interpolated. Yes, I know Excel say empty cell and the cell
is
not empty when there is a formula. Is there anybody who have found a "work
around"? I often have formulas that generate zero in the result but I want
to
plot the result as if it was empty.

Replace the blank "" by NA()
--
David Biddulph

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info