# Thread: Avoiding divide by zero errors (97 sr-2)

1. ## Avoiding divide by zero errors (97 sr-2)

<img src=/S/help.gif border=0 alt=help width=23 height=15>
I am using the formula to generate a percentage in E7.
<hr>=IF(C7>0,IF(D7>0,D7/C7,""),"")<hr>
The nested IFs protect me from divide by zero errors if either C7 or D7 are zero (or a negative number - which I need to trap as well). This isn't elegant, bit of kludge actually. I'm not that familiar with Excel - is there an easier and/or tidier way? <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

2. ## Re: Avoiding divide by zero errors (97 sr-2)

Just slightly more elegant:

=IF(AND(C7>0,D7>0),D7/C7,"")

Do you also need to trap text values in C7 or D7? In that case, try

=IF(AND(C7>0,D7>0,ISNUMBER(C7),ISNUMBER(D7)),D7/C7,"")

3. ## Re: Avoiding divide by zero errors (97 sr-2)

Hans <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
Thanks for this, I hadn't considered trapping text as well but it is a (very) good idea! From the context it appears to me that the AND function evaluates any number of IF type tests and will execute the first formula if all are true. If one is not true it executes the second. - <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> Is this right?
Many thanks and have a good weekend!

4. ## Re: Avoiding divide by zero errors (97 sr-2)

Hi Les,

The AND function itself doesn't evaluate the division (or nothing).

The AND function merely evaluates a series of conditions and returns TRUE if all of them evaluate to TRUE. It returns FALSE if at least one of the conditions evaluates to FALSE.

The IF function takes the first argument - in this case the result of AND(...) - and evaluates the second argument (the division) if the first argument evaluates to TRUE, otherwise evaluates the third argument (an empty string).

Have a nice weekend too.

Regards,
Hans

5. ## Re: Avoiding divide by zero errors (97 sr-2)

=IF(C7*D7>0,D7/C7,"")

The >0 bit is there, because you want to exclude negative numbers.

6. ## Re: Avoiding divide by zero errors (97 sr-2)

This can even be in the form

=IF(C7,D7/C7,)

if negative numbers are OK. Zero value numerators do not cause divide-by-zero errors.

A spreadsheet author would need to have appropriate reasons to ignore negative numbers.

7. ## Re: Avoiding divide by zero errors (97 sr-2)

Doesn't work if both C7 and D7 are negative though.

8. ## Re: Avoiding divide by zero errors (97 sr-2)

But it does...

9. ## Re: Avoiding divide by zero errors (97 sr-2)

The >0 bit is meant to meet a condition the OP mentions. If negs are allowed,

=IF(C7,D7/C7,0)

will of course suffice.

The reason why you added the "Zero value numerators do not cause divide-by-zero errors" to your reply escapes me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

10. ## Re: Avoiding divide by zero errors (97 sr-2)

I'm not sure if you are teasing me <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>, but in Excel, zero value numerators do not cause divide-by-zero errors, they return a zero value, where zero value denominators -do- cause divide-by-zero errors. I think you know that, but my point was that in some of the above solutions the reference to numerator D7 in the logical test argument of the =IF(,,) function was unnecessary.

And just to tease you back:

=IF(C7,D7/C7,)

is the same as

=IF(C7,D7/C7,0)

I'm such a bad typist, I try to use as few characters as possible! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

11. ## Re: Avoiding divide by zero errors (97 sr-2)

On my Excel 2000, if I set C7 = -2 and D7 = -1, then your formula produces 0.5 not blank as the question indicated was required, and as the other solutions provide.

12. ## Re: Avoiding divide by zero errors (97 sr-2)

Legare,

Although not fully applicable here for one reason, how could one forget Rory's star posting of last November (#87625) of the IFERROR function, which directly speaks to the issue of elegance.

Unfortunately, IFERROR is only concerned with whether Excel considers the formula passed to it as being in error. If the user considers certain conditions as being in error (like negative numerator or denominator), IFERROR won't catch these. IFERROR doesn't provide a way to parse the argument (ie, the formula) passed to it to see if anything violates user conditions or to examine cell entries that are part of the formula. Hmmm, too bad.

Fred

13. ## Re: Avoiding divide by zero errors (97 sr-2)

Now I understand why that terse "it doesn't work" comes from!

I interpreted the OP's question/request as a combination of "avoid #DIV/0! " and "a negative % as result." I should have added the interpretation the formula instantiates.

14. ## Re: Avoiding divide by zero errors (97 sr-2)

I didn't mean my response to be terse, I was just trying to point out that problem.

15. ## Re: Avoiding divide by zero errors (97 sr-2)

I want to thank all of you here <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>