difference in numbers displayed as a +/- percentage

[womblew]

I am making a chart and found part of my solution in a previous post. I used
the following formula to solve part of my dilema.

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %

My problem lies here. If A1 =0, then I get #DIV/0!. If both A1 and B1 are =0
I get the same message.

I am trying to show the number of cases compared as a + or - percentage
between A1 and B1, as in the number increased by a certain percentage.

EX:
A1=5
B1=1
Answer= +80%

I also need the formula to show as a negative percentage, as in the number
decreased a certain percentage.

 

[Jacob Skaria]

Do you mean the below

Col A Col B Col C
5 1 -80%

=IF(A2,(B2-A2)/A2,"")
and format to %

If this post helps click Yes

 

[Eduardo]

Hi,
=if(and(A1=0,B1<>0),1,if(and(a1=0,b1=0),0,A1: =(B1-A1)/A1))

 

[David Biddulph]

Is that a valid formula, Eduardo? Won't Excel object to the A1 := ?
Did you perhaps mean
=IF(AND(A1=0,B1<>0),1,IF(AND(A1=0,B1=0),0,(B1-A1)/A1)) ?

I would be reluctant to give the answer 1 where A1 is zero and B1 is
non-zero. It doesn't seem logical that as A1 reduces the result
progressively increases towards infinity, but would then drop to 100%.
A text error message (saying something like "infinte percentage change"), or
a blank result, would be better. The OP, of course, has not specified what
he wants in this situation.