Calculate Percentage Change for Negative Numbers in Excel - Excel Campus
34

Calculate Percentage Change for Negative Numbers in Excel

Bottom line: Learn how to create percentage change formulas in Excel with negative numbers.

Skill level: Intermediate

Calculate Percentage Change with Negative Numbers

In my last post I shared a macro to quickly create percentage change formulas.  I also explained two formulas we can use to calculate percentage change.

Formula #1

=(new value – old value) / old value

Formula #2

=(new value / old value) – 1

Both of these formulas will produce the same result when the numbers are positive.  The one you use is just a matter of personal preference.

The goal of the percentage change formula is to allow us to make comparisons on performance between two or more items.  This could be time periods, entities, categories, businesses, etc.  This formula can also be used to calculate discount percentages.

Download the File

Download the file to follow along.

Percentage Change Formula For Negative Numbers.xlsx (19.8 KB)

What if the numbers are Negative?

After a bit of research, I have not found a good way to calculate the percentage change when either the old number or the new number are negative.  There are definitely formulas you can use, but they seem to produce incorrect or misleading results.

The Wall Street Journal has an old guide to earnings reports that says this:

Net Income percent change is the change from the same period from a year ago. Percent change is not provided if either the latest period or the year-ago period contains a net loss [negative number]. On the digest page, if a company posts a profit in the latest period against a loss in the year-ago period, the percent change is represented as a “P”. Similarly, if a company posts a loss in the latest period against a profit in the year-ago period, the percent change is represented as a “L”.  

This article might be outdated, as I have found that recent WSJ financial pages are using the ABS method (more on that below) to calculate percentage change, but the point is that percentage change on negative numbers can produce misleading results.

What about the ABS (Absolute) Method?

One common way to calculate percentage change with negative numbers it to make the denominator in the formula positive.  The ABS function is used in Excel to change the sign of the number to positive, or its absolute value.

Here is the formula that is commonly used:

=(new value – old value) / ABS(old value)

Technically this formula works to produce a percentage change number with the correct sign (positive/negative).  However, I believe it produces misleading results.

The image below contains an example of this.  The Old value is negative and the New value is positive.

Percentage Change Formula for Negative Numbers Absolute Method with Negative Denominator

  • When the value goes from -10 to 50, the amount change is +60 and percentage change is 600%.
  • When the value goes from -60 to 50, the amount change is +110 and percentage change is 183.3%

Although the calculation is technically correct because we changed the denominator (old value) to a positive number, the results don’t really make sense to me.

If you were just looking at the percentage change numbers, you would think that Coffee did better than Cakes.  When in reality Cakes made a huge gain in profit over the prior period, much larger than Coffee.

The same issue happens when the numerator (new value) is negative.  Here is another example that produces misleading results.

Percentage Change Formula for Negative Numbers Absolute Method with Negative Numerator

In this example Cakes had a larger negative value change, but smaller percentage change than Coffee.

But I’ve Always used the ABS Method!!!

I know, me too.  I’ve seen this method used throughout my career, and it looks like the Wall Street Journal is even using it.  I spotted it in some of their growth metrics on this page of Bank of America financials.

It’s hard to say that it’s completely wrong to use the ABS formula.  But, it’s good to know that the results might be misleading.  So use it with caution! 😉

Alternate Calculations for Percentage Change with Negative Numbers

Here are a few ways to test for the presence of a negative number, and provide an alternate result.

Method #1: No Result for Negatives

The first thing we can do is check if either number is negative, and then display some text to tell the reader a percentage change calculation could not be made.

The following formula does this with an IF function and MIN function.

=IF(MIN(old value, new value)<=0,"--",(new value/old value)-1)

Here’s how the formula works:

  1. The logical test of the IF function (MIN(old value, new value)<=0) finds the minimum of the two values and tests if the value is less than or equal to zero.  The result will either be TRUE or FALSE.
  2. If the result is TRUE, then a negative number (or zero) exists.  In this case we can display some text to tell the reader.  This can be anything you want.  I just used two dashes “–“.  You could also have it return an N/A error with the NA() function, or any other text that lets the reader know the percentage change could not be calculated.
  3. If the result is FALSE, then the percentage change formula is used to return the percentage change on the two positive numbers.

