Percentages help

[sbeard77]

Hello. I'm trying to find a percentage formula that works with both positive and negative numbers. I am trying to figure out prior year totals to current year totals. I've tried 3 different formulas and get 2 different results. I think the problem is that I'm dealing with 2 negative numbers.
Column K = 2016 totals (-32,183.50)
Column U = 2015 totals (-48,257.92)
This is an increase in 16,074.42, so in my mind, I should be able to get a positive percentage from my formula.

Option 1: =(K1-U1)/K1 Using this formula, I get a -49.95%.
Option 2: =(K1-U1)/ABS(K1) Using this formula, I get a 49.95%.
Option 3: =(K1/U1)-1 Using this formula, I get a 49.95%.
I would think Option 3 was correct.

HOWEVER, if I use the same formula in Option 2 for two positive numbers where 2015 was higher than 2016, I get incorrect percentages. But if I use the formula in Option 1 on these numbers, I get the correct percentage. For 2 examples:
Column K = 2016 totals (23,067.76)
Column U = 2015 totals (27,545.42)
and
Column K = 2016 totals (2,166.96)
Column U = 2015 totals (14,876.80)
This should be a -16.26% and -85.43% -- However,
Option 1 gives me -19.41% & -586.53%
Option 2 gives me -19.41% & -586.53%
Option 3 gives me -16.26% & -85.43%
Again, I would think Option 3 is correct.

Why would option 2 & 3 get the same results in my first example, but not in my 2nd & 3rd examples?

Also, with a 4th example,
Column K = 2016 totals (21,440.76)
Column U = 2015 totals (-14,768.80)
This is an increase of 36,209.56 -- However,
Option 1 gives me 168.88%
Option 2 gives me 168.88%
Option 3 gives me -245.18%
This time, Option 3 is clearly not the right option.

I'm so confused and I'm hoping there is just one percentage formula that will calculate all positive and negative numbers the same and accurately.

[CK76]

Your calculation is bit off.

Formula for calculating % Growth/Change from 2015 to 2016 would be (K1:L1 being header).
=(K2-L2)/ABS(L2)

So
Example1: =(-32,183.50 + 48257.92)/48,257.92 = 33.309%
...
Example4: =(21,440.76 + 14,768.80)/14,768.80 = 245.176%

You can check validity using following formula:
=L2+SIGN(L2)*M2*L2

Example1: =-48,257.92+(-1)*0.33309*-48,257.92 = -32,183.50
...
Example4: =-14,768.80+(-1)*2.45176*-14,768.80 = 21,440.76

See attached workbook.

[sbeard77]

Thank you so much. Amazing how one thing wrong throws it off so much. I appreciate your help.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.