Average the Top N Values

Excel Formulas › Average

All versionsAVERAGELARGE

To average only the best few values — top 3 scores, three highest months — nest LARGE inside AVERAGE. LARGE pulls the top N; AVERAGE means them.

Quick formula: to average the three largest values in B2:B8:
=AVERAGE(LARGE(B2:B8, {1,2,3}))
The array {1,2,3} makes LARGE return the 1st, 2nd and 3rd largest; AVERAGE takes their mean.

Functions used (tap for the full reference guide):

AVERAGELARGE

The example

Scores; we want the average of the top 3.

AB
1PlayerScore
2Ana95
3Ben60
4Cy88
5Dot74
6Eve82
7Avg of top 3:88.3

The formula

The average of the three best scores:

=AVERAGE(LARGE(B2:B6, {1,2,3})) // (95+88+82)/3 = 88.3

How it works

LARGE feeds AVERAGE the top values:

  1. LARGE(B2:B6, {1,2,3}) returns the three largest scores as an array: {95, 88, 82}.
  2. AVERAGE(…) takes their mean — 88.3.
  3. Change {1,2,3} to {1,2,3,4,5} for the top 5, ignoring the weakest results.
  4. Use SMALL instead of LARGE to average the bottom N.

Try it: interactive demo

Live demo

Choose how many top scores to average.

Average:

Variations

Average the bottom N

Swap LARGE for SMALL:

=AVERAGE(SMALL(B2:B6, {1,2,3}))

Drop the lowest score, average the rest

Average everything except the minimum:

=(SUM(B2:B6) - MIN(B2:B6)) / (COUNT(B2:B6) - 1)

Top N with a condition

Average the top 3 of one group (Excel 365):

=AVERAGE(LARGE(FILTER(B2:B6, A2:A6="West"), {1,2,3}))

Pitfalls & errors

Older Excel may need Ctrl+Shift+Enter for AVERAGE(LARGE(…,{1,2,3})). In Excel 365 it just works.

#NUM! when N exceeds the count. Asking for the top 6 of 5 values errors — keep N within range.

Ties are kept. Two equal top scores both count, which is usually intended.

Practice workbook

📊
Download the free Average the Top N Values practice workbook
The scores with a live average-of-top-N, the bottom-N and drop-lowest variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I average the top N values in Excel?
Nest LARGE in AVERAGE: =AVERAGE(LARGE(B2:B8, {1,2,3})) averages the three largest values. Change the array for a different count.
How do I average the highest scores and drop the lowest?
Either average the top N with AVERAGE(LARGE(...)), or drop just the minimum with =(SUM(range)-MIN(range))/(COUNT(range)-1).
How do I average the bottom N values?
Use SMALL instead of LARGE: =AVERAGE(SMALL(B2:B8, {1,2,3})) averages the three smallest values.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sum the top N values · Average by group · Nth largest value

Function references: AVERAGE · LARGE