Finding the Percentage % in 2 Numbers

[Michael Burkett]

I am looking for a way to find the % change in 2 numbers.

Example:
A1 .47
A2 1.14
A3 =(a2/a1)-1

This works find so long as both numbers are positive. I need a way that will deal with one or both of the numbers being negative. Ideas?

 

[joeu2004]

I am looking for a way to find the % change in 2 numbers.
Example:
A1 .47
A2 1.14
A3 =(a2/a1)-1
This works find so long as both numbers are positive. I
need a way that will deal with one or both of the numbers
being negative. Ideas?

What you are doing will work when __both__ are negative, as well.

It is debatable whether to and how to calculate percentage change when one
is negative and the other is positive.

The formula I use is:

=IF(A1=0,SIGN(A2),(A2-A1)/ABS(A1))

formatted as Percentage.

Note that I also make the arbitrary choice that going from zero to something
is 100% or -100% change, depending on the direction.

Try it with some examples to see if the result seems intuitive to you.

The important thing is: not everyone will agree with this. Some people
will say that we should simply return "N/A" (not the Excel error #N/A) in
these cases. It depends on the practices that you want to or must follow.

 

[Michael Burkett]

Thank you for your help!

What you are doing will work when __both__ are negative, as well.



It is debatable whether to and how to calculate percentage change when one

is negative and the other is positive.



The formula I use is:



=IF(A1=0,SIGN(A2),(A2-A1)/ABS(A1))



formatted as Percentage.



Note that I also make the arbitrary choice that going from zero to something

is 100% or -100% change, depending on the direction.



Try it with some examples to see if the result seems intuitive to you.



The important thing is: not everyone will agree with this. Some people

will say that we should simply return "N/A" (not the Excel error #N/A) in

these cases. It depends on the practices that you want to or must follow.