Average of Top N Percent of the Values in Google Sheets – With or Without Conditions

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

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%)))
Extract top n percent values in Sheets

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:

Average of top 20% values based on condition

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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.