Div / 0

Hello all
How can I get this formula to not return Div/0. 
N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
It works ok so long as one of either P39, P41, P43, P45 has a value greater 
than 0.
If all are "0" then it returns Div/0. if all are "0" the I need N32 to = 
N34-N30
I hope you can follow this so you can help. ???????

Thanks 
Chris
0
Utf
4/29/2010 5:46:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
499 Views

Similar Articles

[PageSpeed] 53

Hi,
=if(iserror(N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9),N34-N30,N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9)

"Chris" wrote:

> Hello all
> How can I get this formula to not return Div/0. 
> N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
> It works ok so long as one of either P39, P41, P43, P45 has a value greater 
> than 0.
> If all are "0" then it returns Div/0. if all are "0" the I need N32 to = 
> N34-N30
> I hope you can follow this so you can help. ???????
> 
> Thanks 
> Chris
0
Utf
4/29/2010 5:59:02 PM
"Chris" wrote:
> N32=N34
> -Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)
>+P45/16.9
[....]
> If all are "0" then it returns Div/0. if all are "0" the
> I need N32 to = N34-N30

I suspect you want:

=N34
- (if(E52=0,0,P39/E52)
    + if(C32=0,0,P41/C32) + P43/16.9 + N30)
+ P45/16.9

Note the absence of the use of SUM.  It was superfluous in your case.

Also note that the formula can be made a little more readable by applying 
some basic math to simplify it, to wit:

=N34
- if(E52=0,0,P39/E52)
- if(C32=0,0,P41/C32)
- P43/16.9 - N30 + P45/16.9

You can one step further:

=N34
- if(E52=0,0,P39/E52)
- if(C32=0,0,P41/C32)
- N30 + (P45-P43)/16.9


----- original message -----

"Chris" wrote:
> Hello all
> How can I get this formula to not return Div/0. 
> N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
> It works ok so long as one of either P39, P41, P43, P45 has a value greater 
> than 0.
> If all are "0" then it returns Div/0. if all are "0" the I need N32 to = 
> N34-N30
> I hope you can follow this so you can help. ???????
> 
> Thanks 
> Chris
0
Utf
4/29/2010 6:00:02 PM
Actually it's C32 and E52 who are that dasterdly deed doers!
This should work in N32...

=IF(OR(E52=0,C32=0),N34-N30,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9)

-- 
Hope this helps.  
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Chris" wrote:

> Hello all
> How can I get this formula to not return Div/0. 
> N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
> It works ok so long as one of either P39, P41, P43, P45 has a value greater 
> than 0.
> If all are "0" then it returns Div/0. if all are "0" the I need N32 to = 
> N34-N30
> I hope you can follow this so you can help. ???????
> 
> Thanks 
> Chris
0
Utf
4/29/2010 6:00:07 PM
Chris,

It's C32 & E32 that must be populated for the formula to work, try this.

=IF(COUNT(C32,E52)=2,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9,"")
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Chris" wrote:

> Hello all
> How can I get this formula to not return Div/0. 
> N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
> It works ok so long as one of either P39, P41, P43, P45 has a value greater 
> than 0.
> If all are "0" then it returns Div/0. if all are "0" the I need N32 to = 
> N34-N30
> I hope you can follow this so you can help. ???????
> 
> Thanks 
> Chris
0
Utf
4/29/2010 6:04:01 PM
And to make it easier to follow, get rid of the unneeded Sum function and 
parentheses:
=IF(COUNT(C32,E52)=2,N34-P39/E52+P41/C32+P43/16.9+N30+P45/16.9,"")

Fred

"Mike H" <MikeH@discussions.microsoft.com> wrote in message 
news:20BF41EB-84BD-4E0A-A56A-C5D481BFC0AF@microsoft.com...
> Chris,
>
> It's C32 & E32 that must be populated for the formula to work, try this.
>
> =IF(COUNT(C32,E52)=2,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9,"")
> -- 
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Chris" wrote:
>
>> Hello all
>> How can I get this formula to not return Div/0.
>> N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
>> It works ok so long as one of either P39, P41, P43, P45 has a value 
>> greater
>> than 0.
>> If all are "0" then it returns Div/0. if all are "0" the I need N32 to =
>> N34-N30
>> I hope you can follow this so you can help. ???????
>>
>> Thanks
>> Chris 

0
Fred
4/29/2010 9:28:42 PM
I think you've got some signs reversed compared with what the OP was asking 
for, Fred.

Try =IF(COUNT(C32,E52)=2,N34-P39/E52-P41/C32-P43/16.9-N30+P45/16.9,"")
--
David Biddulph


