Calculate Average of Top N Percent Column in Excel 365
In this article, we will discuss how to calculate the average of the top N percent of a column in an Excel 365 workbook. This is particularly useful when working with large datasets where you want to find the average of the top values instead of the entire column.
Calculating the Top N Percent
To start, we need to identify the top N percent of the column. Let's assume we have a table on one tab of our workbook called "Data" and the column we are interested in is called "Results". The "Results" column contains the results of 100 trials.
The first step is to sort the "Results" column in descending order. We can do this by selecting the "Results" column, then going to the "Data" tab in the ribbon and selecting "Sort & Filter" > "Sort Largest to Smallest". This will sort the entire column in descending order based on the values in the "Results" column.
Now that we have the "Results" column sorted in descending order, we can calculate the Nth percentile. This can be done using the PERCENTILE.EXC function. The syntax for the PERCENTILE.EXC function is:
=PERCENTILE.EXC(array, k)
Where:
array: The array of values for which you want to calculate the specified percentile.k: The percentile to be calculated (a value between 0 and 1, exclusive).
For example, if we want to calculate the 95th percentile, we can use the following formula:
=PERCENTILE.EXC(Data!Results, 0.95)
This formula will return the value that is greater than or equal to 95% of the values in the "Results" column.
Calculating the Average of the Top N Percent
Now that we have identified the top N percent of the "Results" column, we can calculate the average of those values. We can do this by using the AVERAGEIFS function. The syntax for the AVERAGEIFS function is:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Where:
average_range: The range of cells to average. This can be a single cell, a range, or an array.criteria_range1,criteria_range2, ...: The range of cells to evaluate the criteria.criteria1,criteria2, ...: The criteria to apply to the range of cells. This can be a number, expression, cell reference, or text.
In our example, we want to calculate the average of the values in the "Results" column that are greater than or equal to the 95th percentile. We can use the following formula:
=AVERAGEIFS(Data!Results, Data!Results, ">="&PERCENTILE.EXC(Data!Results, 0.95))
This formula excludes the 95th percentile value from the average calculation because the PERCENTILE.EXC function returns the smallest value that is greater than or equal to the specified percentile.
- To calculate the average of the top N percent of a column in Excel 365, first sort the column in descending order.
- Calculate the Nth percentile using the
PERCENTILE.EXCfunction. - Calculate the average of the values that are greater than or equal to the Nth percentile using the
AVERAGEIFSfunction.