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.