How to Average the Last N Entries in Excel

Average the most recent 5 entries in a list that keeps growing — the formula updates automatically when new scores are added.

You will learn to

• Return the last entry in a growing list
• Average the last 5 entries with an INDEX:INDEX range
• Compare recent form with the full-season average

How it works

A fixed range like AVERAGE(B6:B10) breaks as soon as you add a new row. Instead, let COUNT find where the data ends. INDEX(B2:B15,COUNT(B2:B15)) is the last entry, and joining two INDEX calls with a colon builds a range that always covers the last five: =AVERAGE(INDEX(B2:B15,COUNT(B2:B15)-4):INDEX(B2:B15,COUNT(B2:B15))).

This works as long as the entries have no gaps. In Microsoft 365 you can also write =AVERAGE(TAKE(FILTER(B2:B15,B2:B15<>""),-5)). Comparing a recent average with the overall average shows whether performance is improving.

=AVERAGE(INDEX(B2:B15,COUNT(B2:B15)-4):INDEX(B2:B15,COUNT(B2:B15)))