I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

Hey there! I can definitely help you with that. The reason you're getting the #DIV/0! error is because you're trying to divide by zero, which is not possible. However, you can use an IF statement to check for this error and display 0% instead. Here's how you can do it:

1. Click on the cell where you have the formula that is giving you the #DIV/0! error.
2. In the formula bar, replace your current formula with the following formula:
   
   
   Formula:

   =IF(E34=0,0%,(E34-D34)/E34) 

3. Press Enter to apply the formula.

What this formula does is it checks if E34 (the denominator) is equal to zero. If it is, it displays 0%. If it's not, it performs the calculation as before.

Check e34 prior to dividing.

if(e34 = 0, 0,(E34-D34)/E34)
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

=IF(E34=0,0,(E34-D34)/E34)

--


Regards,


Peo Sjoblom



"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

Try it like this:

=IF(E34=0,0,(E34-D34)/E34)

--
Biff
Microsoft Excel MVP


"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

thanks

"T. Valko" wrote:

Try it like this:

=IF(E34=0,0,(E34-D34)/E34)

--
Biff
Microsoft Excel MVP


"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

thanks

"Peo Sjoblom" wrote:

=IF(E34=0,0,(E34-D34)/E34)

--


Regards,


Peo Sjoblom



"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

thanks

"Jim Thomlinson" wrote:

Check e34 prior to dividing.

if(e34 = 0, 0,(E34-D34)/E34)
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))

Note that with the formulae which some other contributors have suggested,
you would get the same answer for an input of D34=99 and E34=0 as you would
get for D34 and E34 both 99. Is that what you want?
--
David Biddulph

"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

Although I don't like Excel 2007....

=IFERROR((E34-D34)/E34,0)

--
Dana DeLouis


"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

Hi David,
I have the same problem as above, but my formula is slightly different:
=(J37-C37)/ABS(C37). I compare two values and want to get the percentage of
increase or decrease between the two. Sometimes one of the values (or both)
is zero, and then it gives an error. I Tried the advice given above but I
must do something wrong as it still gives an error. Thanks for helping me.
Kaimarja

"David Biddulph" wrote:

=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))

Note that with the formulae which some other contributors have suggested,
you would get the same answer for an input of D34=99 and E34=0 as you would
get for D34 and E34 both 99. Is that what you want?
--
David Biddulph

"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

"... it still gives an error" isn't very specific. You haven't told us what
formula you are using, so we can't tell you what you've done wrong.

If instead of =(E34-D34)/E34 you wanted to start from =(J37-C37)/ABS(C37),
then instead of
=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))
you would end up with
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37)))
If you want the result as a percentage, format the cell with a percentage
format to suit your requirements.
Is that the formula you were using? If so, what input values did you have
in J37 and C37, what result did you get, and what result did you expect?
--
David Biddulph

"kaimarja" wrote in message
...
Hi David,
I have the same problem as above, but my formula is slightly different:
=(J37-C37)/ABS(C37). I compare two values and want to get the percentage
of
increase or decrease between the two. Sometimes one of the values (or
both)
is zero, and then it gives an error. I Tried the advice given above but I
must do something wrong as it still gives an error. Thanks for helping me.
Kaimarja

"David Biddulph" wrote:

=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))

Note that with the formulae which some other contributors have suggested,
you would get the same answer for an input of D34=99 and E34=0 as you
would
get for D34 and E34 both 99. Is that what you want?
--
David Biddulph

"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

Hi David, Thanks for the reply. Sorry for not being specific enough. Here are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result cells
when the formula does not refer to cells containing 0, I get a correct answer
but not in the above case, where last year's cell contains a 0. The result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains an
error".

Of course I understand I'm asking my formula to calculate a % of 0, which is
illogical, but I wondered whether there was a way around it.

Thanks for your help.
Kai

"David Biddulph" wrote:

"... it still gives an error" isn't very specific. You haven't told us what
formula you are using, so we can't tell you what you've done wrong.

If instead of =(E34-D34)/E34 you wanted to start from =(J37-C37)/ABS(C37),
then instead of
=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))
you would end up with
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37)))
If you want the result as a percentage, format the cell with a percentage
format to suit your requirements.
Is that the formula you were using? If so, what input values did you have
in J37 and C37, what result did you get, and what result did you expect?
--
David Biddulph