Percentage Change Formula Returns Text If Either Number is Negative

This formula also handles the divide by zero (#DIV/0!), so we don’t have to wrap it in the IFERROR function.

Method #2: Show Positive or Negative Change

The Wall Street Journal guide says that its earning reports display a “P” or “L” if there is a negative number and the company posted a profit or loss.

We could use this same methodology to tell our readers if the change was positive (P) or negative (N) when either value is negative.

The following formula tests for this with an additional IF function.

=IF(MIN(old value, new value)<=0,IF((new value - old value)>0,"P","N"),(new value/old value)-1)

Here’s how the formula works:

  1. We start with the same logical test to determine if a negative value exists using the MIN function.
  2. A second IF function is then used to determine if the change from old to new is positive or negative.
    IF((new value – old value)>0,”P”,”N”)
    This IF statement returns a “P” for a positive change and an “N” for a negative change.
  3. If both numbers are positive then the percentage change formula is used to display the result.

Percentage Change Formula Returns Different Results for Positive and Negative Change

Other Methods for Percentage Change with Negatives?

Calculating the percentage change when negative numbers are involved is not easy.  The ABS method is probably the most commonly used, but it can produce misleading results.

The ultimate goal should be to keep things positive, so we don’t have to worry about the negative… 😉

Do you have any other methods for this calculation?  Can we further simplify either of the methods above?  I’m interested to hear your thoughts.  Please leave a comment below with questions or suggestions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 34 comments
Clifford Hammond - September 19, 2017

i think this works

if(b2<0,1-d2/b2,d2/b2-1)

Reply
VaGwezere - August 10, 2017

Just solve it graphically.

Reply
Rhonda Neal - July 26, 2017

Hi Jon, can you tell me if I am calculating his right? It seem like Growth rate should be positive. I tried your formula and still came out with a negative.

FY17 FY16 +/- %
New accounts 77 56 21 38%
% of new accounts 56% 45% 11% 24%
Growth rate (YOY) 27% -13% 14% -308%

Reply
    Jon Acampora - July 31, 2017

    Hi Rhonda,

    As I mention in the article, going from negative to positive tends to lead to misleading results. There are a few alternate solutions posted in the comments here, but the formulas get a bit complex. Thanks!

    Reply
      Mark Christian Simon - August 8, 2017

      been handling y-o-y percentages and stumbled upon the same problem…I hope it makes sense to you and only works if the starting value is negative and the end value is positive…I think reversing the initial and end values then multiplying the result by -1 would show a more logical result… say your initial value is -23 and it grew to 79 over a stated period of time… by switching their position the formula would show (-23-79)/79 which would result to a -129% which you would then multiply by -1 so 129%.

      Reply
      Mark Christian Simon - August 9, 2017

      I sent a response previously, The math was wrong. that was an impulse answer…after further testing… the formula I came up with is to add absolute of the old number to the new number, this will reset figures to zero…so the old number is now zero… then add 1 to both numbers (this would mean that each time the old number will be 1 so as to establish a base for comparison) then do the normal formula…then divide by ten. sample… -1 and 10 (which has a change of 11) > becomes 1 and 12 (10 + abs of -1 +1) which results to 1,100%/10 = 110%
      sample 2 … is -6 and 5… again a change of 11 so it becomes 1(when adding absolute of old number to the new number it is reset to zero and we add 1 to both sides) and 12 (abs of -6 plus 5 plus 1) which also yields 1100% divide by 10 = 110% which shows the same level of change as with the 1st example… send me a response you need clarification I hope this helps

      Reply
      Mark Christian Simon - August 9, 2017

      sorry, forgot one thing… the divisor is not always 10… it will depend on the place value of the new number after adding the absolute of the old number so if the sum of the new number and absolute yields 3,005 the divisor will be 1000…

      Reply
Hazel McLaren - July 12, 2017

This has caused me issues in the past so thought i’d have a go at trying to solve!
I have sampled my results below

Col B Col C Col D Col E
Status Old New Percentage + / –
Row 9 ▼ 10 -5 -150%
Row 10 ▼ 4 -2 -150%
Row 11 ▼ 1 0 -100%
Row 12 ▼ 10 0 -100%
Row 13 ▲ -1 1 200%
Row 14 ▲ 0 1 100%
Row 15 ▼ 2 1 -50%
Row 16 ▼ 10 1 -90%
Row 17 ▲ -2 2 200%
Row 18 ▲ 0 2 200%
Row 19 ▲ 1 2 100%
Row 20 ◄► 2 2 0%
Row 21 ▼ 3 2 -33%
Row 22 ▼ 4 2 -50%
Row 23 ▼ 10 2 -80%
Row 24 ▲ -3 3 200%
Row 25 ▲ 1 3 200%
Row 26 ▲ 2 3 50%
Row 27 ▲ -4 4 200%
Row 28 ▲ 2 4 100%
Row 29 ▲ -5 10 300%
Row 30 ▲ 0 10 1000%
Row 31 ▲ 1 10 900%
Row 32 ◄► 10 10 0%
Row 33 ▼ 20 10 -50%
Row 34 ▼ 50 10 -80%
Row 35 ▲ 5 20 300%
Row 36 ▲ 5 20 300%
Row 37 ▲ 10 20 100%
Row 38 ▲ 10 50 400%

Formula used in COLUMN E

=IF(C9=0,(D9*100)/100,IF(C9>0,(D9/C9)-1,IF(C9<0,IF(C9<0,IF(C9<0,IF(C9<>C9,ABS(D9-C9),ABS(C9-D9)))/((IF(D9>C9,ABS(D9-C9),ABS(C9-D9)))-D9),),)))))

