Finding the Percentage % in 2 Numbers

  • Thread starter Thread starter Michael Burkett
  • Start date Start date
M

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?
 
Michael Burkett said:
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?
Click to expand...

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.
 
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.
Click to expand...
 
Back
Top