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.
0
Data_Lisa (1)
6/7/2006 4:46:03 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
677 Views

Similar Articles

[PageSpeed] 5

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.
0
DataLisa (4)
6/7/2006 4:55:02 PM
"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 


0
david4578 (116)
6/7/2006 4:55:07 PM
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 
> 
> 
> 
0
DataLisa (4)
6/7/2006 6:33:25 PM
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
0
andy9699 (3616)
6/7/2006 6:45:03 PM
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
> 
0
DataLisa (4)
6/7/2006 8:06:03 PM
Reply:

Similar Artilces:

Formula not being stored any more
Recently, Excel has stopped storing certain formulae in the formula bar. For example, if I type in say "=3*1/10" Excel will store "=0.3" in the formula bar. This is most inconvenient as I want to be able to tell what the constituent parts of the calculation are. It never used to do this so have I accidentally set an option on somewhere? How do I turn it off again? I couldn't duplicate this. If I typed: =3*1/10 and hit F9 (calculate)--not enter I got: 0.3 You're not getting close to the F9 key (with only portions of your formula selected? (Yeah, I did...

First non-blank row in WS after hyperlinking
I have several ws I use as check registers. I'd like to be able to go to the last non-blank row in that ws when I click that register's ws from the index page. It would always be the next available row in the applicable ws. Is there a way from HYPERLINK to do that or maybe a function once I get there? Macro? TIA here are a couple different ways: this will select the cell. it's not a good practice to select, but just used as an illustration here. range(worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address).Select Sub FindLastCel...

Blank body on E-mails received with signature and attachment
When I get an e-mail that has an attachment and a signature, either the message body will be blank or the body will have text but the signature will not appear in the body. There will be a .txt file along with the attachment which is probably the signature but when I click on it I get "access is denied". Are there settings that will allow the signature to be displayed in the body of the text? The sender is using Eudora 7. Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you&#...

Publisher
Have set up a small report 10 pages in total but when printing page 9 I get additional blank pages printing and then my page 10 how do I stop this from happening? Open the Design Checker, see if it tells you anything. Is the page visible in print preview? What version Publisher? Is the publication setup as a booklet? Publisher works in groups of 4 when printing a booklet. With a ten page booklet setup, Publisher will print a blank page. -- Mary Sauer http://msauer.mvps.org/ "charliegirl" <charliegirl@discussions.microsoft.com> wrote in message news:F995943A-B6A5-4B6B-...

Days Old formula?
Hi im having a problem trying to figure out the forumla for days old. M teacher wants us to come up with a formula for the age of 2 dates. Does anyone know any formulas that will work? -- Message posted from http://www.ExcelForum.com If you need the days only, subtract =A1-A2 -- Regards, Peo Sjoblom "frackskat004 >" <<frackskat004.11hxa0@excelforum-nospam.com> wrote in message news:frackskat004.11hxa0@excelforum-nospam.com... > Hi im having a problem trying to figure out the forumla for days old. My > teacher wants us to come up with a formula for the age o...

Show userform at spreadsheet open
How do you make a userform open when you open a spreadsheet? Can anyone help? Thankyou, Roger [excel2003] hi sheet or file? sheet..... Private Sub Worksheet_Activate() Load userform1 userform1.Show 0 End Sub lookup modal and modaless forms in vb help. file.... Private Sub Workbook_Open() Load userform1 userform1.Show 0 End Sub also see this site.... http://www.mvps.org/dmcritchie/excel/getstarted.htm regards FSt1 "Roger on Excel" wrote: > How do you make a userform open when you open a spreadsheet? > > Can anyone help? > &g...

How to convert to absolute value?
Hello! How do I convert numbers to absolute value? Thanks Edit - Copy. Edit - PasteSpecial - Values - OK. Or do you want a code solution ? HTH. Best wishes Harald "Betsy Marlow" <cmarlow22@bellsouth.net> skrev i melding news:5WmHg.12873$j8.11911@bignews7.bellsouth.net... > Hello! > > How do I convert numbers to absolute value? > > Thanks > > Print abs(-12) 12 On Thu, 24 Aug 2006 15:16:31 -0400, "Betsy Marlow" <cmarlow22@bellsouth.net> wrote: >Hello! > >How do I convert numbers to absolute value? > >Thanks >...

Viewing Formulas instead of formula results
I want to view all of the formulas in my worksheets without going through each cell and typing " " around each formula. Is there any way to do this so I can check all of my formulas at once? Meghan, here is one way, use Ctrl and ~, this will toggle between formulas and results -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Meghan" <mmckee15@yahoo.com> wrote in message news:00db01c351f6$4eb0...

Don't plot zero values
I am currently using Excel XP. I have a scatter chart and would only like to plot non zero values. These are not null values. Is there a chart setting I can do to skip plotting zero values or do I need to somehow filter/sort the data first and then plot? TIA George Hey George - If there are true zeros in the data, perhaps the easiest way to exclude them from the chart is to use an autofilter on the data that hides the rows with zeros. Alternatively, you could insert a column to hide the zeros. Say the range with zeros is in B1:B10. Select C1:C10, and enter this formula into C1: =IF...

Conditional Formula?
Hi- I need help creating a formula that sums values in a list based on the value in an adjacent cell. Please see attached screen shot. Hopefully it explains what I'm trying to do. Thanks. +-------------------------------------------------------------------+ |Filename: excel help 3-10-06.gif | |Download: http://www.excelforum.com/attachment.php?postid=4442 | +-------------------------------------------------------------------+ -- rhovey ------------------------------------------------------------------------ rhovey's Profile: http://www.excelf...

diable command button based on field value
I have a command button which I would like to disable based on the following rules: If A = 1 then button is disabled If B = 2 then button is enabled Where you put the code depends on how the form works. If this needs to be checked for each record, use the form current event: If A = 1 Then Me.Button.Enabled = False ElseIf B = 2 Then Me.Button.Enabled = True End If But, what if A is not 1 and B is not 2? -- Dave Hargis, Microsoft Access MVP "barrynichols@gmail.com" wrote: > I have a command button which I would like to disable based on the >...

Transactions don't show latest download
This is a multi-part message in MIME format. ------=_NextPart_000_021A_01C4EDED.8453C110 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've downloaded my transactions from my bank, it registers in = MSMoney2004 as downloaded, but the transactions don't show in the = account. How do I force the program to recognize and list the = downloaded transactions? Chad Bresson Xenia, OH ------=_NextPart_000_021A_01C4EDED.8453C110 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DO...

Convert 2002 D/B to 2000 shows no details
When I attempt to use the [Tools];[Database Utility]; to [Convert Database]; to Access 2000 format from Access XP, the resulting database doesn't display anything in the Detail View. All tables and queries appear to be present in LIST View and ICON Views but when DETAIL View is selected the entire display is blank. Is there a "Select Details" option in Access as there is in Windows Explorer? "Tom" <tvandeveer@ldmi.com> schrieb im Newsbeitrag news:000701c3dea6$9c1b5440$a301280a@phx.gbl... > When I attempt to use the [Tools];[Database Utility]; to > ...

displaying negative values
how do I hide the display of negative numbers? =if(A1<0,"",A1) HTH Regards from Brazil Marcelo "cfuller" escreveu: > how do I hide the display of negative numbers? Hi, That will work if cell A1 has a formula in it. If negative numbers are just typed in, you can use conditional formatting. Highlight the range of cells you want to format. Go to Format > Conditional Formatting... Enter cell value is less than 0. Click the Format... button and select Color: and then click the white icon. HTH -- Ken Hudson "Marcelo" wrote: > =if(A1<0,"&qu...

default mail format based on domain
Greetings - After 20_ years of Eudora and Thunderbird (in that order), I'm looking at Outlook for the first time (we're moving to an Exchange backend, so...worth a look, for obvious reasons). In Thunderbird, if I want to set a default HTML format for emails sent to a certain domain, I can specify that. For any other domain(s), plain text. Is there an easy way to do that in Outlook (I'm using 2007)? Basically, I want emails sent to my enterprise domain (i.e., my place of employment) to be in HTML or perhaps RTF, but mail sent to other domains to be plain text only. Again, I want ...

Visio File Summary Dialog Box is showing wrong file name and size
Hi all, I am using Visio 2003 ActiveX Control in my application using VB.NET 2005. I am calling "visCmdFileSummaryInfoDlg" Command to open File Summary Dialog Box. Dialog Box is open successfully but it always showing Template file size instead of open file size in General Tab. The information it shows is: Type: Microsoft Visio Drawing Location: Size :(928 bytes) Based on: Template - ValueStreamMap.vst Can any one let me know about any property or way so that it shows correct file size? Thanks Asif Can we assume that the new drawing has been saved to disk before you call th...

parameter values not valid #3
keep getting a window each time I try and save a new task (Outlook 2000) it says: "Could not complete the operation. One or more parameter values are not valid" I just set up Outllook, and can't figure out why it would do this. ...

How to determine the closed value?
There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric Hi Eric, this should do it: =INDEX($B$1:$B$5,L...

How do you do cross worksheet formulae's
I've just started writing basic addition and multiplication formulae etc. for multiple cells. I want to know how to write formulae to d this across worksheets (I think thats what they are called - the tab at the bottom?) For exaple, how would I add A1 on sheet1 to A1 o sheet2 -- Liam Green ----------------------------------------------------------------------- Liam Greene's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=897 View this thread: http://www.excelforum.com/showthread.php?threadid=26786 Liam, if you formula is in sheet one, =A1+Sheet2!A1, if you f...

need Blank column in invoice
Hi, How can I make(or hide) contents of cell unless the cell before it isfilled in? Hope i said that right. My invoice has a payment col and balance col next to each other,. I want to have the formula in the balance column all the way down, but it should be emty until a payment is filled in? Thanks Frank Frank Trap for no entry in the payment column by entering a formula in the balance column. =IF(payment_cellref="","",balance_formula) Gord Dibben MS Excel MVP On Wed, 19 Apr 2006 21:48:18 -0400, "Frank" <stratster68@IHATESPAMworldnet.att.net> ...

Set value of const member variable in Init() function?
Is there a method for setting the value of a const member variable in an Init() function when the value is unknown in the constructor? TIA, Harvey "Harvey" <harveyab@juno.com> wrote in message news:1172562585.493563.230590@h3g2000cwc.googlegroups.com... > Is there a method for setting the value of a const member variable in > an Init() function when the value is unknown in the constructor? I don't believe so. But be careful of the difference between const int x=1; and const char *pszString; pszString = ... The latter is ok as the pointer is not const - th...

how do I get the if function to return a blank cell, not 0?
I am trying to create a chart from a series that contains data for each month. The series is calculated on other worksheets and copied to the worksheet containing the chart. I would like to have the cells for the months that have not been updated yet (now is January, there are 0' in all cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's. Can this be done? Unfortunately, what you want, and what many of us have requested but doesn't exist, is a worksheet function like BLANK() or NULL(). The best we can do is use NA() in a chart's data source, which is...

How to save the value in the CEdit control to a text file?
Im generating a random number to the CEdit control and now i want t save all of this value to a text file. I dont know to code it in the way of MFC. I hope whose know it can send me the soding cause im running out the time to do my project thanks You can use GetWindowText to get its contents and then use CStdioFile class to write the text. MSDN has enough documentation on this. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "TLee" <anonymous@discussions.microsoft.com> wrote in message news:F730B5D6-85F7-45D9-80D9-C43598FA47B6@microsoft.com... | Im generating a random numbe...

Sum Formula Question
I was having trouble with a specific formula. Assistance would be greatly appreciated. I have a form that contains a form header, detail, and form footer. The detail section contains fields that are doing calculations. There is a text box that has source info going into Hours on a table. The data on that field has a formula: =DSum("[Hours]","Labor Metrics","[Tech manual ID] =Forms![Labor Metrics (Project)]![Labor Metrics (Project) subform]![Tech Manual ID]") When the form is run, it brings up information for all the directed results. I am trying to get the...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...