Percentage from last two filled columns

[RSnap3232]

hello All,

I have a spreadsheet which gets updated with weekly (columns) data for certain subjects (rows), I need to know the percentage for the last two weeks/ columns that have been filled in (as not all subjects will have the same columns filled up), is there a way round this?

example;

 

[lrobbo314]

How about this?

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J
2	subject	week 1	week 2	week 3	week 4	week 5	week 6	week 7		% Difference
3	Movemnets	300	312	289	301	333	312	310		-0.64%
4	distance	2102	2198	2100	2223	2123	2100	2289		9.00%
5	accidents	6	6	5	2	3				50.00%
6	issues	2	2	4	4	2				-50.00%
Sheet7

Cell Formulas
Range	Formula
J3:J6	J3	=(INDEX(B3:I3,COUNT(B3:I3))-INDEX(B3:I3,COUNT(B3:I3)-1))/INDEX(B3:I3,COUNT(B3:I3)-1)

 

[RSnap3232]

That is brilliant thank you.

 

[RSnap3232]

How about this?

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J
2	subject	week 1	week 2	week 3	week 4	week 5	week 6	week 7		% Difference
3	Movemnets	300	312	289	301	333	312	310		-0.64%
4	distance	2102	2198	2100	2223	2123	2100	2289		9.00%
5	accidents	6	6	5	2	3				50.00%
6	issues	2	2	4	4	2				-50.00%
Sheet7

Cell Formulas
Range	Formula
J3:J6	J3	=(INDEX(B3:I3,COUNT(B3:I3))-INDEX(B3:I3,COUNT(B3:I3)-1))/INDEX(B3:I3,COUNT(B3:I3)-1)

sorry it looks like when the first number is a zero or both numbers in the columns are a zero is spits out #DIV/0! any ideas to why?

 

[lrobbo314]

Because the formula for percentage difference is (NewValue-OldValue)/OldValue, and you can't divide by zero. So if the old value is zero you will get the #DIV/0 error. You can wrap the formula with the IFERROR function to give it a default value if there is an error.

 

[RSnap3232]

I had a feeling that be why, thank you again.