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

s86m

New Member
Joined
Feb 2, 2021
Messages
1
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.

1ABCDE
201/01/2108/01/2115/01/21% Change
3Item 11.001.251.15=sum(C3-B3)/B3
4Item 20.50.751=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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
225
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,205
Members
416,080
Latest member
blemon

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

Which adblocker are you using?

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
Top