formula help #4

I could use some help in creating a statement that avoids the div/
error in the following formula

=SUM(((G4/G40)*J40)+((L4/G4)*J42)+((Q4/G41)*J41))

at any point  G4,L4,Q4 could be a value of 0.  G40,J40,J42,G41,J4
always have values.

I'd like the cell to show a value of 0 when there are not any value
for  G4,L4,Q4

THANKS

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
0
12/1/2003 5:03:53 PM
excel 39879 articles. 2 followers. Follow

1 Replies
437 Views

Similar Articles

[PageSpeed] 19

Try something like

=IF(OR(G4=0,G40=0,G41=0),0,SUM(((G4/G40)*J40)+((L4/G4)*J42)+((Q4/
G41)*J41)))


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"kilroy" <kilroy.xrpub@excelforum-nospam.com> wrote in message
news:kilroy.xrpub@excelforum-nospam.com...
>
> I could use some help in creating a statement that avoids the
div/0
> error in the following formula
>
> =SUM(((G4/G40)*J40)+((L4/G4)*J42)+((Q4/G41)*J41))
>
> at any point  G4,L4,Q4 could be a value of 0.
G40,J40,J42,G41,J41
> always have values.
>
> I'd like the cell to show a value of 0 when there are not any
values
> for  G4,L4,Q4
>
> THANKS!
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from
http://www.ExcelForum.com/
>
> ~~Now Available: Financial Statements.xls, a step by step guide
to creating financial statements


0
chip1 (1821)
12/1/2003 5:10:04 PM
Reply:

Similar Artilces:

macros in excel #4
Is it possible to set up a macro in excel to print report with conditions "Jai" wrote: > Is it possible to set up a macro in excel to print report with conditions Can you elaborate on "Conditions" and what kind of report it is? May be I can pitch in .. I am a beginner too, but I can look around. I am trying to set up a report from database of vendors. Each vendor has it's own number. Some vendors are grouped together. In report, there should be list of each vendors and all grouped vedors should have subtotal for its data. I know it's easy to do a subt...

CRM 3.0/4.0: Views to show record that is created X Days Ago
I have a simple requirement to create a View with condition that the record is created x days ago, for example the record that is created 3 days ago. The only operator that is available for datetime (e.g. createdon) is only Last X Days, which if i show Last 3 Days, will show the records that were created today, yesterday, and 2 days ago. Is there any operator or any way to show the record that were created 3 days ago? I try to put condition "createdon Last 3 Days" and "NOT createdon Last 2 days", but there is no "NOT" operator in CRM. I try to insert new attribut...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Help with DLookup
I have a DLookup function on my form and it works by finding the information once I exit and then re-enter the form, it does not bring in the information as soon as I enter the information in the lookup field. =DLookUp("[Employee Name]","[Employee Information]","[Employee ID]=forms![Timesheet Entry]![Employee ID]") Does anybody know why it is not updating immediately? All help is greatly appreciated Where are you using this expression -- as the control source of a textbox? More details about the form's design and how you're using the form, please....

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

HELP- error Outlook
What's this mean and how to fix? in error dialogue box: "c:\program~1\micro~2 \office\outlook.exe" Abnormal program termination .. ...

help diagnosing what's going on in ExchangeServer2000
I'm fairly ignorant of exactly how ES2000 works and would like some general information to help in my diagnosing why some emails are not being delivered to certain email domains (more correctly, why some emails are not being forwarded actually...ES2K is part of a voicemail system where some users have ..wav copies of the voicemails forwarded to their various off-site email accounts) . Here are my questions: 1. How can I see what emails are being forward? Is there a file/folder akin to the SentItems folder in Outook? 2. If there are send failures, where can I find them and how can ...

Auto Recovery #4
Hello everybody I have in excel option the autorecovery function on and is saving in the following path : C:\Documents and Settings\mnikolaou\Application Data\Microsoft\Excel The problem is that i was working a file and make some chanes while my computer crashes. When i made a restart it was imposible to find out the file i was working. not even in a recovery format. Is that option works properly or i have to make some other changes in the options in order to have backup files while i am working? Thanks in advance Manos Auto Recovery is supposed to work automatically so that you do n...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

