# DIV/0! error in Excel
 

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.

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(A20,A3/A2,0)

--
Andy.


"Helpwanted" wrote in message
...
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.

Andy B wrote:
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(A20,A3/A2,0)


Or perhaps a more appropriate approximation fudge would be:

=IF(A20,A3/A2,1E300)

Bill

Andy B wrote:
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(A20,A3/A2,0)



Or perhaps a better approximation fudge would be:

=IF(A20,A3/A2,If(A30,1e300,-1e300))

Bill

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.
--
Andy.
PS I can't believe I spelt evaluation with a double 'l'!!

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Andy B wrote:
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(A20,A3/A2,0)



Or perhaps a better approximation fudge would be:

=IF(A20,A3/A2,If(A30,1e300,-1e300))

Bill

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.


<Andy B wrote in message ...
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!

Andy B wrote:
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

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.
--
Assigning guilt doesn't solve the problem


"Lewis Clark" wrote:

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.


<Andy B wrote in message ...
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!

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 )