Tom’s Tutorials For Excel: Rolling Averages

Tom’s Tutorials For Excel: Rolling Averages

Sometimes you want to know an average of the most recent instances of a recurring item. For example, some companies base their retirement pension plan on the average of an employee’s last three years of annual salary.

In this example, I show a record of my bowling scores, for which I want to know the average score of my last three games. The formula in cell E1 is
=AVERAGE(OFFSET(B3,COUNTA(B3:B12)-3,0,3,1))

In Picture #1 the average score of 214 is based on the three most recent scores of 212, 231, and 198. After another week of practice, Picture #2 shows an improved average score of 239 based on the last three scores of 237, 239, and 241.

Picture #1

Picture #2