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)

To find the largest scores, the LARGE function is used, with this formula in cell G2:

=LARGE(\$B\$2:\$B\$21,\$E2)

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.

=AVERAGE(LARGE(\$B\$2:\$B\$21,ROW(1:10)))

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.

=VLOOKUP(COUNT(\$B\$2:\$B\$21),CountLU,2,TRUE)

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

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

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

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

________________

8 Responses

1. John Luff says:

2. Thanks John, I’ve fixed the link.

3. wayne says:

I have a list of dates since 1/1/11 in column A. I need to find the top 4 weeks of sales in column B between 1/1/11 and 6/30/11. Other than use helper cells to sum the daily numbers by weeks how can you use one formula similar to those in this article that provides the answer in one cell. (one cell per each of the top 4 weeks) I would also then need to know which week number each of the top 4 weeks represented. Any help or guidance would be appreciated. Love your site!

4. @Wayne, I’d add a column for weeknum to the source data.
Then create a pivot table with WeekNum in the row area, and sum of Sales in the Values area
Filter the results for top 4 and sort in descending order.
Refresh the pivot table after you enter new data (and use a dynamic source range for the pivot table).

5. Lisa says:

I have multiple results for my vlookup formula. I need excel to add them together. It is only giving me the first value found. Lisa

6. Al says:

I’m have a spreadsheet with similar properties. The problem I’m having it to look up only every 4th row to calculate the average of the top 5 scores within a year

Jump 1 – 16.04 20.05 23.00
Jump 2 – 16.06 20.01 23.02
Score – 16.05 20.03 23.01<—- Looking at these value in the row
Title – Senior Master Elite
Jump 1 – 16.06 20.09 21.00
Jump 2 – 16.08 20.01 22.00
Score – 16.07 20.05 21.06<—- Looking at these value in the rows
Title – Senior Master

7. Rob says:

Thanks for all the advice i used it to do all the calculation,ranking,and alphabetical order details of a tenpin bowling league of 42 players playing 20 weeks so that i will get the highest score at top if tying then if players tying the their top score of the weeks played to give top spot if still tying it goes to alphabetical order for top spot. None of this would be possible without your skills. Thanks.

8. Jeff says:

can you please provide this formula to work with rows instead of columns

=AVERAGE(SMALL(\$B\$2:\$B\$21,
ROW(INDIRECT(“R1:R” &
VLOOKUP(COUNT(\$B\$2:\$B\$21),CountLU,2,TRUE),FALSE))))

i have tried to change the “row” to “column” and have tried both numbers and letters but I cannot get the correct average using your data.

data is entered across instead of down and after 20 entries only the 10 smallest of the last 20 are averaged.