Go Back

How to Calculate the Average Top 3 Scores in Excel

The average of the top 3 scores in any data set can be calculated by using the AVERAGE and LARGE functions in Excel. This tutorial will walk through the process.

Figure 1.  Calculating the average top 3 scores in Excel

Formula: =AVERAGE(LARGE(C3:G3,{1,2,3}))

Setting up the Data

Below table shows the test scores of students in 5 sets.  We want to calculate the average top 3 scores per student.  

Figure 2.  Data for calculating the average top 3 scores

Average Top 3 Scores

We will combine the two Excel functions: AVERAGE and LARGE

AVERAGE Function in Excel

The AVERAGE function returns the average of a set of values.  

Syntax

=AVERAGE(number1, [number2],...)

LARGE Function in Excel

The LARGE function returns the kth largest value in a set of data.

Syntax

=LARGE(array, k)

Where array is the range of data we are evaluating to find the kth largest value

Calculate the Average Top 3 Scores

In column H, we manually enter the top 3 scores per student so we could better understand our working formula.  

In cell I3, enter the formula:

=AVERAGE(LARGE(C3:G3,{1,2,3}))

Figure 3.  Entering the formula for the average top 3 scores

C3:G3 is the range of values we want to evaluate. We enter the array constant {1,2,3} as the value of k in the LARGE function so that the LARGE function returns an array result containing the 1st, 2nd and 3rd largest values.

Figure 4.  Final result: Calculating the average top 3 scores in Excel

We can see in the example, the AVERAGE function returns the average of the three values returned by the LARGE function. The top 3 scores in row 3 are 88, 84 80 and the average is 84.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a problem regarding to Average top 3 scores
Solved by B. J. in 17 mins
I have a set of 4 scores. I need to take the average of the 3 highest scores. what formula do I use?
Solved by S. D. in 30 mins
I want to automate my golf spreadsheet. Take the last 5 scores, drop the largest and smallest score, and then average the 3 remaining scores. There may be several weeks of no score, need to look back and 5 last scores.
Solved by A. S. in 29 mins

Leave a Comment

avatar