ExcelBanter - #DIV/0! Error

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - #DIV/0! Error (https://www.excelbanter.com/excel-programming/426722-div-0-error.html)

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?
--
Thank you for your help
MO
Albany, NY

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.
--
Thank you for your help
MO
Albany, NY

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

Check for both values before dividing..

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

If this post helps click Yes
---------------
Jacob Skaria

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

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

"MO" wrote in message
...
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.
--
Thank you for your help
MO
Albany, NY

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

Jacob. Thank you for your assistance. Unfortunatley, I get the same error
message.
--
Thank you for your help
MO
Albany, NY

"Jacob Skaria" wrote:

Check for both values before dividing..

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

If this post helps click Yes
---------------
Jacob Skaria

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

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.
--
Thank you for your help
MO
Albany, NY

"Roger" wrote:

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

"MO" wrote in message
...
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.
--
Thank you for your help
MO
Albany, NY

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

On Fri, 10 Apr 2009 06:48:01 -0700, MO wrote:

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?

=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

On Fri, 10 Apr 2009 07:05:01 -0700, Jacob Skaria
wrote:

Check for both values before dividing..

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

Why in the world do you need to check B56?

--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.

--
Thank you for your help
MO
Albany, NY

"Ron Rosenfeld" wrote:

On Fri, 10 Apr 2009 06:48:01 -0700, MO wrote:

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?

=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

=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.

--
Rick (MVP - Excel)

"MO" wrote in message
...
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.

--
Thank you for your help
MO
Albany, NY

"Ron Rosenfeld" wrote:

On Fri, 10 Apr 2009 06:48:01 -0700, MO
wrote:

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?

=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

Mucho thanks Rick and Ron, it's working as expected. I did choose to leave
the cell blank with the formula you provided :)
--
Thank you for your help
MO
Albany, NY

"Rick Rothstein" wrote:

=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.

--
Rick (MVP - Excel)

"MO" wrote in message
...
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.

--
Thank you for your help
MO
Albany, NY

"Ron Rosenfeld" wrote:

On Fri, 10 Apr 2009 06:48:01 -0700, MO
wrote:

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?

=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

On Fri, 10 Apr 2009 09:41:02 -0700, MO wrote:

Mucho thanks Rick and Ron, it's working as expected. I did choose to leave
the cell blank with the formula you provided :)
--
Thank you for your help
MO
Albany, NY

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