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
415 Views

Similar Articles

[PageSpeed] 43

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:

Top 5 recurring text values with count
My data: Col 1 Apples Oranges Oranges Bananas Apples I'm trying have excel show the text "Apples" in one col & the count "2" in another col; same with oranges and bananas. I don't know if it is the blank cell that is causing errors but I have tried LARGE, VLOOKUP, MATCH and am not having any luck. Thank you in advance On May 5, 3:07=A0pm, S <S...@discussions.microsoft.com> wrote: > My data: > > Col 1 =A0 =A0 =A0 =A0 =A0 =A0 > Apples > Oranges > Oranges > > Bananas > Apples > >...

Closed mortgage account doesn't show up in tax sftwe report selector tool
MSM05Dlx - no Passport/Yodlee. Tried both levels of file repair, and scanned the FAQ. We refinanced this year, and I'm trying to get all of the interest from all mortgage accounts active last year into a single tax sofware report. The old account is (correctly) marked as closed. When hitting "Customize" and checking "Show Closed Accounts" the account for our old mortgage does not appear. Actually, reviewing this, neither loan account (old or new) is visible to select from... This makes me think I'm misunderestimating something - it's working properl...

wARNING KEPPS SHOWING
i AM A NEW USERS UPON OPENING MY EMAIL i RECIEVE THE FOLLOEING MESSAGE. the add in "c/program files/microsft office/sbcmsyac,dll could not be loaded. Out of memory,out of resource or missing dll files. Can you asstist me i correcting this problem. try outlook /safe. if this works then you have a bad add-in "HR" <anonymous@discussions.microsoft.com> wrote in message news:5AE9FCC8-2120-4CB6-A423-764AE453ACAF@microsoft.com... > i AM A NEW USERS UPON OPENING MY EMAIL i RECIEVE THE FOLLOEING MESSAGE. > the add in "c/program files/microsft office/sbcmsyac,dll could ...

limiting cell value
Hi, I have a Foreign currency bill making sheet. In that I would like to sort the following problem ----- I----------------- J -------- ------ K ----------------------- L ----------------- M PURCHASE/SALE------- FCY----------QUANTITY------------------RATE-------------LOCALY CRRNCY in the fourth colomn(L-RATE)) there is vlookup formula that brought the currency rate from the RATE sheet. The formula is =IF(ISNA((VLOOKUP(J2,RATE!$A$1:C$32,IF(I2="P",2,3),FALSE))),"",VLOOKUP(J2,RATE!$A$1:C$32,IF(I2="P",2,3),FALSE)) Now i wanted to give the rate colomn so...

Custom XLAM tab not showing up when excel is opened from word
I have two closely identical add-ins for word and excel; the word dotm is saved in the startup folder for word and the excel xlam is saved in the addins folder. They have been working fine for about a year and a half. I added some more functionality to them both by giving the ability to open an excel sheet from the custom ribbon tab and pre-populating it with information from the document that it was opened from. When the beginning document is excel, it works fine which makes sense. when the beginning document is word, however, the macro works correctly and opens the excel do...

ropdownlist selected index remains zero
Hi, I have a ModalPopupExtender in my page. And In popup I am showing a gridview. Inside Gridview a dropdownlist is there. In pop there is a submitt button. But problem is in dropdownlist selected index remains zero irespective of my selection.Even after I select second,third or fourth Item ti will show first item of the dropdown as the selected Item. Please help. Please let me know why dropdownlist selected item is not getting changed ? Also all my code in Page_load is inside if (!IsPostBack). My Code is as below. binding gridview in the popup:- # protected void btnSear...

copied additional column shows in view but does show on hardcopy
I have an existing spreadsheet and needed to add another column with the same format. I copied it and but when I print a hard copy it is not there. I checked and that column is not hidden. Re-define your print range to include that column? Gord Dibben MS Excel MVP On Tue, 1 Jun 2010 06:51:01 -0700, Patricia W <Patricia W@discussions.microsoft.com> wrote: >I have an existing spreadsheet and needed to add another column with the= same=20 >format. I copied it and but when I print a hard copy it is not there. = I=20 >checked and that column is not hidden...

Problems showing asp-pages in ie8
Hi When I try to show an asp-page with following html command <a href="xx.asp">Show page</a> the browser attempts to download xx.asp and not showing the page. Can anyone help? See the security setting "Open files based on content, not file extension." Is xx.asp in your web root (intra/inter-net)? or is it just in a folder on your machine (Local Machine zone) Assuming you are using VS.... Add the file xx.asp to your project before calling it from your web. Regards. "Ole Nielsen" <OleNielsen@discussions.microsoft.com> ...

