#DIV/0! Error

  • Thread starter Thread starter MO
  • Start date Start date
M

MO

Hi.

I'm getting the above error because I have zeros in certain cells/rows that
cross foot percentages. I can't understand why the fix I used to have others
go to 0% won't work in two instances. =IF(+B56=0,0,+B56/$B$59) is an example
of the fix that works, but not all the time.

Can you help?
 
To try to clarify further, the below formula is in preceeding rows which
provides a result of 0 as it should. The forumula that is in the cell where
I am getting the error is =+B89/B98--both of those cells have 0 in them.
 
Check for both values before dividing..

=IF(B56=0,0,IF(B59=0,0,+B56/$B$59))

If this post helps click Yes
 
Hello MO,

You can't divide anything by zero, including zero/zero
Try it on a calculator
But you can divide zero by anything except zero. Result always = zero

Roger
 
Roger,
Thank you for your response. While I know this to be true, it works for one
set of "0's" but not the other.
 
Hi.

I'm getting the above error because I have zeros in certain cells/rows that
cross foot percentages. I can't understand why the fix I used to have others
go to 0% won't work in two instances. =IF(+B56=0,0,+B56/$B$59) is an example
of the fix that works, but not all the time.

Can you help?
Click to expand...


=IF($B$59=0,0,B56/$B$59)

Since your divisor is B59 (not B56), that is the cell you should be checking
for zero.

Your "+" is redundant.

If B56 is zero and B59 is not zero, the result of B56/B59 will be zero anyway,
and not an error, so there is no need to check if B56=0.

--ron
 
Ron,
I get the same error message. I have six rows total. Data gets manually
entered in these rows. The fifth row has this formula =+C26-(D26+E26). The
sixth row has this formula =IF(+B26=0,0,+B26/$B$29).

Each row (for instance row 29) sums with this formula =SUM(B26:B28), the
next row would be SUM(C26:C28), etc. The last row, which is the one I'm
encountering the error with has this formula =+B37/B38, which provides the
percentage.

The idea is if it's zero, I want to see zero percent. In all likelihood,
the zero percent could change, dependent upon the data that is entered.

I MIGHT be confusing you and I apologize. It is much easier when the
document is in front of you.
 
=IF(+B26=0,0,+B26/$B$29)

I think you missed the point of Ron's post. You are checking the numerator
(B26) to see if it is zero... but your #DIV/0! error will only be generated
when the denominator (B29) is zero. If B29 is not zero and B26 is zero, your
division will produce zero as an answer automatically... you don't have to
test for that condition. The only time your division will fail is if B29 is
zero. Change your formula to this...

=IF(B29=0,0,B26/$B$29)

and it will return zero when B29 is zero and the division will proceed
properly and as you expect for all other numbers. By the way, you might not
want to show zero when B29 is zero as that may convey the wrong information
to whoever is using the worksheet. Maybe better would be to return a message
(that is what the #DIV/0! error is doing) or perhaps an empty cell...

=IF(B29=0,"",B26/$B$29)

Also note the plus signs you put in front of your cell references above are
not necessary (this was Ron's other comment)... positive values are assumed
unless a minus sign is used to negate the expression.
 
Mucho thanks Rick and Ron, it's working as expected. I did choose to leave
the cell blank with the formula you provided :)
 
Mucho thanks Rick and Ron, it's working as expected. I did choose to leave
the cell blank with the formula you provided :)
Click to expand...

You're welcome. Glad you finally got it working. Sometimes it is difficult to
convey ideas in this medium.
--ron
 
Back
Top