Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2).

My problem is the percent changes from -4 to +4.

A change from +4 to -4 reports back a -200% change. However, the same formula A1/B1-1 when A1 equals -4 and B2 equals 4 is also reported as -200% but it should be a 200% increase.

The ABS function does not solve this. Has anyone crafted a fix for this?

4

4 Answers

If A1 is the old value and B1 the new one:

=(B1 - A1) / Abs(A1)

8

Use this in Excel where A1 is the new value and B1 is the old value:

=IFERROR(IF(AND(A2<0,B2<0,A2>B2),((A2-B2)/B2)*-1,IF(AND(A2<0,B2<0,A2>B2),
((A2-B2)/B2),IF(AND(A2<0,B2<0),((A2-B2)/B2)*-1,IF(AND(A2>0,B2<0),((A2-B2)/B2)*-1,IF(B2<A2,((A2-B2)
/B2),IF(B2>A2,((A2-B2)/B2),ABS(((A2-B2)/B2))))))))*100,"")

Explanation: The above formula is basically this formula ((A2-B2)/B2)*100 But it only calculates positive numbers But if you want to know the change percentage for all numbers my given formula will solve the issue and it handles all below situations:

1. A>0, B>0, A
2. A>0, B>0, A>B
3. A<0, B>0, |A|
4. A<0, B>0, |A|>B
5. A>0, B<0, A<|B|
6. A>0, B<0, A>|B|
7. A<0, B<0, |A|<|B|
8. A<0, B<0, |A|>|B|

2

Prior + “%change“ * ABS(Prior) = Current

Where “%change” is defined as (Current - Prior)/ABS(Prior)

Current and or Prior can be positive or negative, but Prior cannot be zero.

The following article provides a detailed explanation as to why the (b-a)/ABS(a) formula makes sense.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy