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:
To find the largest scores, the LARGE function is used, with this formula in cell G2:
Then, the AVERAGE function calculates the average of those top 10 scores.
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.
After typing the formula, press Ctrl+Shift+Enter to array-enter the formula.
If you highlight the ROW(1:10) part of the formula, and press F9, to calculate, you’ll see the list of 10 numbers.
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.
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.
Create a Flexible ROW Range
Instead of using a set range for the ROW reference:
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.
Or, use the INDIRECT function, with R1C1 style, to set a reference to the rows.
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