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

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 <A8AA6353-71E6-4506-B646-099FCC8F3766.DeleteThis@microsoft.com>,
> kaimarja <kaimarja.DeleteThis@discussions.microsoft.com> 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" <K@MJP@discussions.microsoft.com> wrote in message
news:257D9847-E3CC-40D2-A0C1-51714530A860@microsoft.com...
> 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 <A8AA6353-71E6-4506-B646-099FCC8F3766.TakeThisOut@microsoft.com>,
>> kaimarja <kaimarja.TakeThisOut@discussions.microsoft.com> 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.
>>