Forum Discussion
sstaton
Jan 02, 2025Copper Contributor
Rolling 12-Month Average
I want to have a column showing a rolling 12-month average for each individual contributor. The table is laid out like this (showing a full 12 months with the previous months' columns hidden):
Name | Rolling Average | Jan 25 | Dec 24 | Nov 24 | Oct 24 |
Each month, a new month column gets added to the left and the oldest column on the right gets hidden to keep the sheet cleaner.
Any help is appreciated!
Shannon
- PeterBartholomew1Silver Contributor
My formula was
= BYROW(TAKE(contribution,,13), AVERAGE)
An obvious question might be why I have selected 13 columns when an average over 1 year is required? To answer my own question, the answer is that column C is hidden and contains blank cells. That makes inserting a column before Jan-25 a less uncertain process.
In B2:
=AVERAGE(OFFSET(B2, 0, 1, 1, 12))
or
=AVERAGE(INDEX(2:2, 3):INDEX(2:2, 14))
Fill down.