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!

64 comments

Your email address will not be published. Required fields are marked *

  • Here’s what I came up with today:
    =IF(A1=0,”New”,IF(A1<0,-(B1-A1)/A1,(B1-A1)/A1))
    where…
    A1 = Previous value
    B1 = New value and
    "New" indicates new activity that wasn't present during the previous period.

    • I thought of that as well but it sees like it give the aame proble as with negatives. I mean aas the example above:

      % Change
      Original -10 50

      Adding 10 10
      New Summ 0 60 #DIV/0!

      Adding 20 20
      New Summ 10 70 600%

      Adding 30 30
      New Summ 20 80 300%

      Adding 40 40
      New Summ 30 90 200%

      Adding 50 50
      New Summ 40 100 150%

      Adding 60 60
      New Summ 50 110 120%

  • Hello all…

    This is one of those things that just drove me crazy because conceptually, this is very simple to understand, but it’s just a mess working it out logically while still taking into consideration what the various results actually mean. I read a lot of your responses here and incorporated a number of your considerations in my solution, including:

    – What to do when the prior period is zero and current period is not zero
    – What to do when the prior period is not zero but the current period is zero
    – How to acknowledge when a current or prior period has had NO activity compared to the other, in which case there is no actual % gain or loss, but the dollars are positive or negative
    – How to deal with #DIV/0! and #N/A issues while still communicating what happened with the dollars (I chose pretty bland descriptions for the weird results, as you’ll see. Replace the text with whatever you think is good).

    Not included in this formula, but still worth noting – I use conditional formatting color scales to visually show the percentage ranges among the broader data, like on cash flows or P&L’s. I think it helps managers put the results in perspective.

    Here you go, this has been working for me for awhile, and it’s a mess, but just throw it in Excel and test it out.

    =IF(ISERROR(IF(AND(ISBLANK(B68),ISNUMBER(C68)),”Prior Period Inactive”,IF(AND(ISNUMBER(B68),ISBLANK(C68)),”Current Period Inactive”,IF(AND(ISBLANK(B68),C680),”Positive”,IF(ISBLANK(C68),””,IF(ISBLANK(B68),””,(IF(ISBLANK(OR(B68,C68)),””,(IF(B68<0,-1,1)*((C68/B68)-1))))))))))),"Prior Period was $0",IF(AND(ISBLANK(B68),ISNUMBER(C68)),"Prior Period Inactive",IF(AND(ISNUMBER(B68),ISBLANK(C68)),"Current Period Inactive",IF(AND(ISBLANK(B68),C680),”Positive”,IF(ISBLANK(C68),””,IF(ISBLANK(B68),””,(IF(ISBLANK(OR(B68,C68)),””,(IF(B68<0,-1,1)*((C68/B68)-1)))))))))))

    Hope this works for you all!

    • The @abs(new/old-1) works well, in some situations yes. E.g. when you have the below:

      New Old Difference
      50 -50 200%

      But as soon as your difference is going from a loss to profit, you´ll get misleading values as ABS formula doesn´t consider the negative value at all:

      New Old Difference
      -50 50 200%

      Same thing if you have zero as new value and either a positive/negative number as old value, ABS doesn´t reflect the change correctly, but gives you a 100% positive difference in both cases:

      New Old Difference
      0 50 100%
      0 -50 100%

  • Hi,

    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?

  • 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
    NEGATIVE = LOWER PRICE – WE ARE LOWER THAN ORIGINAL PRICE
    POSITIVE = HIGHER PRICE – WE ARE HIGHER THAN ORIGINAL PRICE

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

  • 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
    SET A:
    10 20 10
    20 10 -10
    10 -20 -30
    20 -10 -30

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

    =IF(OR(AND(A1>0,A2>0,A3>0),AND(A1>0,A2>0,A30,A2<0,A3<0)),A3/A1,-(A3/A1))

    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.

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

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

    IF(ABS((new-old)/(old))*IF(new<old,-1,1))

    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.

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

  • Jomili,

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

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

    =IF(AND(R7>0,S7>0,T7>0),S7/R7,IF(AND(R7>0,S7>0,T70,S7<0),-(-S7/R7+1),IF(AND(R7<0,S7<0,T7<0),-(S7/R7),IF(AND(R7<0,S70),(S7/R7),IF(AND(R70),(-S7/R7+1)))))))

  • 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

  • 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?

  • Hi

    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

  • 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?

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

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

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

      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?
      =IF(OLD*NEW<0,IF(OLD<0,1-(NEW-OLD)/OLD,-(1-(NEW-OLD))/OLD),IF(OLD+NEW<0,1-(NEW-OLD)/OLD,-(1-(NEW-OLD))/OLD))

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

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

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

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

      • 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

      • 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…

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

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

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

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

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

      • 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…

  • 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)))))

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

  • 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?

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

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

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

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

  • 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)

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

  • 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

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

  • 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

    • 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

      • 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

        • 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))))

  • 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?

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

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

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

          • 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)))

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly