#DIV/0! error
Posted by Mason on December 13, 2001 10:14 AM
In cell H9 I have a formula:
=SUM(D9)/A9
A9 cell is formatted as "currency"; D9 is also formatted as currency.
If there are dollar amounts in D9 and A9, the percentage is displayed properly.
If there are zero dollars in D9, and a dollar amount in A9, 0% is displayed.
However, if there is a dollar amount in D9, are zero dollars in A9, then the #DIV/0! error is displayed.
Same thing if both D9 and A9 are zero dollars, the #DIV/0! error is displayed.
So anytime that A9 has zero dollars, I get #DIV/0! error.
I checked Help, and have tried the formula:
=SUM(A9=0,"",D9/A9)
But instead of getting a null in H9, I get the #VALUE! error.
How can I get a null value in H9? And why is D9 in parentheses in the =SUM(D9)/A9 formula?
Thanks, very much!
Posted by Aladin Akyurek on December 13, 2001 10:21 AM
Mason --
Use
either:
=D9/MAX(1,A9)
or
=IF(A9,D9/A9,"")
Aladin
======
Posted by Gary on December 13, 2001 10:25 AM
What exactly are you trying to do? If you want to divide D9 by A9 then type =D9/A9. If A9=0 then you will get a #DIV/0! as you can't divide a number by zero. It doesn't make sense mathematically.
You can test for errors the IsErr() or IsError() functions. Look at help for examples. Or maybe test A9 to see if its zero using IF() before you divide by it.
Gary
Posted by Mike on December 13, 2001 10:27 AM
Try this: IF(A9=0,"",D9/A9)
Try IF(A9=0,"",D9/A9), which states if A9=0 then fill with blank, otherwise use the value from the division.
Posted by Jacob on December 13, 2001 10:28 AM
Try this
=if(a9=0,0,d9/a9)
jacob
Posted by Mason on December 15, 2001 8:52 AM
Thanks very much for your help. I'll try these Monday AM. Much obliged!
