+ Reply to Thread
Results 1 to 12 of 12

Calculating percent change when the base is a negative number

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    60

    Calculating percent change when the base is a negative number

    CY___ LY__ PCT CHG
    $504 -$154 -445%


    Here is an example where we obviously had a positive increase, but yet the Percent Change shows a negative. I am showing the formula being used to calculate the Pct Diff: =IF(ISERROR((A2-B2)/B2),"-",(A2-B2)/B2). Is there a formula to show the correct Positive Percent Change? I've been searching and searching and read many arguments about this, but no real solutions. Can you help?

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Calculating percent change when the base is a negative number

    what number would you consider to be the correct positive percent change?
    Please click the * icon below if I have helped.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Calculating percent change when the base is a negative number

    Is Percent Change how far it moved divided by where it started from?

    Start at 504 then it went to -154 so it changed 654. Then divide this by 504 and get 130.56% change.?

    Try =(A2-B2)/A2 in C2.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Calculating percent change when the base is a negative number

    You would divide it by last years total, or LY, which is -$154.
    If you simply want the number to be positive, adding the ABS function will do that.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If not, you'll need to specify what you wants displayed instead of 445% (the formula you gave calculated -4.27%)

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Calculating percent change when the base is a negative number

    I didn't know CY = Current Year and LY = Last Year.

  6. #6
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Calculating percent change when the base is a negative number

    I know. I definitely sat there for a while trying to figure it out. Ha ha....

  7. #7
    Registered User
    Join Date
    08-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    60

    Re: Calculating percent change when the base is a negative number

    Melvinrobb....Sorry...I miss typed the -145...it should have been -146. That should correct the percentage back to -445% or close to it. The formula seems to be working correctly. I was thinking the % should be in upwards are 400% and it is with your formula. Thank you.

  8. #8
    Registered User
    Join Date
    08-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    60

    Re: Calculating percent change when the base is a negative number

    LOL...sorry about that. Just ingrained in my lingo world here.

  9. #9
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Calculating percent change when the base is a negative number

    Mathematically, it does not make sense. What if your previous year were 0 and then you made $1? Is that an infinite %ch? Here is what the Wall Street Journal says:
    http://online.wsj.com/public/resourc...s/doe-help.htm
    Look at the Net Income description.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  10. #10
    Registered User
    Join Date
    08-01-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    60

    Re: Calculating percent change when the base is a negative number

    I understand that mathmatically it is not exactly the way we need to see things, but sometimes we work with others that are set in their ways and want to see the percentage change as a simple way of reviewing things. We are still discussing how we want to tackle some of these.

    We just found the formula above does not include all scenarios either. I did however find this formula:

    =IF(AND(B2<=0,A2>=0),(A2-B2)/ABS(B2),IF(AND(B2<=0,B2<=A2),(ABS(A2)-ABS(B2))/(B2),IF(AND(B2>=0,A2<=0),(A2-B2)/B2,IF(AND(B2>=0,A2>=0),(A2-B2)/B2,IF(AND(B2<=0,B2>=A2),(ABS(A2)-ABS(B2))/(B2))))))

    There is still one problem with this one though....dividing by zero.

  11. #11
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Calculating percent change when the base is a negative number

    My intent of including the WSJ link was to help you convince the people set in their ways that their ways are NOT the accepted methods for calculating this number. Many people have recognized this problem before and the 'accepted' solution by an industry leader (the WSJ) is in that link. Unfortunately, also, the people set in their ways may not agree with whatever assumptions you have to make this work.
    Personally, I would push back, but then I would also request that if they wish to pursue this odd calculation, they provide how they want to handle the different scenarios (including dividing by 0), so that your output matches their expectations.

    Pauley

  12. #12
    Registered User
    Join Date
    01-26-2016
    Location
    Riyadh/Saudi Arabia
    MS-Off Ver
    2013
    Posts
    1

    Re: Calculating percent change when the base is a negative number

    In excel Use this Where A1 is new value, B1 is old value

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1