Exceljet

Quick, clean, and to the point

Average top 3 scores

Excel formula: Average top 3 scores
Generic formula 
=AVERAGE(LARGE(range,{1,2,3}))
Explanation 

To average the top 3 scores in a data set, you can use a formula based on the LARGE function.

In the example shown, the formula in G6 is:

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

How this formula works

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

In this case, we are asking for more than one value by passing the array constant {1,2,3} into LARGE for the second argument. This causes LARGE to return an array result that includes the highest 3 values. The AVERAGE function then returns the average of these values.

The AVERAGE function is programmed to automatically handle array results, so it is not necessary to use Ctrl+Shift+Enter (CSE) to enter the formula.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.