# How to average the last N values in Excel

Keep the filled cells and take the last N: `=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))` in Excel 365.

**Excel:** `=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=AVERAGE(CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),SEQUENCE(E1,1,-E1)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Round | Score |  | Last N | 5 |
| R1 | 88 |  | Average |  |
| R2 | 91 |  |  |  |
| R3 | 85 |  |  |  |
| R4 | 87 |  |  |  |
| R5 | 84 |  |  |  |
| R6 | 90 |  |  |  |
| R7 | 83 |  |  |  |
| R8 | 86 |  |  |  |
| R9 |  |  |  |  |
| R10 |  |  |  |  |

Formula in E2: `=AVERAGE(TAKE(FILTER(B2:B11,B2:B11<>""),-E1))`

Result: 86.00

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

## Other ways

- `=SUMPRODUCT((ROW(B2:B11)-ROW(B2)>=COUNT(B2:B11)-E1)*B2:B11)/E1`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/average-the-last-n-values