Excel Function Friday: Average Top 10 Scores

If you have a worksheet with 20 scores listed, how can you find the average of the best 10 scores? And if there are only 11 scores, can the formula automatically adjust, to average just the top 4 scores?
Someone emailed me for help with this question, so let’s start with the simple problem – top 10 of 20 scores. Then we’ll make a flexible solution, for the tougher challenge.

Find the Highest or Lowest Scores

One approach to the problem is to create a table, and find the top 10 scores. In this example, the numbers 1-10 are typed in column E.
In cell F2, the SMALL function finds the first smallest score, in the list of scores:
=SMALL($B$2:$B$21,$E2)
TopScores01
To find the largest scores, the LARGE function is used, with this formula in cell G2:
=LARGE($B$2:$B$21,$E2)
TopScores02
Then, the AVERAGE function calculates the average of those top 10 scores.
TopScores03

One Cell Formula With ROW

Instead of creating a table, with numbers 1-10 typed in a column, we can use an array-entered formula.
In cell F6, the AVERAGE and LARGE functions are combined with the ROW function.
=AVERAGE(LARGE($B$2:$B$21,ROW(1:10)))
After typing the formula, press Ctrl+Shift+Enter to array-enter the formula.
TopScores04
If you highlight the ROW(1:10) part of the formula, and press F9, to calculate, you’ll see the list of 10 numbers.
TopScores04a
This formula produces the same result as the original table, but in a single cell.

Lookup Table of Top Scores

Ideally, each person will have 20 scores, and the top 10 will be averaged. However, if a person has fewer than 20 scores, there is a lookup table that shows the number of top scores to average.
TopScores05
For example, if there are only 11 scores available, then the top 4 will be averaged.
To find the number of top scores to average, we can use a VLOOKUP formula. This formula counts the scores in column B, and finds that number in the lookup table, which is named CountLU.
=VLOOKUP(COUNT($B$2:$B$21),CountLU,2,TRUE)
TopScores06

Create a Flexible ROW Range

Instead of using a set range for the ROW reference:
=AVERAGE(SMALL($B$2:$B$21,ROW(1:10)))
we can create a flexible range.
With the INDEX function, you can create a range that has its number of rows set by the VLOOKUP result.
=AVERAGE(SMALL($B$2:$B$21,
ROW($A$1:INDEX($A:$A,
VLOOKUP(COUNT($B$2:$B$21),CountLU,2,TRUE))
)))

TopScores07
Or, use the INDIRECT function, with R1C1 style, to set a reference to the rows.
=AVERAGE(SMALL($B$2:$B$21,
ROW(INDIRECT(“R1:R” &
VLOOKUP(COUNT($B$2:$B$21),CountLU,2,TRUE),FALSE)
)))

TopScores08
Now, the number of top scores will change automatically, based on the number of scores available.

Download the Average Top 10 Scores Workbook

To see the formulas for averaging the top 10 scores, you can download the Average Top Scores sample file. The workbook is in Excel 2007 format, and is zipped. There are no macros in the file.

Watch the Average Top 10 Scores Video

To see the steps for averaging the top 10 scores, you can watch this Excel video tutorial.

Or, watch the video on YouTube: Average Top 10 Scores in Excel
________________