division by zero error

I have data where there is a division by zero error because nothing is 
entered, but that will change when the data is entered. Is there a way I can 
suppress the chart from putting 0's in its place so that there is a gap in 
the chart when there is a division by zero error?
0
Tom7430 (437)
11/7/2007 5:24:02 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
409 Views

Similar Articles

[PageSpeed] 9

Try replacing the division formula by something along these lines:
=IF(A1="",NA(),B1/A1)
The chart engine ignores N/A
best wishes

-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tom" <Tom@discussions.microsoft.com> wrote in message 
news:570F8C53-DE3D-42C9-8CB0-A4A2E491BAA2@microsoft.com...
>I have data where there is a division by zero error because nothing is
> entered, but that will change when the data is entered. Is there a way I 
> can
> suppress the chart from putting 0's in its place so that there is a gap in
> the chart when there is a division by zero error? 


0
bliengme5824 (3040)
11/7/2007 5:30:27 PM
If you current formula is =A1/B1, try =IF(B1="",NA(),A1/B1)
-- 
David Biddulph

"Tom" <Tom@discussions.microsoft.com> wrote in message 
news:570F8C53-DE3D-42C9-8CB0-A4A2E491BAA2@microsoft.com...
>I have data where there is a division by zero error because nothing is
> entered, but that will change when the data is entered. Is there a way I 
> can
> suppress the chart from putting 0's in its place so that there is a gap in
> the chart when there is a division by zero error? 


0
David
11/7/2007 5:58:24 PM
Thanks, worked like a charm!!

"Bernard Liengme" wrote:

> Try replacing the division formula by something along these lines:
> =IF(A1="",NA(),B1/A1)
> The chart engine ignores N/A
> best wishes
> 
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
> 
> "Tom" <Tom@discussions.microsoft.com> wrote in message 
> news:570F8C53-DE3D-42C9-8CB0-A4A2E491BAA2@microsoft.com...
> >I have data where there is a division by zero error because nothing is
> > entered, but that will change when the data is entered. Is there a way I 
> > can
> > suppress the chart from putting 0's in its place so that there is a gap in
> > the chart when there is a division by zero error? 
> 
> 
> 
0
Tom7430 (437)
11/7/2007 6:13:02 PM
Reply:

Similar Artilces:

Need Error Number
Hi everyone! Using A02 on XP. Need to know what the error number is for this message: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the indes, or redefine the index to permit duplicat entries and try again." I need to show a specific message instead of that one. Is there a directory for errors? How do I find a list? Thanks in advance for any help or advice! -- Bonnie W. Anderson Cincinnati, OH ...

Not mentioned error
I've got someting rather strange here, any suggestions are welcome. I created a webap with 1 page and 1 codebehind file and also a webservice with 1 asmx and codebehind file. From a button on a crmform, the webapp page is called, collects information from my webservice that collects information from the crm service and metadataservice. the webpage shows all the active users in the crm system in a checkboxlist control. The user can check some fields and hits send. The webpage creates a string with xml in and calls a methode from the webservice which creates a crmmail object saves i...

Calendar Error 0x80040216
Microsoft CRM Unhandled Error Details: Server Error in '/' Application. -------------------------------------------------------------------------------- Server was unable to process request. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.Services.Protocols.SoapException: Server was unable to process request. Source Error: An unhandled exception was generated during the execution of the current web req...

division in access to round-up if division not whole number
when dividing a by b giving c; need to round-up c if it is not a whole number Excel has roundup but Access is devoid of this function. Round( a / b ) should give you what you need. That's actually Round( a / b, 0) where the number to the right of the comma is the number of decimals to round. As the helpfile in Access notes, if this is omitted, integers are returned. "ddiel" wrote: > when dividing a by b giving c; need to round-up c if it is not a whole number > Excel has roundup but Access is devoid of this function. "ddiel" <ddiel@discussio...

Change Percentage DIV/0! Error
I'm trying to see the change between (A) 2002 unit sales and (B)200 unit sales, and am using the following formula: =(B-A)/A This gives me the difference negative or positive, however if A (200 sales) is zero, then I get the DIV/0! error, how do I fix this? Thank you -- Message posted from http://www.ExcelForum.com Hi, A couple of things you might want to consider. One is that your formula will not take into account what the change is if the prior month base amount is negative. To fix that use the =IF(base=0,"",(current-base)/ABS(base)) This should help >-----Or...

Help on subscript out of range error (VB6/VBA)
Hi, I want to work with multiple excel workbooks en worksheets using a stand alone VB6 application. 1. First I open and close the workbooks and populate a listbox with sheetnames. 2. Then I select some sheets and fill an Array. (This array contains sheetnames from different workbooks) 3.The last step is to open and close the workbooks again and loop (For Each) through the sheets to get the information I need using the above mentioned array. The only problem is that I receive an "Subscript out of Range" error. This error occurs because I'm attempting tot access a worksheet...

