#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ?

[Guest]

Hi all,
I have the following function:
=IF($E13>0,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?

 

[David Biddulph]

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($E13>0,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))),"")))

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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($E13>0,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($E13>0,SUM(G13/(PI()*$B13*($E13/2)^2*25.4^3)),IF($E13=0,SUM(G13/($B13*$C13*$D13*25.4^3)),"")))

Rick