Lookup value off by one row
Hi, Any help you can provide on this would be appreciated. I have a worksheet that identifies how far a number is from target. I created the lookup below to coorespond to the productivity increase for next year. =LOOKUP(C4,LookUpValues!$A$1:$A$402,LookUpValues!$B$1:$B$402) C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the desired result return of 5.6 in column b, however the lookup function is returning 5.7. The issue may be that the data and the lookupValues tab numbers are not exactly the same. -10.90001 and -10.9004 may display the same, but w...

Excel formula problem =IF(0<B7<20;1;0)
Need excel to return the value 1 if the value in field B7 is larger tha 0 and smaller than 20. =IF(B7<20;1;0) kinda works except that if B7 is empty 1 is returned I then tried to indicate the range from 0 to 20, by doing this: =IF(0<B7<20;1;0) this results in excel returning 0 no matter what write in field B7 What am I doing wrong? Flemmin -- Message posted from http://www.ExcelForum.com Hi try =IF(AND(B7>0,B7<20);1;0) -- Regards Frank Kabel Frankfurt, Germany > Need excel to return the value 1 if the value in field B7 is larger > than 0 and smaller than 20. ...

"Reports" shows CC payment as expense?
Hi I'm using Money 2007, and I'm trying to view the "Reports". I have a checking account and credit card account. Even though I classify my credit card payments as "Credit Card Transfers/Payments" the Reports still shows those payments as Expenses. I purchase everything on my credit card, so it doubles the amount of expenses I have, instead of showing it as a transfer. For example if I charge $100 to my credit card, then pay off that credit card with my checking account, Money shows that I spent $200. $100 on the c/c and $100 paying the c/c. How can I correct...

Data not showing in combo box
I have a database which uses a combo box with 2 columns, in Access 2007 I can see the information from both, in Access 2003 I can only see the data from column 2, if I go to the SQL query and run that I can see both so I know the data is there. Strangely if I try in Access 2000 I can see the data no problem, This database was designed originally in 2000 and has been running fine on all versions untill now so I am baffled - any suggestions so my colleague can use in 2003 would be much appreciated. I have tried on a 2nd machine with 2003 and still does not work. Sheila There was a bug...

DIsplaying negative time values
Hi, I have two columns of time values. I need to determine the difference between the two values and return either a positive or negative time value. The table below is in hours and minutes Time A Time B 09:00:00 09:00:00 09:00:00 09:03:00 09:00:00 08:58:00 In the above example First row should be "00:00:00", Second row should be "00:03:00", and Third row should be "-00:02:00" When subtracting Time B from Time A for the first two rows i get the correct value, but the third row returns as "##########...

Showing Mail when there is NONE
HEY, I have my Outlook Express showing that I have FIVE e- mails in my IN BOX, however, all the mail is OUT of the IN BOX so how can I RESET it so that it shows NO E-MAIL?? Thanks in Advance to the Group David Vincent - Coronado Check View >> Current View... "David" <anonymous@discussions.microsoft.com> wrote in message news:31ec01c4a9d0$37a72890$a401280a@phx.gbl... > HEY, I have my Outlook Express showing that I have FIVE e- > mails in my IN BOX, however, all the mail is OUT of the IN > BOX so how can I RESET it so that it shows NO E-MAIL?? > Thanks in ...

personalized form data show
I need to create a personalized form, to correct bases of information, where it could neither eliminate nor give of high records Isaac Isaac wrote: > I need to create a personalized form, to correct bases of > information, where it could neither eliminate nor give of high records > > Isaac That is very interesting Isaac. Is there any special reason why you felt the need to tell us? ...

check if any values on row
Hi, I am trying to have a formula on cell A1 for example that would look on cells D1:CT1 to see if there are any values different than 0 (in other words not empty or equal to 0). If there are any values different than 0, cell A1 would indicate "Row with data" if none, it would say "Empty row". Any help would be appreciated. Thanks On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom <norvascom@gmail.com> wrote: >Hi, > >I am trying to have a formula on cell A1 for example that would look >on cells D1:CT1 to see if there are any values dif...

