How do I correct the #DIV/0! error?
 

I am dividing number by "0", and want the result to be 100%, or -100%. For
example: -.06/0 should equal -100%. how do I address in the formula? Thanks.
--
Sony Luvy

One way:

Assuming your divisor is in B1 and your dividend in A1:

=IF(B1=0,SIGN(A1),A1/B1)

Format the cell as a percentage.



In article ,
"sony654" wrote:

I am dividing number by "0", and want the result to be 100%, or -100%. For
example: -.06/0 should equal -100%. how do I address in the formula? Thanks.

Here is the formula if it helps in responses, thanks again...
=IF(D29="N/A","0",IF(D29="Nil","0",IF(D29="Nil","0",IF(E29<0, (E29-D29)/E29,((E29-D29)/-E29)))))


"sony654" wrote:

I am dividing number by "0", and want the result to be 100%, or -100%. For
example: -.06/0 should equal -100%. how do I address in the formula? Thanks.
--
Sony Luvy

Thanks JE, but this returned 0%, for example: (10-8)/8 = 25%,

(a2-b2)/b2 = 25%
if b2 would = 0, i want the result to be 100%
Tom

"JE McGimpsey" wrote:

One way:

Assuming your divisor is in B1 and your dividend in A1:

=IF(B1=0,SIGN(A1),A1/B1)

Format the cell as a percentage.



In article ,
"sony654" wrote:

I am dividing number by "0", and want the result to be 100%, or -100%. For
example: -.06/0 should equal -100%. how do I address in the formula? Thanks.

Did you really try it?

With the formula I gave you (adapted for using (A2-B2) rather than just
A2, which you didn't include in your original post):

=IF(B2=0,SIGN(A2),(A2-B2)/B2)

*does* return 100% when B2=0, not 0%.

The only way the formula can return 0% is if A2=B2.


In article ,
"sony654" wrote:

Thanks JE, but this returned 0%, for example: (10-8)/8 = 25%,

(a2-b2)/b2 = 25%
if b2 would = 0, i want the result to be 100%

sony654 wrote:
Here is the formula if it helps in responses, thanks again...
=IF(D29="N/A","0",IF(D29="Nil","0",IF(D29="Nil","0",IF(E29<0, (E29-D29)/E29,((E29-D29)/-E29)))))


"sony654" wrote:


I am dividing number by "0", and want the result to be 100%, or -100%. For
example: -.06/0 should equal -100%. how do I address in the formula? Thanks.
--
Sony Luvy

When D29 is e.g., 2 and E29 0, your formula returns #DIV/0!.