Please let me know if you think i have cracked it!

Reply
    Jon Acampora - July 14, 2017

    Hi Hazel,

    Thanks for sharing your solution! I haven’t had a chance to fully evaluate the formula yet, but based on your list of results it looks like it works.

    When I look at the formula it seems like it could be simplified a bit, but maybe I need to study the logic further. Maybe you can explain it further.

    Thanks again! 🙂

    Reply
    Amy - August 25, 2017

    Hi Hazel,

    I think this formula works, however it is way too complicated. Try this one:

    =IFERROR((O10-N10)/ABS(N10),(O10*100)/100)

    I tested it against yours with positive numbers, negative numbers, and zeros in both columns and it produces the same result in each instance.

    Cheers.

    Reply
      Vince - September 10, 2017

      Let’s not lose hope. This is still wrong.

      try this inputs.

      from -9 to 4.5 has +150% growth
      from 1.8 to 4.5 has +150% growth

      both will yield +150%.

      notice that both are coming from different value. However, with same growth, it is misleading…

      Reply
Vasanth - May 19, 2017

Percentage formula for the negative to positive or positive to negative scenarios:

Old value is F1 and the New value is F2:

=IF(AND(F2>=0,F1<=0),(F2-F1)/ABS(F1),IF(AND(F2=0),(F2-F1)/ABS(F1),IF(AND(F2<F1,F2<=0,F1F1,F2<=0,F1<=0),LN(ABS(F1)/ABS(F2)),LN(F2/F1)))))

Reply
lenz - April 16, 2017

If it’s still relevant.. The percentage is not relative to the value/weight of the change but only to itself. And this is the same with positive number.

ex: 1 to 3 will result in 200% change; 10 to 17 will result in 70% change. Although 17-10 > 3-1. Same thing goes with -5 to 10 (300%) VS -1 to 10 (1100%). Therefore your pct number is not always representative of weight/chg value.

To solve this, I believe you’d have to enter a new parameter. If the SUM of the OLD qty is positive like in your example, you could use this and the change column to compare weight towards total growth.

ex: Sum up the OLD # of items in your last table, which gives +30. Divide the change column by this number to have the % of weight toward growth. In this example your -5 to 10 would be greater than -1 to 10.

