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

#### s86m

##### New Member
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!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### aRandomHelper

##### Active Member
For your first problem, maybe you can try something like this:
1.xlsm
ABCDE
101-01-2108-01-2115-01-21% Change
2Item 111.251.1525%
3Item 20.50.75150%
Sheet1
Cell Formulas
RangeFormula
E2:E3E2=(OFFSET(E2,,-2)-OFFSET(E2,,-3))/OFFSET(E2,,-3)

And if you insert a new column for next week:
1.xlsm
ABCDEF
101-01-2108-01-2115-01-21% Change
2Item 111.251.15-8%
3Item 20.50.75133%
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=(OFFSET(F2,,-2)-OFFSET(F2,,-3))/OFFSET(F2,,-3)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,014
Messages
5,834,931
Members
430,326
Latest member
tomwax46

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back