Avoiding divide by zero errors (97 sr-2)

Topic

New Reply

I am using the formula to generate a percentage in E7.[indent]

---

=IF(C7>0,IF(D7>0,D7/C7,””),””)

---

[/indent] 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?

Reply | Quote

Replies

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,””)

Reply | Quote

Hans
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. – Is this right?
Many thanks and have a good weekend!

Reply | Quote

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

Reply | Quote

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

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

Aladin

Reply | Quote

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.

Reply | Quote

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 .

Aladin

Reply | Quote

Aladin, I was replying to several parts of the thread, not just your response.

I’m not sure if you are teasing me , 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!

Reply | Quote

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

Reply | Quote

But it does…

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Aladin

Reply | Quote

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

Reply | Quote

I want to thank all of you here

I’ve learned a great deal about excel from your responses.

have one on me!

Reply | Quote