"Fred Smith" <fsmith11@yahooo.com> wrote in message 
news:uEB6#L#5KHA.4940@TK2MSFTNGP05.phx.gbl...
> And to make it easier to follow, get rid of the unneeded Sum function and 
> parentheses:
> =IF(COUNT(C32,E52)=2,N34-P39/E52+P41/C32+P43/16.9+N30+P45/16.9,"")
>
> Fred
>
> "Mike H" <MikeH@discussions.microsoft.com> wrote in message 
> news:20BF41EB-84BD-4E0A-A56A-C5D481BFC0AF@microsoft.com...
>> Chris,
>>
>> It's C32 & E32 that must be populated for the formula to work, try this.
>>
>> =IF(COUNT(C32,E52)=2,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9,"")
>> -- 
>> Mike
>>
>> When competing hypotheses are otherwise equal, adopt the hypothesis that
>> introduces the fewest assumptions while still sufficiently answering the
>> question.
>>
>>
>> "Chris" wrote:
>>
>>> Hello all
>>> How can I get this formula to not return Div/0.
>>> N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9
>>> It works ok so long as one of either P39, P41, P43, P45 has a value 
>>> greater
>>> than 0.
>>> If all are "0" then it returns Div/0. if all are "0" the I need N32 to =
>>> N34-N30
>>> I hope you can follow this so you can help. ???????
>>>
>>> Thanks
>>> Chris
> 

0
David
4/29/2010 11:53:57 PM
Reply:

Similar Artilces:

[ANN] Office 2008 12.0.1 update
Hi All, The Office 20089 12.0.1 update is now available on Mactopia: http://www.microsoft.com/mac/downloads.mspx It should soon also be available through Auto-update. 114.1MB!!! The KB describing the update is not yet live on the MS site so at this time I do not yet have the release note, but it will be available here: http://go.microsoft.com/fwlink/?LinkId=108496 The updater is a .mpkg package (so it is installable through over a network through ARD :-) ). Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://...

Migrate CRM 4.0 instance to another server on a different domain
Hello, What would be the best way to migrate our current CRM database to another server on a different domain but still within the same forest? We initially had intended on implementing our CRM at our off-site datacenter but due to some DNS/Network issues we weren't able to add users across different domains. Our deadline was fast approaching so we setup a server locally. Now that we have the issue resolved at our datacenter, we are ready to move the local database to the datacenter. What is involved with this process and what would be the best way to do this? Thank you very much...

IntegrationManager9.0\GP9
I have a SQL Server 2005 database that the source records are in and I setup an ODBC driver using the SQL Server driver in the ODBC Administrator. I choose the ODBC datasource from the Integration Manager, Add Source Dialog, Simple ODBC Adapter. When I try to choose a table from the drop down list, I get the following Error Message This key is already associated with an element of this collection Time: 4/24/2008 10:25:34 AM Source: TTGCommon Message: This key is already associated with an element of this collection Number: 457 Call Stack: ODBC.GetODBCTables frmQueryProperties.ListTab...