"kaimarja" wrote in message
...
Hi David,
I have the same problem as above, but my formula is slightly different:
=(J37-C37)/ABS(C37). I compare two values and want to get the percentage
of
increase or decrease between the two. Sometimes one of the values (or
both)
is zero, and then it gives an error. I Tried the advice given above but I
must do something wrong as it still gives an error. Thanks for helping me.
Kaimarja

"David Biddulph" wrote:

=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))

Note that with the formulae which some other contributors have suggested,
you would get the same answer for an input of D34=99 and E34=0 as you
would
get for D34 and E34 both 99. Is that what you want?
--
David Biddulph

"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

You can't "show a % of increase", since it's mathematically undefined,
but you can show a default value:

=IF(C37=0, "undefined", J37/C37 - 1)

or

=IF(C37=0, 1, J37/C37 - 1)



In article ,
kaimarja wrote:

Hi David, Thanks for the reply. Sorry for not being specific enough. Here are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result cells
when the formula does not refer to cells containing 0, I get a correct answer
but not in the above case, where last year's cell contains a 0. The result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains an
error".

Of course I understand I'm asking my formula to calculate a % of 0, which is
illogical, but I wondered whether there was a way around it.

If you copied and pasted (rather than retyped) my formula
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37))) and
Excel reported "The formula you typed contains an error", then I can only
assume that you are working with Windows Regional Options that are looking
for a semi-colon, rather than a comma, as a list separator?
If so, you ought to use
=IF(J37-C37=0;0;IF(C37=0;"infinite % difference";(J37-C37)/ABS(C37)))

If you are still getting a problem, copy and paste the formula from your
formula bar to the newsgroup.

You are right that a percentage of zero is illogical, hence the various
suggestions for reporting the situation, such as my "infinite % difference"
result.
--
David Biddulph

"kaimarja" wrote in message
...
Hi David, Thanks for the reply. Sorry for not being specific enough. Here
are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J
being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result cells
when the formula does not refer to cells containing 0, I get a correct
answer
but not in the above case, where last year's cell contains a 0. The result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains an
error".

Of course I understand I'm asking my formula to calculate a % of 0, which
is
illogical, but I wondered whether there was a way around it.

Thanks for your help.
Kai

"David Biddulph" wrote:

"... it still gives an error" isn't very specific. You haven't told us
what
formula you are using, so we can't tell you what you've done wrong.

If instead of =(E34-D34)/E34 you wanted to start from
=(J37-C37)/ABS(C37),
then instead of
=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))
you would end up with
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37)))
If you want the result as a percentage, format the cell with a percentage
format to suit your requirements.
Is that the formula you were using? If so, what input values did you
have
in J37 and C37, what result did you get, and what result did you expect?
--
David Biddulph

"kaimarja" wrote in message
...
Hi David,
I have the same problem as above, but my formula is slightly different:
=(J37-C37)/ABS(C37). I compare two values and want to get the
percentage
of
increase or decrease between the two. Sometimes one of the values (or
both)
is zero, and then it gives an error. I Tried the advice given above but
I
must do something wrong as it still gives an error. Thanks for helping
me.
Kaimarja

"David Biddulph" wrote:

=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))

Note that with the formulae which some other contributors have
suggested,
you would get the same answer for an input of D34=99 and E34=0 as you
would
get for D34 and E34 both 99. Is that what you want?
--
David Biddulph

"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format
as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

You can pass the 0 as value.


"pgarcia" wrote:

I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

This formula is all fine and dandy to get zero... now what about when you put
numbers in those boxes... it still returns zero

You haven't told us which formula, so we can't help you.
If you are referring to a formula in a previous message, you need to quote
enough of the previous message(s) to put your reply into context.
--
David Biddulph

"Jim" wrote in message
...
This formula is all fine and dandy to get zero... now what about when you
put
numbers in those boxes... it still returns zero

Reading your solution to subject topic. I have a similar situation where
AO=units sold and AP=units returned. In trying to get the return rate
percentage, I must divide AP by AO. However, if 0 units sold and 1 unit
returned, what formula would give me the high return rate that I need to show
when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2). This
works for all except those with 0 in AO or units sold column. I need to see
at least a 100% return rate when AO=0 and AP=1 or more. Can you help?

Thank you.

"JE McGimpsey" wrote:

You can't "show a % of increase", since it's mathematically undefined,
but you can show a default value:

=IF(C37=0, "undefined", J37/C37 - 1)

or

=IF(C37=0, 1, J37/C37 - 1)



