You can use the following formula in Excel to calculate the average of the top n values in Excel:
=AVERAGE(LARGE(A2:A11,ROW(1:3)))
This particular formula calculates the average of the top 3 (i.e. largest 3) values in the range A2:A11.
Note: To calculate the average for a different number of top values, simply replace the 3 in the ROW function with a different number.
The following example shows how to use this formula in practice.
Example: Calculate Average of Top N Values in Excel
Suppose we have the following column of values in Excel:
Suppose we would like to calculate the average of the largest 3 values in the range.
We can type the following formula into cell C2 to do so:
=AVERAGE(LARGE(A2:A11,ROW(1:3)))
The following screenshot shows how to use this formula in practice:
We can see that the average of the top 3 values in the range A2:A11 is 34.
We can verify this is correct by typing the following formula into cell D2 to actually display the top 3 values in the range:
=LARGE(A2:A11,ROW(1:3))
The following screenshot shows how to use this formula in practice:
The average of these top 3 values is calculated as:
Average = (44 + 30 + 28) / 3 = 34
This matches the value calculated by our formula.
In order to calculate the average of a different number of top n values, we can change the 3 in the ROW function of the formula to a different number.
For example, we can calculate the average of the top 5 values in the range by using the following formula:
=AVERAGE(LARGE(A2:A11,ROW(1:5)))
The following screenshot shows how to use this formula in practice:
We can see that the average of the top 5 values in the range A2:A11 is 29.6.
We can also verify this is correct by manually calculating the top 5 values in the range:
Average = (44 + 30 + 28 + 24 + 22) / 5 = 29.6
This matches the value calculated by our formula.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Calculate Average and Drop Lowest Value in Excel
How to Average Every Nth Row in Excel
How to Calculate Average and Ignore Zero and Blank Cells in Excel