Another way would be to Nest IF() functions and setup the different formulas for negatives, positives, zeroes, etc.

Reply
CK - April 10, 2017

Hi,

How do you explain the change of the following example:
(-60)/60 = -100%
(-60-10)/(60+40) = (-70)/100 = -70%

As the numerator decreases, and denominator increases, why is the percentage change increases?

Reply
Ed Collins - February 9, 2017

Ignore my last comment. Upon re-reading it, I see you are mentioning this formula in your article.

Reply
Ed Collins - February 9, 2017

What is wrong with this formula:

=(A1-B1)/ABS(B1)

where a1 is the current value and b1 is the prior value.

It works for all six possible scenarios:

both numbers are positive and the current value is greater than prior value
both numbers are positive and the current value is less than the prior value
both numbers are negative and the current value is greater than prior value
both numbers are negative and the current value is less than the prior value
current value is positive and the prior value is negative
current value is negative and the prior value is positive

To wit:

current vs prior

10 vs 5 = 100.0%
5 vs 10 = -50.0%
10 vs -5 = 300.0%
-10 vs 5 = -300.0%
-5 vs -10 = 50.0%
-10 vs -5 = -100.0%

Reply
    Jon Acampora - February 15, 2017

    Hi Ed,
    The negative to positive or positive to negative scenarios are where the formula produces misleading results.
    Going from -5 to 10 = 300%, this is a change of 15.
    Going from -1 to 10 = 1100%, this is a change of 11.

    If you were to look at the percentages without the numbers, an 1100% increase would look better than a 300% increase. However, the 300% increase was actually a bigger change in terms of whole numbers. I’m sure we could argue that the math is correct, but if we are only looking at percentages then we might miss something significant.

    I hope that helps.

    Reply
Humza - January 27, 2017

I believe I have created the ultimate % change formula which works with -ves and 0s:

Where Y17957 is the old value and Z17957 is the new value.

=IFERROR(IF(AND(Y17597=0,Z17597>0),1,IF(AND(Y17597=0,Z17597=0),0,(Z17597/Y17597-1)*SIGN(Y17597))),-1)

Good luck!

Reply
    Pia - March 2, 2017

    Hey, looks like this one works! Need to test a bit more but looks like this is it. Well done! Thank you!

    Reply
    Pia - March 2, 2017

    Tested this formula with the series used in the example for ABS formula above and both SIGN and ABS gave exactly the same results.

    Looks like we need to continue with the search for the right one! 🙂

    Reply
John - August 4, 2016

Using your example where

Product Old New Change
Cell B2 C2 D2
Coffee -10 50 60

this formula applied to all rows returns results that seem to provide percentages that represent the correct directional change.

=IFERROR(IF(MIN(B2,C2)<=0,D2/ABS(B2)+C2/ABS(D2),(C2/B2)-1),0)

Reply
    Jon Acampora - August 8, 2016

    Thanks for the suggestion John! I see an issue when the numberator (new number) is negative. I tried it on the Negative Numerator tab in the example file and still see misleading results.

    Reply
Mark - August 4, 2016

Hey Jon –

I’ve usually used the following:

Assume A1 is the new value & B1 is the old value. =(A1-B1)/B1*SIGN(B1)

I wonder if I’m missing something by using this method. Can you spot any limitation?

Best,
Mark

Reply
    Jon Acampora - August 4, 2016

    Hi Mark,
    I believe that would be the same result as the ABS method. The SIGN function is just going to return a negative 1 if the sign of the denominator (B1) is negative. A negative multiplied by a negative is a positive. So this is the same result as using ABS to return the absolute value of the denominator.

    Therefore, the limitations would be the same as a I explained with the ABS method. It’s amazing how many different ways there are to accomplish the same task in Excel. I guess that is what makes it fun. 🙂

    Thanks for posting this Mark!

    Reply
Anmol Singh - August 2, 2016