Report Manager #4
Hi I have created a cusomised toolbar but when I customise the toolbar I cannot find "Report Manager" anywhere to add to it. The odd thing is that it is on the main menu but does not appear in the customisable dialogue box. Does anyone know where to find it, so I can put it on my toolbar. Cheers -- All help much appreciated. Thanks I'm guessing you created this customized toolbar manually (not by code). If that's the case and you have "report manager" under the existing tools menu already (or is it under Data???). Tools|Customize (just to see that dialog ...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

help with unusual request
Hello everyone, My boss has hit me with an odd request and I would like to ask for someone to point me in the right direction. What he wants is everytime anyone sends or recieves an email that a copy is made that he can get to anytime he wants. I talked to him about our daily backups, but he wants something that's realtime and that he can get to whenever he wants. I'm stuck and looking for ideas. We're currently on Exchange 5.5 with all of the latest service packs and patches. Any and all help is very much appreciated. You can enable "message journaling" fea...

Help designing a checklist in access
Hello All My company currently uses excel checklists to ensure that projects are completed fully. we have around 10 different checklists. each checklist (excel file) has 6 sheets, each sheet 15 questions or steps. most answers are Y,N,NA and the employees put their initials in the correct answer column. Many questions are redundant throughout the 10 checklists. I would like to create the checklists in access because their are too many excel files getting lost and if we change question/ step. we have to change it multiple times. the employees need to enter a clients name and then choose the c...

Timesheet Pseudo Tied Mode
I'm hoping that I might have a colleague out there who could help me offline figure out how to install this open source utility (unsupported by Microsoft but apparently works OK for some of their clients) that streamlines the Timesheet process (automatically synchronizes the data into My Tasks whenever a timesheet is saved). I'm not sure where to start and may need someone to bounce questions off of - especially if you've been successful with it. Thanks, Andy Novak UNT Hi Anovak, We installed it (still using the version 1.3 based on event handler) and we're usi...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

Why are checks printing incorrect amounts? Please help.
When I print a Payables check batch, several invoices within the batch are printing incorrectly as $0.00 amount, even though it should be paying off a certain amount. The problem seems to be with invoices that are applied to credit memos. Say I have an invoice for $500 and I apply a credit memo of $80 to the invoice, when I go back to pay the invoice, it should pay $420, but instead it shows $0.00 under amount paid column of check stub. Does anyone know why this is occuring? By the way, I am using MICR check printing. ...

Please help with Exchange 2003 SMTP Connector
I'm trying to create an SMTP Connector in Exchange Server 2003, to have our company's email sent out (Internet/External emails) to a specific mail forwarder server (the "specific" server is our ISP/Firewall provider server). But I'm having problems understanding how to set this up properly with our Exchange 2003 Server, I know I have to create a new SMTP Connector but not sure what settings to make. Could someone please help me with this. Thanks William On Thu, 25 Feb 2010 22:41:39 -0500, "Bill" <wstyler@verizon.net> wrote: >I&...

Help in turning off underlining and blue font
Hello, Does anyone know if there is a way to turn off the automatic underlining and blue font of the text when entering a email address in a cell? Thanks, Bob Tools / Autocorrect options / Autoformat Tab / Replace Network paths with hyperlinks - Deselect it -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ----------------------------------------------------------------------...

Help!!Getting audio files to play continuously during slide show?!
I have Powerpoint 2002, and i am having a hard time getting the music file to play continuously during the slide show. it plays on the first slide (which is the only slide that has the little speaker picture on it), but then it doesn't transition to the next slide until after the song is done, when i want the song to play while the slides are transitioning. HELP please! Mega, Have a look here and see if it helps, there are tuturials in video demonstrating the process. http://www.pptfaq.com/FAQ00047.htm -- Luc Sanders MVP - PowerPoint "MegaB86" <MegaB86...

Formula causing run-time error in Excel Programming
This is a repost. I have the following line of code that I am trying to drop in a cell via Access VBA but I keep getting a Run-time error 1004, Application-defined or object-defined error. xls.cells(Rw, Col + lngColumn).value = "=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000,ROW(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000)-ROW(ET_Raw_Data!$C$2),0,1)),--(ET_Raw_Data!$A$2:ET_Raw_Data!$A$20000=A" & lngColumn + 1 & "),--(ET_Raw_Data!$D$2:ET_Raw_Data!$D$20000='Feature Request'))" I already have 2 other formulas that are being add...