To calculate the average of top n percent of values in Google Sheets, you can use the PERCENTILE function as the criterion in AVERAGEIF.
Further, when you want to calculate the average of top n percent of values based on condition, use the function PERCENTILE within AVERAGEIFS.
What more? You can even replace the AVERAGEIF and AVERAGEIFS functions with AVERAGE + FILTER combo. See that all the interesting combinations below.
To explain further, we need a sample data. Let me start with a very basic example.
How to Calculate the Average of Top N Percent Values Without Condition
Using the above sample data I want to find the average of the top 20% of the values. I have marked the values that fall above the top 20% mark. Also, see the average on the screenshot. Let’s see how to calculate the same using a formula.
Steps:
Using the function PERCENTILE we can find the value at a given percentile of a range. For example,
=percentile(A2:A11,20%)Result: 280
This 280 is the value at the given percentile (20%) of the selected range/array. We can’t use this value like >280 or <280 to find the top 20% of the values in the range.
The reason, in PERCENTILE calculation the values are taking from the lowest to highest (ascending order), not on the reverse.
So in order to get the top 20% of the vales, you must use the PERCENTILE formula as below.
=percentile(A2:A11,(100%-20%))or
=percentile(A2:A11,(80%))Then use it in FILTER formula as below.
=filter(A2:A11,A2:A11>percentile(A2:A11,(100%-20%)))
Simply wrap this formula with the AVERAGE function to get the average of top 20% of the values in a range.
=average(filter(A2:A11,A2:A11>percentile(A2:A11,(100%-20%))))Actually, we don’t want the FILTER formula in between to find the average of top n percent (here 20%) of the values. Using the FILTER function, I was just showing you how to filter top n% values.
Here is the alternative to the above formula to find the average of top n percent, here 20%, values in Google Sheets.
=averageif(A2:A11,">"&percentile(A2:A11,(100%-20%)))Calculating the Average of Top N % Values With Condition in Google Sheets
Sample Data:
Here I want to calculate the average of the top n (read 20%) percent sales values from the “South” region.
In this scenario also we can use either the FILTER based formula or AVERAGEIFS (not AVERAGEIF)
Here are both solutions. You can compare these two formulas with my earlier formulas to understand how I have incorporated the condition in them.
AVERAGIFS Based Formula:
=averageifs(D2:D,C2:C,"South",D2:D,">"&percentile(D2:D,(100%-20%)))Filter Based Formula:
=average(filter(D2:D,C2:C="South",D2:D>percentile(D2:D,(100%-20%))))That’s all for now. Enjoy!