In article ,
kaimarja wrote:

Hi David, Thanks for the reply. Sorry for not being specific enough. Here are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result cells
when the formula does not refer to cells containing 0, I get a correct answer
but not in the above case, where last year's cell contains a 0. The result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains an
error".

Of course I understand I'm asking my formula to calculate a % of 0, which is
illogical, but I wondered whether there was a way around it.

You want:
=IF(AO2=0,1,AP2/AO2).
Format as percent

Regards,
Fred.

"K@MJP" wrote in message
...
Reading your solution to subject topic. I have a similar situation where
AO=units sold and AP=units returned. In trying to get the return rate
percentage, I must divide AP by AO. However, if 0 units sold and 1 unit
returned, what formula would give me the high return rate that I need to
show
when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2).
This
works for all except those with 0 in AO or units sold column. I need to
see
at least a 100% return rate when AO=0 and AP=1 or more. Can you help?

Thank you.

"JE McGimpsey" wrote:

You can't "show a % of increase", since it's mathematically undefined,
but you can show a default value:

=IF(C37=0, "undefined", J37/C37 - 1)

or

=IF(C37=0, 1, J37/C37 - 1)



In article ,
kaimarja wrote:

Hi David, Thanks for the reply. Sorry for not being specific enough.
Here are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J
being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result
cells
when the formula does not refer to cells containing 0, I get a correct
answer
but not in the above case, where last year's cell contains a 0. The
result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains
an
error".

Of course I understand I'm asking my formula to calculate a % of 0,
which is
illogical, but I wondered whether there was a way around it.

Hi Fred,

Thank you for your response. However, it's the AP2/AO2 where AO=0 that is
the problem. I believe that I resolved the issue using
=IF(AP2=0,0,IF(AO2=0,1,AP2/AO2). This seemed to result in the percentage I
was looking for. Your solution was the second part of the formula I ended up
using.

My best.
K@MJP

"Fred Smith" wrote:

You want:
=IF(AO2=0,1,AP2/AO2).
Format as percent

Regards,
Fred.

"K@MJP" wrote in message
...
Reading your solution to subject topic. I have a similar situation where
AO=units sold and AP=units returned. In trying to get the return rate
percentage, I must divide AP by AO. However, if 0 units sold and 1 unit
returned, what formula would give me the high return rate that I need to
show
when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2).
This
works for all except those with 0 in AO or units sold column. I need to
see
at least a 100% return rate when AO=0 and AP=1 or more. Can you help?

Thank you.

"JE McGimpsey" wrote:

You can't "show a % of increase", since it's mathematically undefined,
but you can show a default value:

=IF(C37=0, "undefined", J37/C37 - 1)

or

=IF(C37=0, 1, J37/C37 - 1)



In article ,
kaimarja wrote:

Hi David, Thanks for the reply. Sorry for not being specific enough.
Here are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J
being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result
cells
when the formula does not refer to cells containing 0, I get a correct
answer
but not in the above case, where last year's cell contains a 0. The
result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains
an
error".

Of course I understand I'm asking my formula to calculate a % of 0,
which is
illogical, but I wondered whether there was a way around it.

Yes, your formula checked only AP2 for zero. But the error is dividing by
zero, so you need to check the divisor, AO2, for zero.

Regards,
Fred.

"K@MJP" wrote in message
...
Hi Fred,

Thank you for your response. However, it's the AP2/AO2 where AO=0 that is
the problem. I believe that I resolved the issue using
=IF(AP2=0,0,IF(AO2=0,1,AP2/AO2). This seemed to result in the percentage I
was looking for. Your solution was the second part of the formula I ended
up
using.

My best.
K@MJP

"Fred Smith" wrote:

You want:
=IF(AO2=0,1,AP2/AO2).
Format as percent

Regards,
Fred.

"K@MJP" wrote in message
...
Reading your solution to subject topic. I have a similar situation
where
AO=units sold and AP=units returned. In trying to get the return rate
percentage, I must divide AP by AO. However, if 0 units sold and 1 unit
returned, what formula would give me the high return rate that I need
to
show
when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2).
This
works for all except those with 0 in AO or units sold column. I need to
see
at least a 100% return rate when AO=0 and AP=1 or more. Can you help?

Thank you.

"JE McGimpsey" wrote:

You can't "show a % of increase", since it's mathematically undefined,
but you can show a default value:

=IF(C37=0, "undefined", J37/C37 - 1)

