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 59 comments
alexander balleh - December 4, 2018

Do we have a final verdict?

chris li - October 28, 2018

i think just @abs(new/old-1) would work regardless of the sign

Collins - October 24, 2018


Over and beyonf this how about when you are dealing with Cumulative Annual Growth Rates (CAGR) for a period of more than 2 years as well as both positive and negative numbers. Does anyone know how to deal with this? Excel ends up with the same #NUM error caused by finding the nth root of a negative number.

Any takers?

Dean - October 2, 2018

How can I calculate the original value with only the percentage change and the final value.
PRICE QUOTED PERCENT HIGH/LOW What is original price?
$4.746 195% = X original price
$4.014 67% = X
$2.269 379% = X
$1.710 -58% = X
$0.643 2098% = X
$2.623 -96% = X

    Tsvetana Yacheva - October 4, 2018

    Hello guys,

    I read your comments but still can’t figure out how to solve my issue. I am calculating delta% out of 2 negative profits.

    Current year: -167 086
    Previous year: -798 204
    The standard formula = (CY/PY)-1 give me -79.1%
    I can’t understand why this happens, as it’s clear that there is an increase of 631118. Why the % is negative. I see like 300% increase.

VISWANATHAN A - August 23, 2018


How to calculate CAGR , when the beginning number is negative ?


Neil U - August 1, 2018

While in math, a negative percentage change might not be worth considering, in finance it is. Consider this: Year 1 (Y1) I spend $100 more than I make (-$100), but Year 2 (Y2) I make $200 more than I spend. That’s a difference of $300 (or 300% change). It’s important to be able to meaningfully track these growth/change rates, even if the change involves negative numbers.

After trying some of the formulas above, I believe I found a simple solution that as far as I can tell always works, even when calculating change in percentages. There are 8 conditions to consider, but only two outcomes: a positive ratio or a negative ratio. Half of the conditions result in a simple Y2/Y1 formula, the other half in a -(Y2/Y1) formula.

If you have 3 columns (the 3rd one can be hidden in your spreadsheet), the first is Y1, the 2nd Y2, and the 3rd is the change between Y1 and Y2 (we’ll call these columns A1, A2, and A3 respectively). Consider then the following conditions (in $)

Y1 Y2 Change/Delta
10 20 10
20 10 -10
10 -20 -30
20 -10 -30

-10 -20 -10
-20 -10 10
-10 20 30
-20 10 30

In SET A, to calculate the percentage change you use the formula Y2/Y1
In SET B, to calculate the percentage change you use the formula -(Y2/Y1)

However, in both sets, conditions 3 and 4 are identical, so there are only 3 conditions (not 4) for each set.

Thus, the excel formula to calculate percentage change in each row (column A4) would look like this:


You can substitute the A3 part with (A2-A1), but I find it's useful to be able to actually see the difference as part of the error correction process, and breaking it out simplifies the final formula.

Kim - June 20, 2018

I don’t see how that’s a misleading result because the percentage changes are generated for different base values. we are not supposed to compare like that.

jodell - June 13, 2018

Here is the formula I came up with. Hope it helps someone. Everyone’s input here helped me.


So what is happening is first we determine the percent change and then create an absolute number. Next we multiply that by a -1 or +1 depending on whether the new number is less than the old or not.

    Desmond - November 28, 2018

    Your formula does not work. Its invalid for an If function.

Sunil - May 12, 2018

its not an excel problem. its a statistics one. please try to understand the gap from old to new and for higher gap result should be higher %age and vice versa.

Eugene - April 4, 2018


There are actually 6 conditions not just 3.
Old New Net Outcome
+ + + +
+ + – –
+ – – –
– + + +
– – + +
– – – –

Where R is Old, S is New, T is Net


Josh - January 10, 2018

Apologies if my solution has already been posted, I’m undeniably lazy by not checking everyone’s comments.

The way I’ve got around the problem is (bearing in mind I want the %, not the % change – you can find the % change by simply -1 at the end):

=if(budget>=0, revenue/budget , if (revenue<0, budget/revenue), abs (revenue/budget)))

For lazy people: where the numbers are positive it does a standard %, where both numbers are negative it reverses the formula, and where the budget is negative but the result positive it uses the abs.

It does open me up to DIV/0 errors, so I've wrapped it in an iferror on my actual sheet, although from a practical perspective this is unlikely given the nature of my sheet.

