# Thread: % of Change formula (XLS 03)

1. ## % 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. ## 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. ## 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. ## Re: % of Change formula (XLS 03)

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

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