I am facing similar problem in my work. I have to calculate percent change between last year and this year numbers for which your solution is good but goes tangent. I want a single formula that sees everything and calculates correctly. I created a formula of my own but is lengthy :
=IF(AND(B4>0,C4>0),C4/B4-1,IF(AND(B4>0,C4<0),-(-C4/B4+1),IF(AND(B40),-(C4/B4-1),-(C4/B4-1))))
B4 and C4 are cells containing last yr and this yr numbers – there can be 4 possibilities so i listed all of them in the formula which has made it lengthy. It maybe very helpful if you can simplify this formula

Thanks & Rgds
Anmol

Reply
    Jon Acampora - August 4, 2016

    Hi Armol,
    I believe the last AND function is missing some text. AND(B40) doesn’t look right. Should it be B4<0? Do you have another condition there for the AND function?
    Jon

    Reply
      Anmol - August 4, 2016

      Hi Jon,

      You were right. It seems i had wrongly copied and pasted. So doing it again – the formula is below:

      =IF(AND(B4>0,C4>0),C4/B4-1,IF(AND(B4>0,C4<0),-(-C4/B4+1),IF(AND(B40),-(C4/B4-1),-(C4/B4-1))))

      This formula can be dragged down or right and is working for me. But, takes lot of space and slows speed. So a more efficient formula is required

      Anmol

      Reply
        bluefred - March 16, 2017

        hi Jon/Anmol,

        Anmol’s fornula is the right one, I guess, am I right?
        just editing it as he wrongly copy/paste it

        =IF(AND(B1>0,C1>0),C1/B1-1,IF(AND(B1>0,C1<0),-(-C1/B1+1),IF(B1,-(C1/B1-1),-(C1/B1-1))))

        Reply
jomili - August 2, 2016

I haven’t worked out the formula yet but it seems to me in the “Cakes” example above the value of the change is 110, so the percent change between -60 and 50 would be the equivalent % change between 0 and 110. In this case the ABS value of the negative number could be added to both sides (so we’d have 160+60 = 0, 50+60=110), so the calc would be done between 0 and 110. Am I on the right track?

Reply
    Jon Acampora - August 2, 2016

    Hi Jomili,
    I understand where you are going with that. The issue I see is that we can’t really calculate the percentage change between 0 and 110. You would then get the divide by zero error.

    If you were to change the 0 to 1, then you would get a percentage change of 10,900%. This produces misleading results as well.

    Thanks for leaving a comment though. It’s tough to find a solution for this calculation.

    Reply
      jomili - August 2, 2016

      I think I’m almost there. I got over the divide by zero by adding 2 to the numerator and using 2 for the denominator. I started with our base formula and then came up with the three alternatives:

      =IF(MIN(A1, B1)>0,(B1/A1)-1,”–“) Base formula

      =IF(A1<=0,(B1+ABS(A1)+2/2)-1,"–") If A1 is negative
      =IF(B10,(B1/A1)-1,IF(A1<=0,(B1+ABS(A1)+2/2)-1,IF(B1<=0,(ABS(B1)+A1+2/2)-1,ABS(ABS(B1)/ABS(A1)-1))))

      It works, except I need to rearrange it so the check for Both is at the front instead of the back of the formula. Please fool with it and see if it's the way to go. I think it is.

      Reply
        jomili - August 2, 2016

        I think some of my formulas got messed up. Let’s try again:
        If A1 is negative:
        ‘=IF(A1<'=0,(B1+ABS(A1)+2/2)-1,"–")
        IF B1 is negative:
        '=IF(B1<'=0,(ABS(B1)+A1+2/2)-1,"–")
        If both are negative:
        '=ABS(ABS(B1)/ABS(A1)-1)

        I think if we can put all three conditions together with the base formula we'll be there.

        Reply
          jomili - August 2, 2016

          I think this is it. Old value is in A2, New Value is in B2:

          =IF(MIN(A2, B2)>0,(B2/A2)-1,
          IF(AND(A2<=0,B2<=0),ABS(ABS(B2)/ABS(A2)-1),
          IF(A2<=0,(B2+ABS(A2)+2/2)-1,
          -(ABS(B2)+A2+2/2)-1)))

          Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x