Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have tried to use the following formula to calculate the % Increase(decrease-current year VS prior Year

    =IF(OR(B9>0,B23<0),B9/B23-1,(-B9-B23)/B23)

    The formula calculates the increase/decrease if the values in B9 and B23 are both positive or negative. However, if one value is positive & one is negative, the result is incorrect.

    For Eg if B9 is (455389) and B23 is 131025, the result should be -447.55%

    Your assistance will be most appreciated

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    No mattter what the sign, the results seem to me to be:

    =B9/B23-1

    which is essentially a simplification of:

    =(B9-B23)/B23

    The change divided by the original

    Using either of these formulas with your examples gives the value you indicate as corrrect...

    The problem seems to stem from the issue if the original (and we are talking about something like dollars where negative is bad) is negative that is is desired to have a negative increase since that brings the value closer to a positive. (Interpreted as a negative increase in "loss" or a positive increase in revenue)

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. I have developed this formula which works perfectly =IF(AND(B9<0,B23>0),-B9/B23+1,B9/B23-1)

    Regards

    Howard

Posting Permissions

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