Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
Data_Lisa
 
Posts: n/a
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.charting
Data_Lisa
 
Posts: n/a
Default Diagram show blank as zero value when based on a formula

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.

  #3   Report Post  
Posted to microsoft.public.excel.charting
David Biddulph
 
Posts: n/a
Default Diagram show blank as zero value when based on a formula

"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


  #4   Report Post  
Posted to microsoft.public.excel.charting
Data_Lisa
 
Posts: n/a
Default Diagram show blank as zero value when based on a formula

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



  #5   Report Post  
Posted to microsoft.public.excel.charting
Andy Pope
 
Posts: n/a
Default Diagram show blank as zero value when based on a formula

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


  #6   Report Post  
Posted to microsoft.public.excel.charting
Data_Lisa
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula to delete blank cells across multiple columns? SamFunMail Excel Worksheet Functions 2 September 1st 05 07:05 AM
How do you nest the following formula to show blank cells JV Excel Worksheet Functions 3 August 6th 05 06:26 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM
Show a date based on today DJ Dusty Excel Worksheet Functions 2 November 12th 04 04:20 AM


All times are GMT +1. The time now is 06:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"