Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    % of Change formula (XLS 03)

    Is there an easier way to formulate the % of change between This Year and Last Year, in Column C?
    The formula below gives me the desired results in Column C... but I'd like something easier to use.

    =IF(A4+B4=0,"0%",IF(B4=0,(A4-B4/A4)*1,IF(A4=0,(B4-A4/B4)*-1,IF(ISERROR((A4/B4)-1),"0%",(A4/B4)-1))))
    Columns:
    A | B | C
    TY | LY | "%" of Change
    1 1 0%
    1 0 100%
    0 0 0%
    0 8 -800%
    8 0 800%
    0 0 0%

    Any help would be greatly appreciated
    Thanks,
    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: % of Change formula (XLS 03)

    Your calculations don't all make sense.
    - If last year was 0 and this year is non-zero, the percent change is infinite.
    - If this year is 0 and last year was non-zero, the percent change is -100% regardless of last year's value.
    - If this year and last year are both 0, the percent chanfe is undefined (but you could use 0% in this case).

    You could use this formula in C2:

    =IF(B2=0,IF(A2=0,0,"infinite"),A2/B2-1)

    and format it as a percentage.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: % of Change formula (XLS 03)

    Hans,
    As I auto fill the formula you provided, the TY 0 and LY 0 opportunities return the #DIV/0 error.
    This was the problem I was trying to correct using my convoluted IF and ISERROR formula.

    I'd be happy changing the 'infinite' to '1000%', if I could get rid of the error messages when I auto fill, and hit a 0 - 0 combination..
    This solution may not be pure, but it would work for what I need it for..

    Thanks again for simplifying things...
    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: % of Change formula (XLS 03)

    You must have entered the formula incorrectly. See the attached sample workbook.

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: % of Change formula (XLS 03)

    Thanks Hans, the #DIV/0 was a result of the lookup formula used to populate the zero's. When I changed the vlookup in the A & B columns, your formula worked as designed...
    Thanks again.
    Tom

Posting Permissions

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