Average top 3 scores

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.