If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ?
Hi all,
I have the following function: =IF($E130,SUM(G13/(((($E13/2)/0.0393700787)*(($E13/2)/0.0393700787)*3.14159265)*$B13/0.0393700787)),IF($E13=0,SUM(G13/(($B13/0.0393700787)*($C13/0.0393700787)*($D13/0.0393700787))),"")) All works fine when there are values filled into B,C and D13, or B & E13. Thats great, as intended...but....when there is no value in B13,C13,D13 or E13, I get the error #DIV/0! in my cell. Why is the very end part of my function, the ' Otherwise "" ' [,""] not working? I need it to just show a blank cell when there are no numbers in B,C,D or E13? Any ideas? |
#2
|
|||
|
|||
#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ?
You don't get to your final part of the function because you've satisfied
the IF(E13=0,... condition (as an empty cell counts as zero). If you specifically want to return an empty string if your 4 input cells are all empty, you can test for that: =IF(COUNT($B13,$C13,$E13,$G13)=0,"",IF($E130,SUM( G13/(((($E13/2)/0.0393700787)*(($E13/2)/0.0393700787)*3.14159265)*$B13/0.0393700787)),IF($E13=0,SUM(G13/(($B13/0.0393700787)*($C13/0.0393700787)*($D13/0.0393700787))),""))) -- David Biddulph "dim" wrote in message ... Hi all, I have the following function: =IF($E130,SUM(G13/(((($E13/2)/0.0393700787)*(($E13/2)/0.0393700787)*3.14159265)*$B13/0.0393700787)),IF($E13=0,SUM(G13/(($B13/0.0393700787)*($C13/0.0393700787)*($D13/0.0393700787))),"")) All works fine when there are values filled into B,C and D13, or B & E13. Thats great, as intended...but....when there is no value in B13,C13,D13 or E13, I get the error #DIV/0! in my cell. Why is the very end part of my function, the ' Otherwise "" ' [,""] not working? I need it to just show a blank cell when there are no numbers in B,C,D or E13? Any ideas? |
#3
|
|||
|
|||
#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ?
The first part of your formula divides G13 by the product of various numbers.
If B13 is zero (or blank), this divisor will also be zero thus giving an error. The second part (which will be evaluated if E 13 is zero or blank) does something similar, except the divisor will be zero if *any* of B13, C13 or D13 are zero. If you want the formula to not evaluate if any of B13, C13, D13 or E13 are zero (or blank) then you would need something like =If((b13*c13*d13*e13)=0,"",...) Eric "dim" wrote: Hi all, I have the following function: =IF($E130,SUM(G13/(((($E13/2)/0.0393700787)*(($E13/2)/0.0393700787)*3.14159265)*$B13/0.0393700787)),IF($E13=0,SUM(G13/(($B13/0.0393700787)*($C13/0.0393700787)*($D13/0.0393700787))),"")) All works fine when there are values filled into B,C and D13, or B & E13. Thats great, as intended...but....when there is no value in B13,C13,D13 or E13, I get the error #DIV/0! in my cell. Why is the very end part of my function, the ' Otherwise "" ' [,""] not working? I need it to just show a blank cell when there are no numbers in B,C,D or E13? Any ideas? |
#4
|
|||
|
|||
#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ?
You don't get to your final part of the function because you've satisfied
the IF(E13=0,... condition (as an empty cell counts as zero). If you specifically want to return an empty string if your 4 input cells are all empty, you can test for that: =IF(COUNT($B13,$C13,$E13,$G13)=0,"",IF($E130,SUM( G13/(((($E13/2)/0.0393700787)*(($E13/2)/0.0393700787)*3.14159265)*$B13/0.0393700787)),IF($E13=0,SUM(G13/(($B13/0.0393700787)*($C13/0.0393700787)*($D13/0.0393700787))),""))) And, to follow up on David's posting, you can shorten your main equation by using Excel's built-in PI() function and replacing all the divisions by 0.0393700787 with a multiplication by its reciprocal 25.4 (you are doing a conversion involving millimeters and inches, right?). By the way, using 25.4 this way will yield an ever-so-slightly more accurate result as 25.4 is an exact conversion value whereas 0.0393700787 is a rounded (to 10 decimal places) one. In addition, you can remove some extraneous parentheses and combine like terms to further simplify it. Unless I screwed the math up somewhere (you should check to make sure I didn't), this is the final (modification to David's posted) formula that I come up with... =IF(COUNT($B13,$C13,$E13,$G13)=0,"",IF($E130,SUM( G13/(PI()*$B13*($E13/2)^2*25.4^3)),IF($E13=0,SUM(G13/($B13*$C13*$D13*25.4^3)),""))) Rick |
Thread Tools | |
Display Modes | |
|
|