How to average the last N values in Excel
Excel formula
=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))Keep the filled cells and take the last N: =AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1)) in Excel 365.
- Result on the example
- 86.00 in
E2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER drops the empty rounds so the list ends at the latest score. TAKE with a negative number keeps that many rows from the end: rounds 4 to 8. When round 9 is entered, the window moves on by itself. Google Sheets has no TAKE; CHOOSEROWS with -5 to -1 picks the same rows.
=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))Result86
B2:B1110 rows × 1 col: 88, 91, 85, 87, …A range: 10 cells in one column.
B2:B11<>""{10 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The <> operator tests whether they differ.
FILTER(B2:B11,B2:B11<>""){8 rows × 1 col} 88, 91, 85, 87, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
B2:B1110 rows × 1 col: 88, 91, 85, 87, … - condition1
B2:B11<>""{10 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
- range
E15The value in E1.
-E1-5Makes the value negative.
TAKE(FILTER(B2:B11,B2:B11<>""),-E1){5 rows × 1 col} 87, 84, 90, 83, …TAKE: First (or last, if negative) rows or columns.- array
FILTER(B2:B11,B2:B11<>""){8 rows × 1 col} 88, 91, 85, 87, … - rows
-E1-5
- array
AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))86AVERAGE: Arithmetic mean of the numbers.- number1
TAKE(FILTER(B2:B11,B2:B11<>""),-E1){5 rows × 1 col} 87, 84, 90, 83, …
- number1
In Google Sheets
Google Sheets needs a different formula: =AVERAGE(CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),SEQUENCE(E1,1,-E1))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((ROW(B2:B11)-ROW(B2)>=COUNT(B2:B11)-E1)*B2:B11)/E1 | 86.00 | Works in every version, as long as there are no gaps between scores. |
Common mistakes
=AVERAGE(TAKE(B2:B11,-5))without FILTER includes the empty rounds; AVERAGE skips them, so it averages only the scores among the last five rows.- If fewer than N scores exist, TAKE returns them all and the average covers fewer rounds than you think.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Average the last N values
E2, average the most recent E1 golf scores, ignoring the rounds not played yet.Questions
How do I average the last N values that meet a condition?
Put the condition in FILTER: =AVERAGE(TAKE(FILTER(B2:B11,C2:C11="Home"),-5)).
How do I sum the last N instead?
Swap AVERAGE for SUM.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.