Error Message in Money2002
Since yesterday whenever I start Microsoft Money, I get an error message saying that there is no disk in the drive and I have an option to continue or cancel. While I am still able to access my money file and enter transactions this message pops up every time I change a screen. What should I do? Solomon solman1@comcast.net ...

Outlook generating errors
Hi, I currently have a users laptop that is running W2K with office 2000. Outlook runs of exchange server. Whenever the user tries to create a new email, reply or forward an existing email outlook generates errors says it is creating an error log and shuts down. The problem is not profile specific, I've completely removed outlook and completed a fresh install with office service pack three installed. Has anyone got any ideas as to what could be causing this or know where the Outlook error logs are stored? Any help would really be appreciated Cheers David ...

shutting down due to error
I have tried to delete p16 of my 26 page newletter, because it keeps causing an error, I report and shuts down, I cant delete the page. How can I resolve this issue and not have to redo entire document. Is there a way to scan docs for errors and auto fix them in publisher online? Is your newsletter in booklet form? This article has different methods you can use, more times than not it will be a graphic. Have you tried deleting the objects first, or moving the page to the end of the publication. How to troubleshoot a damaged publication in Publisher http://support.microsoft.com/kb/19825...

ODBC
When I try to import a file using an ODBC dsn I get the following message in Excel 2002 and Word 2002: Unable to obtain list of tables from the data source. I am able to import the tables into Access however. Any assistance would be appreciated. ...

Outlook 2003 error message #3
I am recieving this error message when i attempt to send email messages to any other site outside of my own email provider "I.E. myself" Any help? 530 delivery not allowed to non-local recipient, try authenticating Keith <Tabo2@rennlist.com> wrote: > I am recieving this error message when i attempt to send > email messages to any other site outside of my own email > provider "I.E. myself" > > Any help? > > 530 delivery not allowed to non-local recipient, try > authenticating Did you do what the message requests? Did you check the au...

Formula error.
I have the following formula in cell M7, =IF(AND($I$3="n",$L$3="n"),0, IF(AND($I$3="n",$L$3="y"),IF(AND(K7>1,K7<cap),ROUND (K7*srate1,2), IF(AND($I$3="n",$L$3="y"),IF(K7>cap,ROUND(cap*srate1,2), IF(AND($I$3="y",$L$3="y"),ROUND(K7*csrate1,2),0)))))) The idea is to have the user answer two Yes or No questions in cells I3 and L3. The combination for a 'n' and a 'n' work fine. The two combinations for a 'n' and a 'y' work fine The 'y' and 'y' combination gi...

Uninstall errors with Outlook client
I have just installed MS CRM 1.2 on out network everythign appears ok. I have a new machine for our telesales person who was starting. Before I knew his name I thought that I would get his PC ready for him. I installed the 1.2 outlook client when I was logged on as the Domain Administrator and the client looked fine. However I need to add teh client to the users profile now that they have started work. When I logon as the user I can not install the client for them. I get an error saying that I should unistall it from the other user first. I wouold do this but when I logon as the admini...

Restore leading zero
I imported an excel spreadsheet with 10 digit ID numbers into Access, and it dropped all of the leading zeros in the ID number field. I changed the field type to text instead of numeric, but the zeros are gone. How can I replace those zeros without having to type them all in by hand? All of the ID numbers are 10 digits, some have 2 leading zeros, some 1, and some none. Or better yet is there simply a way to import the data again without losing the zeros? Thanks for your help. On Oct 23, 12:36 pm, Xerxov <schroder....@gmail.com> wrote: > I imported an excel spreadsheet with 10 d...

sbs 2008 console error
Good day, after the migration we have some problems with sbs 2008 console: we cannon't create the user with console. We can create with manual command. An exception of type 'Type: Microsoft.WindowsServerSolutions.Admin.Common.ADException, AdminCommon, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' has occurred. Timestamp: 03/09/2010 19:21:28 Message: La funzionalità Windows SBS Console non è stata configurata correttamente. Stack: in Microsoft.WindowsServerSolutions.Admin.Common.ProductADConstants.GetRolesDistinguishedName() ...

How do I average a formula without calculating zero values?
I want to average a column, except for the cells that have zero as a value. How do I do this? Thanks! =AVERAGE(IF(rng<>0,rng)) which is an array formula so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "KMHarpe" <KMHarpe@discussions.microsoft.com> wrote in message news:DA83589C-D838-4A91-8703-40B48668DBD6@microsoft.com... > I want to average a column, except for the cells that have zero as a value. > How do I do this? > > Thanks! ...

