How to calculate growth with a positive and negative number?

Question

I am trying to calculate percentage growth in excel with a positive and negative number.

This Year's value: 2434
Last Year's value: -2

formula I'm using is:

(This_Year - Last_Year) / Last_Year

=(2434 - -2) / -2

The problem is I get a negative result. Can an approximate growth number be calculated and if so how?

Answer 1

You could try shifting the number space upward so they both become positive.

To calculate a gain between any two positive or negative numbers, you're going to have to keep one foot in the magnitude-growth world and the other foot in the volume-growth world. You can lean to one side or the other depending on how you want the result gains to appear, and there are consequences to each choice.

Strategy

1. Create a shift equation that generates a positive number relative to the old and new numbers.

2. Add the custom shift to the old and new numbers to get new_shifted and old_shifted.

3. Take the (new_shifted - old_shifted) / old_shifted) calculation to get the gain.

For example:

old -> new
-50 -> 30               //Calculate a shift like (2*(50 + 30)) = 160

shifted_old -> shifted_new
110         ->  190

= (new-old)/old
= (190-110)/110 = 72.73%

How to choose a shift function

If your shift function shifts the numbers too far upward, like for example adding 10000 to each number, you always get a tiny growth/decline. But if the shift is just big enough to get both numbers into positive territory, you'll get wild swings in the growth/decline on edge cases. You'll need to dial in the shift function so it makes sense for your particular application. There is no totally correct solution to this problem, you must take the bitter with the sweet.

Add this to your excel to see how the numbers and gains move about:

                           shift function
old new  abs_old  abs_new  2*abs(old)+abs(new)   shiftedold  shiftednew    gain
-50  30  50          30          160                110         190        72.73%
-50  40  50          40          180                130         220        69.23%
10   20  10          20          60                 70          80         14.29%
10   30  10          30          80                 90          110        22.22%
1    10  1           10          22                 23          32         39.13%
1    20  1           20          42                 43          62         44.19%
-10  10  10          10          40                 30          50         66.67%
-10  20  10          20          60                 50          80         60.00%
1    100  1          100         202                203         302        48.77%
1    1000 1          1000        2002               2003        3002       49.88%

The gain percentage is affected by the magnitude of the numbers. The numbers above are a bad example and result from a primitive shift function.

You have to ask yourself which critter has the most productive gain:

Evaluate the growth of critters A, B, C, and D:
A used to consume 0.01 units of energy and now consumes 10 units.
B used to consume 500 units and now consumes 700 units.
C used to consume -50 units (Producing units!) and now consumes 30 units.
D used to consume -0.01 units (Producing) and now consumes -30 units (producing).

In some ways arguments can be made that each critter is the biggest grower in their own way. Some people say B is best grower, others will say D is a bigger gain. You have to decide for yourself which is better.

The question becomes, can we map this intuitive feel of what we label as growth into a continuous function that tells us what humans tend to regard as "awesome growth" vs "mediocre growth".

Growth a mysterious thing

You then have to take into account that Critter B may have had a far more difficult time than critter D. Critter D may have far more prospects for it in the future than the others. It had an advantage! How do you measure the opportunity, difficulty, velocity and acceleration of growth? To be able to predict the future, you need to have an intuitive feel for what constitutes a "major home run" and a "lame advance in productivity".

The first and second derivatives of a function will give you the "velocity of growth" and "acceleration of growth". Learn about those in calculus, they are super important.

Which is growing more? A critter that is accelerating its growth minute by minute, or a critter that is decelerating its growth? What about high and low velocity and high/low rate of change? What about the notion of exhausting opportunities for growth. Cost benefit analysis and ability/inability to capitalize on opportunity. What about adversarial systems (where your success comes from another person's failure) and zero sum games?

There is exponential growth, liner growth. And unsustainable growth. Cost benefit analysis and fitting a curve to the data. The world is far queerer than we can suppose. Plotting a perfect line to the data does not tell you which data point comes next because of the black swan effect. I suggest all humans listen to this lecture on growth, the University of Colorado At Boulder gave a fantastic talk on growth, what it is, what it isn't, and how humans completely misunderstand it. http://www.youtube.com/watch?v=u5iFESMAU58

Fit a line to the temperature of heated water, once you think you've fit a curve, a black swan happens, and the water boils. This effect happens all throughout our universe, and your primitive function (new-old)/old is not going to help you.

Here is Java code that accomplishes most of the above notions in a neat package that suits my needs:

Critter growth - (a critter can be "radio waves", "beetles", "oil temprature", "stock options", anything).

public double evaluate_critter_growth_return_a_gain_percentage(
        double old_value, double new_value) throws Exception{
    
    double abs_old = Math.abs(old_value);
    double abs_new = Math.abs(new_value);

    //This is your shift function, fool around with it and see how
    //It changes.  Have a full battery of unit tests though before you fiddle.
    double biggest_absolute_value = (Math.max(abs_old, abs_new)+1)*2;
    
    if (new_value <= 0 || old_value <= 0){
        new_value = new_value + (biggest_absolute_value+1);
        old_value = old_value + (biggest_absolute_value+1);
    }
    
    if (old_value == 0 || new_value == 0){
        old_value+=1;
        new_value+=1;
    }
    
    if (old_value <= 0)
        throw new Exception("This should never happen.");
    
    if (new_value <= 0)
        throw new Exception("This should never happen.");
    
    
    return (new_value - old_value) / old_value;
    
    
}

Result

It behaves kind-of sort-of like humans have an instinctual feel for critter growth. When our bank account goes from -9000 to -3000, we say that is better growth than when the account goes from 1000 to 2000.

1->2     (1.0)  should be bigger than   1->1 (0.0)
1->2     (1.0)  should be smaller than  1->4 (3.0)
0->1     (0.2)  should be smaller than  1->3 (2.0)
-5-> -3  (0.25) should be smaller than -5->-1 (0.5)
-5->1    (0.75) should be smaller than -5->5 (1.25)
100->200 (1.0)  should be the same as  10->20 (1.0)
-10->1   (0.84) should be smaller than -20->1 (0.91)
-10->10  (1.53) should be smaller than -20->20 (1.73)
-200->200 should not be in outer space (say more than 500%):(1.97)
handle edge case 1-> -4: (-0.41)
1-> -4:  (-0.42) should be bigger than 1-> -9:(-0.45)

Answer 2

Simplest solution is the following:

=(NEW/OLD-1)*SIGN(OLD)

The SIGN() function will result in -1 if the value is negative and 1 if the value is positive. So multiplying by that will conditionally invert the result if the previous value is negative.

Answer 3

Percentage growth is not a meaningful measure when the base is less than 0 and the current figure is greater than 0:

Yr 1 Yr 2 % Change (abs val base)
-1 10 %1100
-10 10 %200

The above calc reveals the weakness in this measure- if the base year is negative and current is positive, result is N/A