Forum Discussion

sstaton's avatar
sstaton
Copper Contributor
Jan 02, 2025

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

  • 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.

Resources