And in the event that the result is negative but the budget positive, I get the correct output if not the mathematically correct answer – so I'm not losing sleep over that aspect.

Hope this helps

Shreya - December 4, 2017

Hi Jon,

The formula provided in this page really helped me. But I have one question regarding formula with ABS.

I think this formula doesn’t give misleading result. In above example, though the change between old and new values has increased, the old value has also increased compared to old value of previous row. So, the percent change has not increased as per increase in value.

Am I making sense?

Tracy - November 29, 2017


I had a situation, where the turnover was negative but the cost price was in the positive.

What I had to do, was try and use the GPS. But it got complicated when I did a recon with several positive GP’s and a negative GP.

Nobody can seem to help me with this.

Hope you have some suggestions

Al - November 14, 2017

Hi all
How about =IFERROR((C2-B2)/ABS(B2),IF(C2=0,”-“,IF(C2>0,1,-1))) , where C2 is NEW VALUE and B2 is OLD VALUE ?

    LLGRC - April 19, 2018


Monica - November 10, 2017

What is the old value is zero and the new is 60? Excel gives an error, other sites claim the % increase is “infinity”. How do I explain that?

Hope this helps - September 25, 2017

Simplest way to solve this uses Boolean logic and the original yoy formula(s):
if( OLD < 0 , -1 , 1) * ( (NEW – OLD)/OLD ) = YOY%
if( OLD < 0 , -1 , 1) * ( (NEW / OLD) -1 ) = YOY%

    ItHelped - October 30, 2017

    The second formula was a huge help! I removed the spaces here for anyone who wants to copy it straight into Excel:


    Thanks Mate - February 15, 2018

    Brilliant and easy solution, thanks

Clifford Hammond - September 19, 2017

i think this works


    Brenda Peluso - January 11, 2018

    I think it almost works, if you are not trying to find % increase – just %, but I think it should be:


    The results look reasonable, but it is hard to test the negatives. In my old days, I would try to do a mathematical proof to test, but I'm afraid it is over my head now!!

    For % change, what about this crazy nested if statement?

    The results look reasonable and they are positive and negative when they should be, but I'm assuming if industry just doesn't bother doing it, there isn't a simple solution???

VaGwezere - August 10, 2017

Just solve it graphically.

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%

    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!

      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%.

      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

      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…

      J - February 20, 2018

      Going from a negative to a positive does make sense. It doesn’t give misleading results at all, in fact it gives the exact results you’d expect. Your first excel sheet example when you compare coffee and cakes you say it doesn’t make sense? Well yeah it makes sense, why would anyone think cakes did better than coffee? Coffee obviously did way better than cakes. the profit increase DOES NOT matter here. When seeing who did better you look at by what FACTOR did it increase. Coffee goes from -10 to 50, so it increases by a factor of 6, which is very impressive (hence the 600%). Cakes did not do that good, they went from -60 to 50. They did not even increase by a factor of 2 but in fact was just shy of that. (hence the 183%). The amount change DOES NOT MATTER, only the factor by how much the starting amount increases. This is the same as stocks.

        Mitch - June 7, 2018

        How is a change from -10 to 50 a much more impressive than a change from -60 to 50? You said that “only the factor by how much the starting amount increases” matters. -60 is a much smaller number than -10, which means that the factor by how much the starting amount increases is much greater with the cakes than with the coffee. You are treating the negative and positive numbers as if they were the same, but they’re not.

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


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

    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! 🙂

    Amy - August 25, 2017

    Hi Hazel,

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


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


      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…

        mamta - September 4, 2018

        vince how to get the output of 150% growth in excel in case of: from -9 to 4.5 growth

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:


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.

CK - April 10, 2017


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?

Ed Collins - February 9, 2017

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

    Jon Acampora - February 15, 2017

    Sorry Ed, I just read this after responding. 🙂

Ed Collins - February 9, 2017

What is wrong with this formula:


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%

    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.

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.


Good luck!

    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!

    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! 🙂

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.


    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.

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?


    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!

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 :
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

    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?

      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:


      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


        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


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?

    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.

      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

      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.

        jomili - August 2, 2016

        I think some of my formulas got messed up. Let’s try again:
        If A1 is negative:
        IF B1 is negative:
        If both are negative:

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

          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,


Leave a Reply: