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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.