Is it possible to create a formula that changes which cells it reads over time?

[s86m]

Hi All,

I am currently trying to create a Spreadsheet that can track the value of items over time. I have a list of about twenty items in rows and each column represents a week. Each week I will add new data into the spreadsheet and so over time it will become more populated with data.

I want to be able to automate a process that looks at the percentage change of each item over a one week, one month, and year to date time period (in three separate formulas), however I have been trying to determine if it is possible for the formula to know which week is current and amend which cells it looks at accordingly.

For example in the table below to look at % change between rows B & C I would use the formula (C-B)/B but is there a way so that next week the formula would change to (D-C)/C and so on as time progresses. I thought about using some sort of IF statement and a TODAY for example (IF TODAY > C2 (D-C)/C, IF TODAY < C2 (C-B)/B however trying to figure out that across a 52 week period sounded messy and I hope there would be a better way.

1	A	B	C	D	E
2		01/01/21	08/01/21	15/01/21	% Change
3	Item 1	1.00	1.25	1.15	=sum(C3-B3)/B3
4	Item 2	0.5	0.75	1	=sum(C4-B4)/B4

Similarly with the one month period I was thinking that I would want the formula to look at the most recent column of data and the 4th most recent column of data, and calculate a % change based on that. In this scenario the original formula may be (E-A)/A for wk 1, then (F-B)/B for wk 2. Again is there a way to automate this?

Finally for a year to date time period I thought that there will be a constant from Wk1 but then it will update as weeks progress so it would be (B-A)/A, (C-A)/A, (D/A)/A and so on.

Hopefully this all makes sense. If anyone has any ideas that would be great.

Thanks for the help!

 

[aRandomHelper]

For your first problem, maybe you can try something like this:

1.xlsm
	A	B	C	D	E
1		01-01-21	08-01-21	15-01-21	% Change
2	Item 1	1	1.25	1.15	25%
3	Item 2	0.5	0.75	1	50%
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=(OFFSET(E2,,-2)-OFFSET(E2,,-3))/OFFSET(E2,,-3)

And if you insert a new column for next week:

1.xlsm
	A	B	C	D	E	F
1		01-01-21	08-01-21	15-01-21		% Change
2	Item 1	1	1.25	1.15		-8%
3	Item 2	0.5	0.75	1		33%
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=(OFFSET(F2,,-2)-OFFSET(F2,,-3))/OFFSET(F2,,-3)