Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide by zero error
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide by zero error
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... -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113585 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide by zero error
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 --- "Jacq" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide by zero error
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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide by zero error
Try
=IF(C10+0=0,0,((C13/C10)-E13)/((SQRT(E13*((1-E13)/C10)))*(SQRT(1-F10)))) If this post helps click Yes --------------- Jacob Skaria "Jacq" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Divide by zero error | Excel Worksheet Functions | |||
Divide by Zero Error | Excel Worksheet Functions | |||
divide by 0 error jmp | New Users to Excel | |||
Divide by zero error | Excel Discussion (Misc queries) | |||
Divide by zero error | Excel Discussion (Misc queries) |