Results 1 to 5 of 5
Thread: % of Change formula (XLS 03)

20061218, 23:17 #1
 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,(A4B4/A4)*1,IF(A4=0,(B4A4/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

20061218, 23:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: % of Change formula (XLS 03)
Your calculations don't all make sense.
 If last year was 0 and this year is nonzero, the percent change is infinite.
 If this year is 0 and last year was nonzero, 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/B21)
and format it as a percentage.

20061219, 03:33 #3
 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

20061219, 06:05 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: % of Change formula (XLS 03)
You must have entered the formula incorrectly. See the attached sample workbook.

20061219, 10:39 #5
 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