How to Find the Highest N Values in Each Group in Google Sheets

Are you looking to find the highest values in each group in Google Sheets? Well, look no further! In this article, I will share with you a formula that will help you achieve this effortlessly.

Introducing the Formula

When it comes to finding the highest n values in each group, functions like RANK, MAX, MAXIFS, or even LARGE may not suffice. But fret not, because I have a formula up my sleeve that will do the trick!

The formula I am about to share with you makes use of QUERY, SORT, MATCH, LET, CHOOSECOLS, and ROW functions. It may seem a bit unfamiliar at first, but trust me, it works like magic! With this formula, you’ll be able to find the highest n values in each group in Google Sheets.

Let’s dive right in!

The Formula

Here’s the formula that you can use in your Google Sheets to find the highest 2 scores of each group:

=LET(
  sorted, SORT(A2:B,1,true,2,false),
  ARRAYFORMULA(
    QUERY(
      HSTACK(
        sorted,
        IFERROR(ROW(A2:A)-MATCH(CHOOSECOLS(sorted,1),CHOOSECOLS(sorted,1),0))
      ),
      "Select Col1,Col2 where Col3<3"
    )
  )
)

Note: I’ve made some improvements to the formula to enhance its performance. I’ve included the LET function to avoid repetitive calculations and used HSTACK instead of curly brackets to stack data horizontally.

Explaining the Formula

I understand that the logic behind this formula might be a bit complex. So instead of delving into its intricacies, let me provide you with detailed instructions on how to use it effectively in your sheet.

Changing the Highest 2 Value Rows in Each Group to N Value Rows

You might be wondering how you can adjust the formula to find the highest 3 values or even a user-defined number of values in each group. Well, it’s simpler than you think!

The last part of the formula contains the Query “Where” clause, which determines the number of values to consider. In this case, the clause “where Col3<3” indicates that we are looking for the highest 2 values/rows in each group.

If you want to find the highest 3 values in each group, you just need to change it to “where Col3<4”. Similarly, you can adjust the number by changing the value in the clause.

Accommodating More Columns in the Formula

What if you have more than two columns in your dataset? Not to worry! You can still use this formula by making a few adjustments.

The crucial part you need to focus on is the SORT formula. Let’s say you have a four-column dataset. In that case, you should modify the SORT formula as follows:

SORT(A2:D,1,true,4,false)

This means that you’re sorting the name (group) column in ascending order and the value column containing the highest n values in descending order.

Next, you should slightly adjust the Query select clause (the last part of the formula). For example, if you have a four-column dataset, the select clause should be:

Select Col1,Col2,Col3,Col4 where Col5<3

Here, the number of columns is four, so it should be reflected in the Query. The “Col5<3” part determines the number of values you want to consider.

Adjusting the Formula for Any Number of Columns

You can easily adapt this formula for datasets with any number of columns. Simply follow these steps:

1. Modify the SORT formula to match your dataset.
2. Adjust the Query select clause by increasing the number accordingly.

For example, if you’re working with a 10-column dataset, the last part of the formula should be “Col11<3”.

And that’s it! You now have the power to find the highest n values in each group in Google Sheets, regardless of the number of columns in your dataset.

Feel free to explore and experiment with this formula to suit your needs. Happy analyzing!

For more exciting tips and tricks related to Google Sheets, visit Crawlan.com. You’ll find a wealth of knowledge to take your spreadsheet skills to the next level!

Image source: Crawlan.com