Divide by zero error

[Jacq]

Whenever I use the below formula, I get a divide by zero error if one of the
numbers it uses is zero. Can someone tell me how to adjust the below formula
to make it display zero instead of the divide by zero error?

=((C13/C10)-E13)/((SQRT(E13*((1-E13)/C10)))*(SQRT(1-F10)))

Thanks

 

[jamescox]

Really, that's probably the wrong way to approach the problem. A better
way would be to use data validation on the cells your formula references
so that user's can't enter zeros.

But if you want what you say you want, the simplest way to do it is
something like this:

=IF(ISERROR(((C13/C10)-E13)/((SQRT(E13*((1-E13)/C10)))*(SQRT(1-F10)))),0,((C13/C10)-E13)/((SQRT(E13*((1-E13)/C10)))*(SQRT(1-F10))))

Basically, that says If your original formula returns an error, return
a zero. If not, return the calculated value. I'm suggesting the
ISERROR function because (as you may have noticed) a value greater than
1 for F10 also causes your formula to generate an error.

You could simplify the above a bit by using just the denominator of
your calculation (the (SQRT(E13*((1-E13)/C10)))*(SQRT(1-F10)) piece )
because you can't get a divide by zero unless that piece evaluates to
zero. But if you just check to see if that is zero, you miss the
'protection' of detecting the issue of the F10 value I mentioned above.

Hope this helps...

 

[Max]

You could try something like this:
=IF(OR(C10={"",0}),0,yourformula)
which checks the denominator part of your formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Max]

Just adapt/extend it if necessary, to trap all the cells:
=IF(OR(C10={"",0},E13={"",0}),0,yourformula)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Jacob Skaria]

Try

=IF(C10+0=0,0,((C13/C10)-E13)/((SQRT(E13*((1-E13)/C10)))*(SQRT(1-F10))))

If this post helps click Yes