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.

Are you still looking for help with the Average function? View our comprehensive round-up of Average function tutorials here.