or

=IF(C37=0, 1, J37/C37 - 1)



In article ,
kaimarja wrote:

Hi David, Thanks for the reply. Sorry for not being specific enough.
Here are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and
J
being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result
cells
when the formula does not refer to cells containing 0, I get a
correct
answer
but not in the above case, where last year's cell contains a 0. The
result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70,
the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer,
but I
still get an error message from excel "The formula you typed
contains
an
error".

Of course I understand I'm asking my formula to calculate a % of 0,
which is
illogical, but I wondered whether there was a way around it.

ok, i have a formula that works but i want to have the cell blank when
nothing is in the other cells... =IF(ISERROR(F4/E4,0,F4/E4) which gives me
0.00 in cell G4. question is, what do i need to add to the formula to have
G4 blank until valves are added to cells F4 and E4... i.e. ,"", but i can't
get it right... any help?

Maybe this:

=IF(COUNT(E4:F4)=2,F4/E4,"")

--
Biff
Microsoft Excel MVP


"dnmusic" wrote in message
...
ok, i have a formula that works but i want to have the cell blank when
nothing is in the other cells... =IF(ISERROR(F4/E4,0,F4/E4) which gives
me
0.00 in cell G4. question is, what do i need to add to the formula to
have
G4 blank until valves are added to cells F4 and E4... i.e. ,"", but i
can't
get it right... any help?

Maybe you can check for numbers:
=if(count(e4:f4)<2,"",if(e4=0,0,f4/e4))

And I only checked for a non-zero denominator.



dnmusic wrote:

ok, i have a formula that works but i want to have the cell blank when
nothing is in the other cells... =IF(ISERROR(F4/E4,0,F4/E4) which gives me
0.00 in cell G4. question is, what do i need to add to the formula to have
G4 blank until valves are added to cells F4 and E4... i.e. ,"", but i can't
get it right... any help?

--

Dave Peterson

"Jim Thomlinson" wrote:

Check e34 prior to dividing.

if(e34 = 0, 0,(E34-D34)/E34)
--
HTH...

Jim Thomlinson


"pgarcia" wrote:

I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

I am still getting a Value after changing formula to instead of #DIV/0!
=IF(c13=0,0,(c12:c16)/c13

"Peo Sjoblom" wrote:

=IF(E34=0,0,(E34-D34)/E34)

--


Regards,


Peo Sjoblom



"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

You are using a range divided by a number and you should change that to a
single cell

=IF(C13=0,0,C12/C13)


or something


and the only way you can get a value error is if C13 itself has a DIV error
in it.


Maybe you should explain what you are trying to do instead?

--


Regards,


Peo Sjoblom


"kevin" wrote in message
...
I am still getting a Value after changing formula to instead of #DIV/0!
=IF(c13=0,0,(c12:c16)/c13

"Peo Sjoblom" wrote:

=IF(E34=0,0,(E34-D34)/E34)

--


Regards,


Peo Sjoblom



"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%

I am getting #Div/0! with the following formula.. I want it to display 0% in
the cell.

=100%-I8

Thanks!

You won't get #DIV/0! with that formula unless I8 is #DIV/0! to start with.

Correct the error in I8 would be my first step.

Or use this formula =IF(ISERROR(100%-I8),"",100%-I8)


Gord Dibben MS Excel MVP

On Fri, 19 Feb 2010 11:55:01 -0800, Elaine
wrote:

I am getting #Div/0! with the following formula.. I want it to display 0% in
the cell.

=100%-I8

Thanks!

If you're getting #Div/0! from =100%-I8, it's because I8 has the error
#Div/0!
You could test for the divisor in I8 being zero, and if so set your formula
result to zero.
--
David Biddulph


Elaine wrote:
I am getting #Div/0! with the following formula.. I want it to
display 0% in the cell.

=100%-I8

Thanks!

Correction......you wanted 0%

=IF(ISERROR(100%-I8),0,100%-I8)


Gord

On Fri, 19 Feb 2010 13:50:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

You won't get #DIV/0! with that formula unless I8 is #DIV/0! to start with.

Correct the error in I8 would be my first step.

Or use this formula =IF(ISERROR(100%-I8),"",100%-I8)


Gord Dibben MS Excel MVP

On Fri, 19 Feb 2010 11:55:01 -0800, Elaine
wrote:

I am getting #Div/0! with the following formula.. I want it to display 0% in
the cell.

=100%-I8

Thanks!