+ Reply to Thread
Results 1 to 8 of 8

Formula to calculate Increase/Decrease

  1. #1
    Registered User
    Join Date
    11-11-2007
    Posts
    25

    Formula to calculate Increase/Decrease

    I have a spreadsheet where I need to calculate the increase in % one year over the other in column D for Eg if Profits are 95415 in Jan 2008 (Column B) and 80215 in Jan 2008 (Column C), then the % Increase will be (95415-80215)/80215 *100

    I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/

    Your assistance or anybody's on the forum will be greatly appreciated

    Regards

    Howard

    http://www.mrexcel.com/forum/showthr...ghlight=howardneed
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You can't meaningfully compute a percentage increase from a negative or zero number to a positive number.

  3. #3
    Registered User
    Join Date
    11-11-2007
    Posts
    25

    Formula to calculate Increase/Decrease

    Hi shg

    Thanks for the reply.


    1) If I have a loss in say Jan 2008 of say -95140 and in Jan 2007 I made a profit of 48150 then the % decrease will be -297.59%.(-95140+48150)/48150 *100

    2) If I make a profit of 60000 in Jan 2008 and a profit of 45000 in Jan 2007 then the % increase will be 33.33% (60000-45000)/45000 * 100

    3) If i make a profit of 60000 in Jan 2008 and a loss of 35000 in Jan 2007
    then the % increase will be 271.43% (60000+35000)/35000 *100

    I would like to incorporate this into one formula in column D, which will take all the scenarios above into account


    Your assistance in this regard will be most appreciated

    Howard

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Howard,

    The formula is the same in all cases: = current / prior - 1, and gives the same results in your post. But consider this:

    1. If the prior profit was zero, the formula blows up. (#DIV/0!)

    2. If the prior profit was a penny, and the current profit is a dollar, touting that to analysts as a 1000% increase might not get a warm reception.

    3: A positive change in profitability is always good. What about a negative change, e.g.:

    Jan 2007 = -35K, Jan 2008 = +60K, change = -271% -- very good
    Jan 2007 = +35K, Jan 2008 = -60K, change = -271% -- not so good

    BTW, don't multiply the result by 100 to get percent; just format the result as %
    Last edited by shg; 02-21-2008 at 03:22 PM.

  5. #5
    Registered User
    Join Date
    11-11-2007
    Posts
    25

    Formula to calculate Increase/Decrease

    Hi shg

    Thanks for the advice, much appreciated. It works well, except if the prev years profit is in a loss and the current year is in a profit. I have highlighted the incorrect percentage & have shown the amemded formula in D12:D16

    Please find sample file showing the formula suggested by yourself and my amendment using your formula and amending using the if formula.

    Regards

    Howard
    Attached Files Attached Files

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Note how the second and third rows give the same result (I reversed the order of the first two columns to show them in ascending date order):

    Please Login or Register  to view this content.
    The formula is only meaningful if given the change and the current profit one can compute the prior profit.

  7. #7
    Registered User
    Join Date
    08-01-2012
    Location
    Ashford, Kent England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Formula to calculate Increase/Decrease

    But if you want to calculate the increase / decrease of ordinary minus / plus numbers the previous solutions do not seem to work. Viz.

    (col A, col B, col C)
    number, number, % increase / decrease
    -4, 5, ?
    -4, -5, ?
    4, 5. ?
    4, -5, ?

    I think the function ABS might be relevant but can't think it through just yet.

    Would be grateful for help

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Formula to calculate Increase/Decrease

    Crockhamtown,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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