MrExcel Publishing
Your One Stop for Excel Tips & Solutions

division by zero

Posted by Andonny on October 19, 2001 3:43 AM

I have this formula '=IF(ABS(A1-B1)/B1*100>10,"More Than 10%","")in column C. It works fine with the exception when I have a 0 in column B. Then I get #DIV/0!.
I would like it to still display "More Than 10%" in this case. How can I change the formula to achieve that.

Thanks a million

Posted by Dan on October 19, 2001 4:01 AM

Try: =IF(B1=0,"More Than 10%",IF(ABS(A1-B1)/B1*100>10,"More Than 10%",""))

Posted by IML on October 19, 2001 6:10 AM

OR use or

such as
=IF(OR(B1=0,ABS(A1-B1)/100>0.1),"More than 10%","")

good luck

Posted by Aladin Akyurek on October 19, 2001 6:43 AM

If you don't want to treat B1=0 and B1="" as the same thing, use:

=IF(B1,IF(ABS(A1-B1)/B1*100>10,"More Than 10%",""),IF(ISNUMBER(B1),"More Than 10%","")