# DIV/0! error in Excel

[Guest]

I have a function that divides two numbers, sometimes those numbers are 0 so
the result is # DIV/0!. My second function is based on the result of the
first function. Since there isnt always data available for the first function
I get the # DIV/0! error and my final calculation wont calculate. I need the
final calculation to disregard any 0 cells or # DIV/0! errors and still
calculate the data that is available.

 

[Guest]

Hi
You need to wrap your division with an IF() so it will evalluate the result
before it puts anything in the cell. Try something like:
=IF(A2>0,A3/A2,0)

 

[Guest]

Hi
You need to wrap your division with an IF() so it will evalluate the result
before it puts anything in the cell. Try something like:
=IF(A2>0,A3/A2,0)

Or perhaps a more appropriate approximation fudge would be:

=IF(A2>0,A3/A2,1E300)

Bill

 

[Guest]

Hi
You need to wrap your division with an IF() so it will evalluate the result
before it puts anything in the cell. Try something like:
=IF(A2>0,A3/A2,0)

Or perhaps a better approximation fudge would be:

=IF(A2>0,A3/A2,If(A3>0,1e300,-1e300))

Bill

 

[Guest]

Hi Bill
Why do you say that putting 1e300 would be a 'better approximation fudge'? I
don't understand what that contributes towards the result!
Cheers.

 

[Lewis Clark]

Because 1e300 is a very large number. When you divide by zero, you get an
infinitely large number.

Which one you use should depend on how you use this value in the next
calculation.

 

[Guest]

Hi Bill
Why do you say that putting 1e300 would be a 'better approximation fudge'? I
don't understand what that contributes towards the result!
Cheers.

It's a matter of how to handle errors. For example, say you're trying
to evaluate your return on investment. You invest $5 and get $10 back.
Divide 10/5 = 2x return as desired.

Now what would you get if for some reason the investment was $0? Your
original example would say 0 return which is very plainly wrong and
misleading. My version would return a huge number (1E200) which is also
too small, but less so.

This would allow downstream computations to continue, but would show up
in the end as a huge stand out number which would flag that something
peculiar was going on that should be investigated. Inserting "0" would
hide the error rather than flagging it.

The only thing worse than a spreadsheet which returns incorrect results
might be one that also hides the fact that it's lied to you.

IMHO....

Bill

 

[Guest]

I have to quibble. Strictly speaking, division by zero is meaningless - you
can't divide anything into zero parts. You could, however, speak of the
limit of (1/n) as n -> 0 as tending toward inifinity.

 

[Jay]

I have a function that divides two numbers, sometimes those numbers

are 0 so the result is # DIV/0!. My second function is based on the
result of the first function. Since there isnt always data available
for the first function I get the # DIV/0! error and my final
calculation wont calculate. I need the final calculation to disregard
any 0 cells or # DIV/0! errors and still calculate the data that is
available.

Here's one way.

Suppose the first function is in A1.

Then for the second function, you could put something like:
=IF(ISERROR(A1),"N/A", <your second function here> )