Percent change with negative numbers

[artz]

Could anyone in the Forum show how to caluculate percent change with both positive and negative numbers? The simple ((B2-A2)/A2)*100 does not compute properly with negative numbers.

Suggestions?

Thanks,

Art

 

[barry houdini]

I think it depends upon your definition of "properly"

If you always expect a positive result when B2 is greater than A2 and negative when not then perhaps

=((B2-A2)/A2)*100*SIGN(A2)

 

[artz]

Mr. Houdini,

Thank you sir, that seems to fill the bill.

Art

 

[Tackleberry51]

This should be exhaustive (addresses all percent changes):

=IF(AND(I2<=0,G2>=0),(G2-I2)/ABS(I2),IF(AND(I2<=0,I2<=G2),(ABS(G2)-ABS(I2))/(I2),IF(AND(I2>=0,G2<=0),(G2-I2)/I2,IF(AND(I2>=0,G2>=0),(G2-I2)/I2,IF(AND(I2<=0,I2>=G2),(ABS(G2)-ABS(I2))/(I2))))))

Where I2 = previous period & G2 = current period

 

[allana13]

Both formulas were helpful.

Thanks a ton.

However in the 1st formula *100 has to be omited to give the correct result.

Thanks once again all.

 

[krafty]

This should be exhaustive (addresses all percent changes):

=IF(AND(I2<=0,G2>=0),(G2-I2)/ABS(I2),IF(AND(I2<=0,I2<=G2),(ABS(G2)-ABS(I2))/(I2),IF(AND(I2>=0,G2<=0),(G2-I2)/I2,IF(AND(I2>=0,G2>=0),(G2-I2)/I2,IF(AND(I2<=0,I2>=G2),(ABS(G2)-ABS(I2))/(I2))))))

Where I2 = previous period & G2 = current period

Sorry for re-opening an old thread, but I don't think this level of detail is really necessary.

This covers everything (8 possible cases, see below), and is a bit more compact:
=(G2 - I2) / ABS(I2)

where
G2 = New value
I2 = Old value

Then use format cells to make it a percentage.
-------
8 possible cases
Let x = New, y = Old

1. x>0, y>0, x
2. x>0, y>0, x>y
3. x<0, y>0, |x|
<Y< p>4. x<0, y>0, |x|>y
5. x>0, y<0, x<|y|
6. x>0, y<0, x>|y|
7. x<0, y<0, |x|<|y|
8. x<0, y<0, |x|>|y|

 

[krafty]

Apologies, case 1 and 3 from the previous post should read:


1. x>0, y>0, x<1y
3. x<0, y>0, |x|<1y

 

[allana13]

I have tried this. Thanks works great.

 

[njligernj]

Dragging up an old thread I found via Google when I was looking for a solution to this problem.

The problem with all the formulas above is that they don't return numbers which clearly demonstrate what is happening (at least not without spending 10 minutes explaining things). For instance look at these two changes:

Scenario A: Before -16% After 30% = 292% growth (according to all of the above formulas)
Scenario B: Before -7% After 17% = 327% growth

I understand why this happens (there is a good discussion here: http://www.mathkb.com/Uwe/Forum.aspx/math/26906/Percent-change-between-negative-and-positive-numbers) but at the risk of offending math majors I don't particularly care. I would like a larger percentage returned for the first change (so that it clearly demonstrates why going from -16 to 30 is a larger growth than going from -7 to 17).

Again, I understand to some degree why I shouldn't want this -- yet I still do. I understand if this makes me a bad person.

I messed around with formulas which calculate the "spread" between the negative and the positive and got the following changes:

A: 4595%
B: 2381%

=(I2-I2*2+G2)*100

where
G2 = New value
I2 = Old value

However I'm not sure if those percentages returned are in any way meaningful or logical. Probably not. But at least A is bigger than B.

Anyone have any better ideas?

 

[vaskov17]

Percent change is directly based on the magnitude of the numbers being compared. So going from 1 to 2 and from 100 to 200 will yield the same 100% increase, even though the absolute increase is larger in the second case.

Either choose to use percent increases and accept them for what they represent or just use absolute values.