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

--
Andy.


"Helpwanted" <[email protected]> wrote in message
news:[email protected]...
>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(A2>0,A3/A2,0)
>

Or perhaps a more appropriate approximation fudge would be:

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


Or perhaps a better approximation fudge would be:

=IF(A2>0,A3/A2,If(A3>0,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)" <[email protected]>
wrote in message news:%[email protected]...
> 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(A2>0,A3/A2,0)
>>
>
>
> Or perhaps a better approximation fudge would be:
>
> =IF(A2>0,A3/A2,If(A3>0,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 news:uC5%[email protected]...
> 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 news:uC5%[email protected]...
> > 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> )

I read all I can in these forums about the # DIV/01 error, but I can't seem to figure out how to make it apply to what I am trying to do.

This sheet I am doing is an on going project and there will be a number to divide against eventually, but for printing purposes, I want to hide the error. I am still trying to learn this program and all of this gets puzzling, Here is the formula I am working with =(G8-G9)/SUM(G8)

Thanks for helping.

It didn't work for me, maybe if I explain my format, it will help.

I am using "SUM G8" because I have a formula in G10 that requires an answer =(G8*0.036).

Currently in G8 there is nothing, but there will be at the end of the month, so when I punch G8 numbers and G9 numbers, G10 will be the answer. Where # DIV/0! is showing up is in G11, where the formula =(G8-G9)/SUM(G8) is and will give me my total %. out of a goal.

My formula for G11 is correct, because when I have the numbers for the month, the correct percentage is there.

It's possible I need to nest my formulas, but I am quite new at all of this and learning as I create this sheet, so nesting is another thing I need to learn.

Thanks for helping.

A simple solution to hide a errors from printing is Conditional Formatting.
If an error occurs, change the text color to White --> will not show on a white paper.

Ola Sandström