Table of Contents
The ability to calculate the average of top N values in Excel is an incredibly useful and powerful tool for data analysis. It allows users to quickly identify trends in their data, and to make informed decisions based on the results. The average of top N values can be used to measure the performance of a particular dataset or to compare different datasets. It can also be used to identify outliers and to filter out data that may not be relevant. Having the ability to calculate the average of top N values in Excel will help users gain deeper insights into their data and make better decisions.
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.