Summary

To average the top 3 scores in a data set, you can use a formula based on the LARGE and AVERAGE functions. In the example shown, the formula in I6, copied down, is:

=AVERAGE(LARGE(B6:F6,{1,2,3}))

Generic formula

=AVERAGE(LARGE(range,{1,2,3}))

Explanation 

The LARGE function is designed to retrieve the top nth value from a set of numbers. So, for example LARGE(A1:A10,1) will return highest value, LARGE(A1:A10,2) will return the 2nd highest value, and so on:

LARGE(range,1) // 1st largest value
LARGE(range,2) // 2nd largest value
LARGE(range,3) // 2nd largest value

In this case, we are asking for more than one value by passing an array constant {1,2,3} into LARGE as the second argument. This causes LARGE to return an array result that includes the highest 3 values. In cell I6:

LARGE(B6:F6,{1,2,3})

returns an array like this:

{10,10,10}

This array is returned directly to the AVERAGE function:

=AVERAGE({10,10,10}) // returns 10

The AVERAGE function then returns the average of these values.

Note: the AVERAGE function can handle arrays natively, so it is not necessary to enter this formula with control + shift + enter.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.