How to Get the Top N Percent Scores From Each Group in Google Sheets

There is no shortcut to filter or get the top n percent scores from each group in Google Sheets.

One of my readers, Lorraine, asked me about the possibility of filtering the top 10% scores from each group.

As far as I know, in Docs Sheets, there is no built-in command or function for the same. I have workarounds. So I thought about writing this tutorial.

We can use combinations of four functions – Percentile, Filter, Vlookup, and IF – for the said purpose. There are also four steps involved.

In my last post, I have already covered the first step. I’ll link to that post when we start writing the formula below.

Without further ado, I am going to the steps below.

Get the Top N Percent Scores From Each Group – Step-by-Step Instructions

Sample Data (in cell range B1:C17) and Expected Result (in cell range in G7:H10):

Filtering Top N Percent Scores From Each Group
screenshot # 1

There are two groups in my sample, and they are “a” and “b”.

In my result, there are three scores. Two from the first group and one from the last group. Because in the example, we are filtering the top 10% values from each group.

To get the top n percent scores from each group in Google Sheets, we should first find the k-th percentile value.

That means to get the top 10% scores, first, we should find the 90th percentile. We can use the Percentile function for the same. That’s step 1.

Here are the four steps involved.

Percentile and Top N Percent Values - Steps
screenshot # 2

Group Wise 90th Percentile of Scores (Step 1)

We are writing the formula to get the top n percent scores from each group in Google Sheets. Let’s consider the n is 10%.

So what we should do is to find the 90th percentile value from each group.

That means the scores above the 90th percentile values are the top 10% scores in each group.

In cell G2, unique the items using the following Unique formula.

=unique(B2:B)

In cell H2, key in the following formula and drag to H3 to get the 90th percentile of each item.

=round(percentile(filter($C$2:$C,$B$2:$B=G2),90%))

For details (formula explanation), you can read this post – Calculating Percentile for Each Group in Google Sheets.

Note:-

Rounding the percentile is a must, and I have opted for that in the formula. But it may sometimes cause issues if you have decimal scores.

For example, if the 90th percentile is 6.5 when you round, it will become 7. So if you have no scores above 7, the formula may not return any rows from that group.

Example:

The 90th percentile of the values 5, 5, 5, 5, 6.5, 6.5, and 6.5 will be 6.5. If you round this percentile value, it will become 7. So you won’t be able to filter the top 10% values from this.

That means, if you have decimal scores, either round down or do not round the percentile value.

Lookup and Assign Percentiles to Groups (Step 2)

Step 2 is the key in filtering the top n percent scores from each group.

The above formula returns two scores in cell range H2:H3. Using a Vlookup Array Formula, we can assign those values against the items.

Insert the following Vlookup in cell D2.

=ArrayFormula(IFNA(vlookup(B2:B,G2:H3,2,0)))

Vlookup searches the items (B2:B) in the first column of the range (G2:H3) and returns the 90th percentile values from the second column (H2:H3).

Now you may please take a look at the column C and D in the screenshot above.

As you can understand, we only want to filter the rows wherever the score is greater than or equal to the corresponding percentile value.

We can apply this condition in the next column using a logical statement. That’s the next step.

Logical Test Scores and Percentile Values (Step 3)

We can insert the following IF statement for the said purpose.

=ArrayFormula(if(C2:C="",,if(C2:C>=D2:D,true,false)))

This step 3 formula should go in cell E2.

What does this formula do?

It tests whether the scores (C2:C) are >= percentile values (D2:D). If it satisfies, the formula returns TRUE else FALSE.

Now to the final part.

Filter TRUE Values to Get the Top N Percent Scores from Each Group (Step 4)

Since we have already completed the groundworks, it’s now easy to get the top n (here 10) percent values from each group.

Just insert the following FILTER formula in cell G8, and voila!

=filter(B2:C,E2:E=true)

The formula filters the table (B2:C) if the column E2:E contains TRUE.

Conclusion

The only roadblock in combining all the above four steps into a single formula is the step 1 formula.

We can’t make that an array formula.

That means we can get the result in 2 steps.

The first one is, of course, the step 1 formula. The second one is the combined step 2 to 4 formulas.

You can skip the steps 2 to 4 formulas and instead use the below formula in G8.

=filter(B2:C,if(C2:C="",,if(C2:C>=IFNA(vlookup(B2:B,G2:H3,2,0)),true,false))=true)

Additional Notes:-

In the above sample data, there are only two groups. So the step 1 formula returns only two rows in G2:H3.

If you have growing data, you can’t predict the number of groups.

So better insert the above formula in cell J2 and replace G2:H3 in the formula with G2:H. Then, drag the H2 formula further down depending on the number of groups.

That’s all about how to get top n percent scores from each group in Google Sheets.

Thanks for the stay. Enjoy!

Sample_Sheet_25321

Resources:

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.