Spreadsheet Practice

How to average the top 3 values in Excel

Excel formula

=AVERAGE(LARGE(B2:B9,{1,2,3}))

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

Result on the example
$62,633 in E1
Works in
Every current version
Google Sheets
Different formula, see below

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

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.

=AVERAGE(LARGE(B2:B9,{1,2,3}))

Result62633.33333

  1. B2:B98 rows × 1 col: $48,200, $53,900, $61,200, $47,650, …

    A range: 8 cells in one column.

  2. {1,2,3}{1 row × 3 cols} 1, 2, 3

    An array of values.

  3. LARGE(B2:B9,{1,2,3}){1 row × 3 cols} 66900, 61200, 59800

    LARGE: k-th largest value.

    • arrayB2:B98 rows × 1 col: $48,200, $53,900, $61,200, $47,650, …
    • k{1,2,3}{1 row × 3 cols} 1, 2, 3
  4. AVERAGE(LARGE(B2:B9,{1,2,3}))62633.33333

    AVERAGE: Arithmetic mean of the numbers.

    • number1LARGE(B2:B9,{1,2,3}){1 row × 3 cols} 66900, 61200, 59800

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(AVERAGE(LARGE(B2:B9,{1,2,3}))). 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.

FormulaResultWhen to use it
=SUMPRODUCT(LARGE(B2:B9,{1,2,3}))/3$62,633SUMPRODUCT 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.

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

In E1, average the three best months of sales in B2:B9.

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})).

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.