This guide to find the highest N Values in each group in Google Sheets is useful if you want to find the top-ranking items in the first n places within an array.
Table of Contents
This is especially helpful if you have a dynamic sheet where the values constantly change, and it’s important to keep up with the top-ranking entries for your monitoring purposes.
Let’s look at an example.
You are a class monitor and your job is to collect the grades of a class for every quiz, and post an update of students and their scored quizzes they have collected thus far. Your task is to keep track of the top two scored quizzes of each student that counts towards their final score, which changes often due to additional requirements.
In this problem, we consider each student as a group and the top 2 highest quiz scores as the highest 2 values.
You want to have a formula that will announce the top 2 quizzes of each student immediately after inputting the new scores that can potentially change rankings. It will also help students keep track of their grades and aim for a score that will help their final grade.
How might we go about that?
It’s as simple as finding the highest 2 values for each group within the array.
For this problem, you might be tempted to use RANK, MAX, or a combination. However, this solution also offers a straightforward method.
This solution will use a combination of
MATCHfunctions to find our highest N values. This formula will run a Google API that simplifies data analysis, sort through, and find the correct values and find the corresponding group that matches said values.
Not to worry if the formula doesn’t make sense yet. By the end of this article, you will understand what each part of the formula does, so just keep your mind open and patient as you learn each portion step-by-step.
The Anatomy of the QUERY Function
The syntax (the way we write) the
QUERY function is as follows:
Let’s break the formula down to understand what is being required in each portion:
=the equal sign is how we start all the functions in Google Sheets
QUERYis our function. We need to provide the data, query, and optional headers.
datais the range of cells to perform the query on. Note that each column of data should be of the numeric, string, or Boolean type. If a column mixes data types, it will execute the query considering the majority data type of the column, where other data types are considered as null.
queryis the query to perform as written in the Google Visualization API Query Language. It should be a value enclosed in quotation marks, or a cell reference containing the appropriate text.
headersare [OPTIONAL], and it indicates the number of header row on top of the data. If you don’t fill it up, it is set as a guess according to the data contents.
A Real Example of Using QUERY Function
Have a look at the example to see how you can use the
QUERY function to find the data that you need in a complex spreadsheet. Let’s see this class list and see which students have submitted their final projects.
The above image shows how to use the
QUERY function to find the desired data among the different details within the array. The function is as follows:
=QUERY(A2:E21,"Select A, C, D, E WHERE E='No'")
After you input this formula, Google Sheets will run (don’t be surprised, it may take a while to load the results you are looking for) and instantly populate the cells with the subset of the array that you needed. Note that the query is an actual instruction, wrapped in quotation marks. It has some getting used to, but the powerful
QUERY function can be very intuitive with a lot of practice!
Here is what this example does:
- We wrote our
QUERYfunction with the variables separated by commas.
- We prepared a cell for the formula to output the subset we want to find, extracted from the larger set. In the example, we prepared the headers we want, and input the function in cell G2. Note that this is the only cell you need to manipulate.
- The first variable is the data range. Therefore, we selected the range of the entire set, including the headers. Here we input A:E.
- The second variable is the query. It is part of the Google API, and you can read more about the possible English-language queries to insert in this portion of the formula. For this example, we simply want the ID, Last Name, Class slot, and whether the student submitted their final project. We write, in double quotation marks, “Select A, C, D, E WHERE E=’No’”. Note what data each column letter refers to, and how in the final column, the requirement as a string was inputted with single quotation marks.
- You can also add more items to the list. If you adjust the range, then your list will automatically populate.
With some practice, you will be familiar with how the
QUERY function works.
Make a copy of the spreadsheet from the link attached below and try it for yourself:
How to Find the N Highest Values in a Group in Google Sheets
Here is a step-by-step process to find the n highest values in a group in Google Sheets using the
QUERY function combined with
- To start, select the cell where you want to show the result of your query. Make sure there is enough space to the right of the cell to populate. In the example, We chose cell G3.
- Since we are working on a query that deals with more than 1 desired result (in this case, we want the n = 2 highest scores, we must use the
ARRAYFORMULAfunction. Enter the equal sign and look for
- After that, type in the QUERY function. For now, just focus on the
QUERYfunction and what’s inside it, and let the
ARRAYFORMULAwork its Google Sheet magic.
- It’s time to fill in the first requirement, data. Add an open curly bracket to start this section. We want sorted columns of name and scores so that it’s easy to refer to it. In this example, we want the Alphabetical names of the students in order. We apply the data range B3:E. To sort the names, we pick column 1 and pick true for is_ascending. To sort the scores, we pick column 4 and pick false for is_ascending, since we’re already getting the highest 2 scores for each student.
- The following
IFERRORstatement, you will note, uses the
MATCHto find if the number of rows in the array minus the position of the matched item. This is part of the data range of the original
QUERYstatement. It’s advised that you follow this statement as is to avoid any errors, only adjusting the formula to your range and the desired data that you want. For example, we picked the
ROWB3:B to pick the B column, and in
MATCHpicked the same data range from Step 4. Closeout your
MATCHfunction with return_type 0. Close this data part in the original query function – don’t forget the closing curly brace!
IFERROR statement is as follows:
=IFERROR(ROW(B3:B)-MATCH(QUERY(SORT(B3:E,1,true,4,false),"Select Col1"),QUERY(SORT(B3:E,1,true,4,false),"Select Col1"),0))
- Add the query section of the
QUERYfunction. Remember that we are interested in the Student name, and the top 2 scores. Write: “Select Col1, Col4, where Col5<4”, where Col1 is the names column, Col4 is the scores column. Wait – what does Col5 refer to then? Think about this: you are essentially creating a Col5 that displays your desired results. Now that we have to pick through those results as well, instead of accepting the number of values <3 (for two highest values), we need to use <4 because of that extra data.
- Close this out with a double parenthesis – because of
ARRAYFORMULA– and you will see your list populate like magic!
And with that, you’re done! You can now use the above
MATCH function to find the highest n values per group in a set. Create powerful spreadsheets with these formulas and more.