Convert Lead to Account produces an Error
I receive the following Error whe nattempting to convert a Lead to an Account. (See more Info at bottom of this post). Also - I can convert a newly created (test) Lead with no problem. ********************************** Microsoft CRM Platform Error Report: Error Description: Exception from HRESULT: 0x80042F09. Error Details: Details on this error have not been provided by the platform. Error Number: 0x80042F09 Source File: Not available Line Number: Not available Date: 04-26-2005 Time: 1:34:33 PM Type: Platform Error (2) Server: sbs.tiltrac.com ************************ More Inf...

Division help? #2
In MS Excel I would like to show a quotient in three separate columns such as 16/3 = 5 + 5 + 6. Can this be done? Please let me know a.s.a.p. I can't deduce the logic you are applying here. Can you post some more examples of what you want to input and how you would like the output to appear? Pete If you put 16 in A1, you can put this in B1: =int(a1/3) you can put this in C1: =b1 you can put this in D1: =a1-b1-c1 clayblay wrote: > > In MS Excel I would like to show a quotient in three separate columns such as > 16/3 = 5 + 5 + 6. Can this be done? Please let me know ...

I just install Office 2003 I get error- interface not registered .
I get this error INTERFACE NOT REGISTERED when using Outlook create mail when I attached a Word Doc to it, If I use Word to create e-mail with the same letter I get the same error. Regardless of which program I use the mail cannot be sent Make sure your Word version is 2003, too. Versions must match. "Thomsign" wrote: > I get this error INTERFACE NOT REGISTERED when using Outlook create mail when > I attached a Word Doc to it, > If I use Word to create e-mail with the same letter I get the same error. > Regardless of which program I use the mail cannot be sent Not...

The Microsoft agreement error
The Microsoft agrrement keeps coming up even and i agree to the question. end user agreement comes up to accept every time I open word, excel or outlook I am using 2003, I had to reinstall this on my new computer running win 7 after I accept it I can use it when I close down and try to open it again I get the message "You must accept the end user agreement" Anyone can help me on this I can not open my templates in Quick Books Pro. Log on to the computer as Administrator and accept the EULA. -- Hope this helps. Please reply to the newsgroup unless you wish to ava...

Microsoft Money 2004 Balance Error
When I change a CREDIT CARD account from "sorted by date (Increasing)" to "sorted by date (Decreasing)" the balance display messes up (try it, the math doesn't make any sense, create a credit card with 0 opening balance, and creater 3 transacations of $50, the math just doesn't make sense). The math doesn't add up? Any idea how I submit this to microsoft as a bug? Open a support case directly with Microsoft at http://support.microsoft.com. If you live long enough, they may pass the case up the chain as a possible issue. They are unlikely to call it a bug ...

Leading zeros #6
When entering an 'IF' formula to locate information on another spreadsheet the formula truncates the leading zeros in my argument. The information I want is tied to a reference with leading zeros i.e. 00123 and so on. How can i set the formula to accept the leading zeros to locate the correct information? Maybe you can use "00123" in your formula. =if(a1="00123", ... Or if 123 is in a cell, =if(text(a1,"00000").... But if that reference is just 123 and formatted to show leading 0's, you'll still want to use the numeric value 123--not the te...

Division as a %
What am I doing wrong. example: 100 divided by 3 = a third which = 33.3%. Using =b2/c2 and cell formulated for % gives me 3333% I am not understanding this. ss schreef in news:NlSxq.47805$th7.9112@newsfe07.ams2 > What am I doing wrong. > example: > > 100 divided by 3 = a third which = 33.3%. > Using =b2/c2 and cell formulated for % gives me 3333% > > I am not understanding this. 100/3 = 33.33 = 3333%. -- Alex. On 19/11/2011 19:50, Alex Plantema wrote: > ss schreef in news:NlSxq.47805$th7.9112@newsfe07.ams2 > >> What am I doing wrong. >> example:...

Error on conversion to UNICODE
Hi I have a popup window in my application, similar to the MSN taskbar popup. I set its window text by loading it from the resource file (LoadString), and set a CString data member in its class. Everything works fine with MBCS character set but when I change the project settings to use the UNICODE character set, the text appears completely mangled. On debugging, I see that the text is being set correctly in the data member. But, when the window gradually pops up I see that the text consists of junk characters What am I doing wrong? Any special considerations that I need to take care of...

Conditional format for Highlight first Minimum Value greater than zero
Hello All, I am using Office 2003 and have the following problem I am trying to use CF to highlight the first Minimum value greater than zero. I selected the range with A10 as active cell and used =A10=MIN($A$10:$A $15) in CF but this highlights Zeros I also tried =AND(A10>0,MIN($A$10:$A15)) but did not succeed Can anybody point me in the right direction TIA Rashid Khan Hi Rashid, Try this formula =A10=MIN(IF($A$10:$A$15<>0,$A$10:$A$15)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <prkhan56@gmail.com> wrote in m...