GP 7.5 to 8.0 Upgrade: Problem with Extended Pricing Upgrade
I just performed an upgrade for a customer running Great Plains Professional with Extended pricing (7.5 to 8.0). After the upgrade, when entering an SOP Document, I would recieve a pricing error indicating that no price could be obtained for the item. This affected all items and all customers. To attempt to fix the problem, I ran check links on the Multicurrency Setup file. This produced the following error: Several Price Sheets exist with no functional currency. These price sheets have been removed. Upon further review, I realized that all of my price sheet data (header and detail...

Microsoft: How the 4.0 E-mail Router service query Exchange mailbo
We found the CRM 4.0 E-mail Router service query Exchange mailbox via a 'half' SMTP address, not via the primary SMTP address or SAM account. e.g.: In our case, we have a test user in CRM, the user's SamAccount name is: TU341-NBG125, SMTP mailbox: TUser@domain.com which is the user's primary email address in CRM. The CRM E-Mail Router service should be making a request to Exchange mail server for the Test users mailbox using http://ExchangeMailServer/Exchange/TU341-NBG125 instead it is using http://ExchangeMailServer/Exchange/TUser We also find out that the service acco...

#DIV/0! Error #3
Hi, In the formula listed below sometimes I put zeroes in the 2 cells or I leave them blank but I get the #DIV/0! error. How can I add the ISError function to my formula. Its driving me crazy. =SUM(B14-C14)/B14 Thanks In advance. Kenny Firstly, you do not need SUM. This is used in formulas such as SUM(A1:A100) to sum a range. You need just = (B14-C14)/B14 But if B14 is zero you will get DIV error So =IF(B14<>0,(B14-C12)/B14,"Zero value in B14") best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Kenny16301 via OfficeKB.com" ...

OWA problem. 440 Login Timeout https://emilserver/exchweb/bin/auth/owalogon.asp?url=https://emailserver/exchange&reason=0
I installed Exchange2003 in windows 2003 server. it runs well . but few days ago, when I using https://emailserver/exchange to access OWA it automatic change address to https://emilserver/exchweb/bin/auth/owalogon.asp?url=https://emailserver/exchange&reason=0 and display below: 440 login Timeout. exchange2003 other part is good. user can use POP3 and SMTP. so I don't wan't reinstall all exchange2003 . I need find a tools or some thing can reinstall or reset OWA in IIS . who can help me? Thanks A lot. Best Regards Jeff HI I ve the same problem . When I select Form Authe...

Install CRM 4.0 with CRM 3.0 licences + upgrage
Dear all, my customer has bought licenses for CRM 3.0 and then the upgrade to 4.0. Can we now install directly the 4.0 with the latest licenses or are we forced to pass through the 3.0 installation and then upgrade it? Many thanks in advance Bye ale Yes "locatao" <locatao@discussions.microsoft.com> wrote in message news:02CE8EF6-8557-47E1-BF94-0116073098B8@microsoft.com... > Dear all, > my customer has bought licenses for CRM 3.0 and then the upgrade to 4.0. > Can we now install directly the 4.0 with the latest licenses or are we > forced to pass through the 3...

If difference is 0 or below how do show just 0
I'm not sure how to set this up? =(D21-B13)+(A5-B12) I only want to add the difference of (A5-B12) to (D21-B13) if it is positive number so if the difference is 0 or below how do I get it t just show 0? thanks for your help. jac -- Message posted from http://www.ExcelForum.com =D21-B13+MAX(A1-B12,0) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) -------------------...

Unable to install CRM 3.0 client on Outlook 2003
I originally attempted to install the CRM 3.0 laptop client for Outlook 2003 on a standalone WinXP machine at a customer's home office (in a workgroup; not joined to the customer's domain). Received the following error message after the first list of missing components were successfully added during the installation prerequisite phase: ================ "Action Microsoft.CRM.Setup.Common.Analyzer+CollectAction failed. %1 is not a valid Win32 application." ================ Pressing the "Ignore" button at this prompt results in the following message: "Data P...

CRM 3.0 internal recipients don't recieve mails
Hi everybody... We've installed CRM 3.0, including the Exchange Route Component on an Exchange Server 2003. Inbound and outbound mails, mail tracking and so on are working correctly, but if someone sends an email using the CRM WebInterface or the CRM Outlook Client and uses an email-recipient of our internal domain (e.g. as TO, CC or BCC with adress username@mydomain.com) the mail is not send. An activity that describes that email ist correctly generated by the CRM, but the mail itself is not send to any of the listed recipients, if at least one of them belongs to our domain. Doe...

CRM 3.0 Enhancements
I'm preparing training documentation to highlight the enhancements/changes from CRM 1.2 to 3.0. I've been unable to find a single document that outlines the changes. Does something like this exist? I apologize in advance if this has already been asked. Hello Donna, does this help? http://www.microsoft.com/dynamics/crm/product/whatsnew.mspx ============================== John O'Donnell Microsoft CRM MVP http://www.crowechizek.com/microsoft "Donna Sharp" <DonnaSharp@discussions.microsoft.com> wrote in message news:FA8C8A32-5D30-447F-9B87-3BCF3897E232@...

Knowledge Base in CRM 3.0
Hi, In MSFT CRM, is it possible to set some approval rule before the article being published? Let say user A create the article and submit it. Once it's being submitted, there is a group of people who can review this article. Then this group of people can approve/reject it. Is it possible? I did not see the WF associated with article/KB. Another problem that i face, after i publish the article, when i browse to KB> search and choose full text search, keyword search, title search for the article that i just created, i could not find the article at all. I can find my solution if ...

Adding a leading zero "0" to a number in a cell
I am trying to create a formula that will add a leading zerto to any number that comes in with less than 5 digits. This is for a spreadsheet that will carry over zip codes for a mailing. I want to know if I can write a formula and if so, what it is that if a column of numbers just has 234 that the formula will automatically add the two zeros making it 00234. Or if it is 2345 it will convert it to 02345. Also - will the leading zeros (for lack of a better term) be hard coded so that when you extor to a csv or another format, the zeros will remain. Thanks -- Chip supposi...

delete columns if 0
i am using the following code to delete the complete row: Range("B:B").Select Selection.AutoFilter Field:=1, Criteria1:="0" On Error GoTo line1 With Sheet1.Range("b:b") Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete End With line1: what i need is to delete only columns a:g (not the entire row) if the value in column b = 0. any help is appreciated. jat Try some code like the following. Change the line Set WS = Worksheets("Sheet1") '<<< Change to appropriate sheet to the app...

