Getting #DIV/0!, how to get 0%?

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!