Thread: Diagram show blank as zero value when based on a formula

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.

"Data_Lisa" <Data_Lisa@discussions.microsoft.com> wrote in message
news:59ABF24B-F75E-4AAC-A599-0CE3BA4356CA@microsoft.com...
> 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

I found the reply myself in a question from another user similar to this issue.

I will use NA() instead of ""

Thank you that works!

"Data_Lisa" wrote:

> 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.

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" <Data_Lisa@discussions.microsoft.com> wrote in message
> news:59ABF24B-F75E-4AAC-A599-0CE3BA4356CA@microsoft.com...
> > 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
>
>
>

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" <Data_Lisa@discussions.microsoft.com> wrote in message
>>news:59ABF24B-F75E-4AAC-A599-0CE3BA4356CA@microsoft.com...
>>
>>>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

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" <Data_Lisa@discussions.microsoft.com> wrote in message
> >>news:59ABF24B-F75E-4AAC-A599-0CE3BA4356CA@microsoft.com...
> >>
> >>>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
>