eConnect 8.0.2 error
Hello, I am new to eConnect. we just bought great plains and want to get a jump on intergrations of our other systems while the implamentation team meets with the other dept and do their thing. So I was trying to do the example from the help file and I get the following error: System.Security.SecurityException: Requested registry access is not allowed. Server stack trace: at Microsoft.GreatPlains.eConnect.EventLogging.WriteLog(String Info, EventLogEntryType LogType) at Microsoft.GreatPlains.eConnect.eConnectMethods.eConnect_EntryPoint(String ConnectionString, ConnectionStringTyp...

CRM 4.0 Cisco Unified Connector
Team, Does anyone have any experience with the Cisco CRM Connector in terms of hardware/software requirements, configuration…etc I am also not sure if the connector for CRM 4.0 is available… I believe the 4.0 Cisco connector is due for release imminently... :) ...

Error Install BP 4.0
Hi i Have the following Virtual Envioroment W2003 Enterprised R2 SQL-2005 Pck 2 GP-10 Pck2. WSS 3.0 I'm on local machine and the error is the following !Installer Information! An Error ocurred while applying security settings. nt authority\network services is not a valid user or group. This could be a problem with the package, or a problem connecting to a domain controller on the network. Check your network connection and click retry, or cancel to end install. Please, give a hint!!!! Jaime O. ...

CRM 3.0 and action pack
CRM 1.2 was part of Action Pack. Does anyone know if 3.0 will be also? If not will it be free to Certified Parters...Small Business Specialists? Yes, plans are for it to be in Action ack and to also be available to certified partners. -- Matt Parks MVP - Microsoft CRM "Mark Bowles" <MarkBowles@discussions.microsoft.com> wrote in message news:2869E396-C733-43DB-875E-69B69673D38E@microsoft.com... CRM 1.2 was part of Action Pack. Does anyone know if 3.0 will be also? If not will it be free to Certified Parters...Small Business Specialists? I got my Certified Partner MSDN...

RMS 2.0 sp1 won't install on Server 2003
I have installed the service pack on all 22 work stations successfully, but on a Server 2003 config'd as a terminal server I get a Hotfix Installer message: "The update cannot be applied" This is a big concern, as starting tomorrow I will be running sp1 on all workstations except this server. Your suggestions highly appreciated. I have been running RMS for 3 years on this server and have done updates for the RMS 1.2 and 1.3 without problems RMS 2.0 has been running smoothly on the terminal server for all users except three users in accounting who require 20 seconds to p...

Pie Chart not to display 0 Values
Good day, I have an excel Table something like below. Column A Column B Item 1 24% Item 2 13% Item 3 0% Item 4 3% Item 5 0% The Values in Column B are being automatically updated from another table. There are 24 Items in the table and usually about 10 of them are 0, in a given period of time. Is there any way that the chart can be intelligent enough to skip the 0 Values and display only values greater than 0. This would clear the clutter on the chart. I am using a pie chart for the purpose. Thanks for Help _________________ Best Regards Khawar Andy Pope has instructions on his web sit...

GP 7.5 and Acrobat 6.0
Are these two compatible? I have been able to print to the PDF printer in the past, but every now and then it doesnt work. I get a printer error. Would this be less of an issue if I installed an older version of Acrobat? If I upgraded Great Plains, what version of Acrobat would I need then? Please help... Chris There was a problem with Adobe v6, but it was supposedly fixed in a 7.5 service pack. Are you on the latest sp? "Chris" <chrishoche@odysseylogistics.com> wrote in message news:EFBB1C65-0DC1-4E8F-9F6E-1EE4E8884CD7@microsoft.com... > Are these two compatible? ...

CAsyncSocket connect() returns 0
Hi, My CAsyncSocket::Connect() always returns 0 (which indicates an error) but the connection is accepted by the server socket and the client OnConnect() and OnSend() events are fired. So everything seems to be working ok, but why does Connect() returns 0 ???? Call GetLastError to see why it is returning 0 AliR. "RAN" <nijenhuis@wish.nl> wrote in message news:1185808912.678740.270420@l70g2000hse.googlegroups.com... > Hi, > > My CAsyncSocket::Connect() always returns 0 (which indicates an error) > but the connection is accepted by the server socket and the cli...

eConnect 9.0 TRDISAMT is missing in XML
Hi, I've just upgraded from eConnect 7.0.4 to 9.0; I'm populating an amount into TRDISAMT property of the taSopHdrIvcInsert class, but it doesn't show up in the xml - so I get an eConnect eventlog error saying the document amount does not equal subtotal + freight+misc+taxamt+etc... The EventLog entry also says the parameter TRDISAMT was not passed in Has anyone experienced a problem with this? ...

Informacion a Cerca de Vender MS CRM 3.0 En LIma
Saludos... Me gustaria saber.. con quien podria comunicarme para poder ser un distribuidor! del producto Crm 3.0 en Lima Peru ...