Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Average top 3 scores in Excel

This tutorial shows how to work  Average top 3 scores in Excel using the example below;

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.

Also See:   How to calculate average last N values in a table in Excel

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.

Also See:   LARGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply

Your email address will not be published. Required fields are marked *