---

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: