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

User · Answer

Use this code    IFERROR  This Year Last Year -1 IF AND D2 0 E2 0  0 1     The first part of this code iferror gets rid of the N A issues when there is a negative or a 0 value  It does this by looking at the values in e2 and d2 and makes sure they are not both 0  If they are both 0 then it will place a 0   If only one of the cells are a 0 then it will place 100  or -100  depending on where the 0 value falls  The second part of this code  e2 d2 -1 is the same code as  this year - lastyear  Last year  Please click here for example picture

User · Answer

Simplest method is the one I would use     ThisYear - LastYear   ABS LastYear     However it only works in certain situations   With certain values the results will be inverted

User · Answer

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

User · Answer

Formula should be this one      thisYear IF LastYear lt 0 ABS LastYear  0   ABS LastYear -100    The IF value if  lt  0 is added to your Thisyear value to generate the real difference   If   0  the LastYear value is 0  Seems to work in different scenarios checked

User · Answer

It is true that this calculation does not make sense in a strict mathematical perspective  however if we are checking financial data it is still a useful metric  The formula could be the following   if lastyear gt 0  thisyear lastyear-1    thisyear abs lastyear  abs lastyear    let s verify the formula empirically with simple numbers   thisyear 50 lastyear 25     growth 100  makes sense  thisyear 25 lastyear 50     growth -50  makes sense  thisyear -25 lastyear 25    growth -200  makes sense  thisyear 50 lastyear -25    growth 300  makes sense  thisyear -50 lastyear -25   growth -100  makes sense  thisyear -25 lastyear -50   growth 50  makes sense  again  it might not be mathematically correct  but if you need meaningful numbers  maybe to plug them in graphs or other formulas  it s a good alternative to N A  especially when using N A could screw all subsequent calculations

User · Answer

Use this formula    100     Year 2 Year 1   The logic is that you recover 100  of the negative in year 1  hence the initial 100   plus any excess will be a ratio against year 1

User · Answer

Short one    IF D2 gt C2  ABS  D2-C2  C2   -1 ABS  D2-C2  C2      or confusing one  my first attempt     IF D2 gt C2  IF C2 gt 0   D2-C2  C2   D2-C2  ABS C2    IF OR D2 gt 0 C2 gt 0    D2-C2  C2  IF AND D2 lt 0  C2 lt 0    D2-C2  ABS C2   0      D2 is this year  C2 is last year

User · Answer

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

User · Answer

It really does not make sense to shift both into the positive  if you want a growth value that is comparable with the normal growth as result of both positive numbers  If I want to see the growth of 2 positive numbers  I don t want the shifting   It makes however sense to invert the growth for 2 negative numbers  -1 to -2 is mathematically a growth of 100   but that feels as something positive  and in fact  the result is a decline    So  I have following function  allowing to invert the growth for 2 negative numbers   setGrowth Quantity q1  Quantity q2  boolean fromPositiveBase    if  q1 getValue   equals q2 getValue         setValue 0 0F   else if  q1 getValue    lt   0   q2 getValue    lt   0     growth makes no sense     setNaN    else if  q1 getValue    lt  0  amp  amp  q2 getValue    lt  0     both negative  option to invert     setValue  q2 getValue   - q1 getValue        fromPositiveBase  -1  1    q1 getValue      else    both positive     setValue  q2 getValue   - q1 getValue      q1 getValue

User · Answer

The best way to solve this issue is using the formula to calculate a slope    y1-y2 x1-x2    define x1 as the first moment  so value will be  C4 1   define x2 as the first moment  so value will be  C5 2   In order to get the correct percentage growth we can follow this order       B4-B5   C4-C5   ABS B4   100  Perfectly Works

User · Answer

These questions are answering the question of  how should I   without considering the question  should I   A change in the value of a variable that takes positive and negative values is fairly meaning less  statistically speaking  The suggestion to  shift  might work well for some variables  e g  temperature which can be shifted to a kelvin scale or something to take care of the problem  but very poorly for others  where negativity has a precise implication for direction  For example net income or losses  Operating at a loss  negative income  has a precise meaning in this context  and moving from -50 to 30 is not in any way the same for this context as moving from 110 to 190  as a previous post suggests  These percentage changes should most likely be reported as  NA

User · Answer

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   Create a shift equation that generates a positive number relative to the old and new numbers  Add the custom shift to the old and new numbers to get new shifted and old shifted  Take the  new shifted - old shifted    old shifted  calculation to get the gain    For example   old - gt  new -50 - gt  30                 Calculate a shift like  2  50   30     160  shifted old - gt  shifted new 110         - gt   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  lt   0    old value  lt   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  lt   0          throw new Exception  This should never happen          if  new value  lt   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- gt 2      1 0   should be bigger than   1- gt 1  0 0  1- gt 2      1 0   should be smaller than  1- gt 4  3 0  0- gt 1      0 2   should be smaller than  1- gt 3  2 0  -5- gt  -3   0 25  should be smaller than -5- gt -1  0 5  -5- gt 1     0 75  should be smaller than -5- gt 5  1 25  100- gt 200  1 0   should be the same as  10- gt 20  1 0  -10- gt 1    0 84  should be smaller than -20- gt 1  0 91  -10- gt 10   1 53  should be smaller than -20- gt 20  1 73  -200- gt 200 should not be in outer space  say more than 500    1 97  handle edge case 1- gt  -4   -0 41  1- gt  -4    -0 42  should be bigger than 1- gt  -9  -0 45    If my shift function makes sense for your needs  use it   Be sure to battle test this  if you crash the space shuttle its totally NOT my fault   This method is a heuristic

User · Answer

You should be getting a negative result - you are dividing by a negative number   If last year was negative  then you had negative growth   You can avoid this anomaly by dividing by Abs Last Year

User · Answer

This Year - Last Year     ABS Last Year     This only works reliably if this year and last year are always positive numbers     For example last year -50 this year   -1  You get -100  growth when in fact the numbers have improved a great deal

User · Answer

I was fumbling for answers today  and think this would work      IF C5 0  B5 1  IF C5 lt 0   B5 ABS C5  1   IF C5 0   B5 C5 -1     C5   Last Year  B5   This Year  We have 3 IF statements in the cell     IF Last Year is 0  then This Year divided by 1  IF Last Year is less than 0  then This Year   ABSolute value of Last Year divided by 1  IF Last Year is greater than 0  then This Year divided by Last Year minus 1

User · Answer

This article offers a detailed explanation for why the  b - a  ABS a  formula makes sense  It is counter-intuitive at first  but once you play with the underlying arithmetic  it starts to make sense  As you get used to it eventually  it changes the way you look at percentages

User · Answer

Let me draw the scenario   From  -303  To  183  what is the percentage change   -303  -100   nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp 0 nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp 183  60 396  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp 303  100                                                          183 - -303     -303    100   160 396  Total Percent Change is approximately 160   Note  No matter how negative the value is  it is treated as -100

User · Answer

Just change the divider to an absolute number i e           A          B           C         D 1    25 000      50 000      75 000     200   2    25 000      50 000      25 000     200    The formula in D2 is    C2-A2  ABS A2  compare with the all positive row the result is the same  when the absolute base number is the same   Without the ABS in the formula the result will be -200    Franco

[excel] How to calculate growth with a positive and negative number?

Examples related to excel