# How to average the top 3 values in Excel

Take the three largest with LARGE and average them: `=AVERAGE(LARGE(B2:B9,{1,2,3}))`.

**Excel:** `=AVERAGE(LARGE(B2:B9,{1,2,3}))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(AVERAGE(LARGE(B2:B9,{1,2,3})))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Month | Sales |  | Top 3 average |
| Jan | 48200 |  |  |
| Feb | 53900 |  |  |
| Mar | 61200 |  |  |
| Apr | 47650 |  |  |
| May | 58400 |  |  |
| Jun | 66900 |  |  |
| Jul | 52300 |  |  |
| Aug | 59800 |  |  |

Formula in E1: `=AVERAGE(LARGE(B2:B9,{1,2,3}))`

Result: $62,633

## How it works

`LARGE(B2:B9,{1,2,3})` returns the 1st, 2nd and 3rd largest values at once: June, March and August. AVERAGE then divides their total by 3. The other months never enter the calculation.

## Other ways

- `=SUMPRODUCT(LARGE(B2:B9,{1,2,3}))/3`: SUMPRODUCT handles the array in every version without Ctrl+Shift+Enter.

## Common mistakes

- If there are fewer than three values, LARGE returns #NUM!.
- Ties are separate values: if two months tie for third, LARGE returns that amount for both rank 3 and rank 4, so exactly three values are still averaged.

## Questions

**How do I average the top N with N in a cell?**

Use SEQUENCE: `=AVERAGE(LARGE(B2:B9,SEQUENCE(E2)))`.

**How do I average the bottom 3?**

Use SMALL: `=AVERAGE(SMALL(B2:B9,{1,2,3}))`.

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-top-3-values