Help with complex formula
Well it's complex for me :-) I have created the following formula which in the main works well, but I am wanting to enhance it further with other criteria below and struggling. =IF(M22="","",(IF(LEFT(M22,2)="XD",VLOOKUP(M22,'\\WYP_GEN_1\EA_DIV$\Duties\[Callsigns.xls]CollarNo'!$A$1:$D$100,4,FALSE),(IF(LEFT(M22,3)="TW1",CONCATENATE("TW1",RIGHT(M22,2)),(IF(LEFT(M22,3)="STW",CONCATENATE("STW",RIGHT(M22,3)),(IF(LEFT(M22,3)="CS1",CONCATENATE("CS01",RIGHT(M22,2)),(IF(LEN(M22)=2,CONCATENATE("0...

Need formula #2
I need a formula to provide the perfect score in a catagory Using a relative index of 5 and weights Category-Performance Relativity Index-5 Weights-as noted below Scope Of Work-8 (weight) Service Level-6 (weight) Estimate Turn Time-8 (weight) Timeliness of Repairs-8 (weight) Ingate To Available-6 (weight) Inventory Reporting-4 (weight) Staus Response-8 (weight) Sub-total (perfect score) 137 (is this correct) What should the formula be for this? needinhelp -- needinhelp ------------------------------------------------------------------------ needinhelp's Profile: http://www.excelfor...

Hyperlink one Visio diagram to another Visio diagram (in Visio 200 #3
I would like to hyperlink to many other Visio diagram within one diagram to show a high level overview of processes. If someone wanted a more detailed view of a particular process they could click on the Visio Diagram. I know how to hyperlink to other file. My questions is there a way to get a thumb nail graphic of a Visio diagram so I can use it as a inserted graphic of the different Visio digrams? ...

A formula that displays the last letter in a text
I frequently use text in cells. I use MID and LEN often enough, but is there a formula that will display the last letter in a text that will often vary in length. Thanks. Michael =RIGHT(A1,1) -- __________________________________ HTH Bob <MichaelRLanier@gmail.com> wrote in message news:0983e9df-8991-43ff-ab3a-09ee5450fbd6@a70g2000hsh.googlegroups.com... >I frequently use text in cells. I use MID and LEN often enough, but > is there a formula that will display the last letter in a text that > will often vary in length. Thanks. > > Michael Hi Michael =MID(A1,...

Showing Month Value for a Number
I'm using Excell for XP and need some help. I have a worksheet in which I reflect total number of hours left on a project. Lets say 198. If you take 198 and divide it by 160 (average number of work hours in a month) you get 1.2 months. With today being September 10 and adding on 1.2 months, the project would be completed in October (I'm not worried about the exact date). What I'm ty to figure out is if it possible to change the resulting value of data to reflect the name of a month. Right now I say 1.2 months from today is October which I do manually. It would be nice to som...

Duplicates formula, empty cells
I'm ecstatic to find a solution to my first post this morning - saved my nonprofit a lot of scarce resources. Another issue: I'm using a formula to identify duplicate values in and among several columns in this worksheet of contacts. This is the formula: =IF(COUNTIFS(B2:B3,B2,C2:C3,C2)>1,"Dupe","") Is there a way to keep it from identifying empy cells as duplicates? (For example, some entries are just company names with no contact first name or last name.) Thank you again for help earlier, and in advance for any suggestions. Dwight Downs Lakeshore Foundati...

MAX AVERAGE MIN with additional criteria, ignoring blank cells
Hi all, After some recent help from the forum I'm successfully using the following array formulas to calculate the MAX values in several columns of cells. The values used to calculate the MAX depend on other numerical values located in adjacent columns: {=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,K3:K1002)))} {=IF(P1019="","",MAX(IF($E$3:$E$1002=$F$3:$F$1002,IF($G$3:$G$1002=$H$3:$H$1002,P3:P1002))))} {=IF(U1019="","",MAX(IF($G$3:$G$1002=0,IF($H$3:$H$1002=0,U3:U1002))))} {=IF(AL1019="",""...

Show "Notes" in Entity diagram
I can show the PK, FK, Indexes in column 1, I can show db/conceptual names in column 2, I can show data type in column 3. Is there anyway to show in the diagram the Notes/Description field? Thank you, Ray ...

Can't Show Client Edge
I need to create controls dynamically in an app. I wrote the following test code but can't get the dynamically created CEdit control to show the client edge (WS_EX_CLIENTEDGE). The dialog has a CEdit defined in the dialog editor with a member variable m_edit. the code is as follows: BOOL CEditCtrlDlg::OnInitDialog() { CDialog::OnInitDialog(); // Dynamic control creation // Get location of new control. CRect editRect; m_edit.GetWindowRect(&editRect); editRect.top +=20; editRect.bottom +=20; // CEdit Dynamic DWORD my...