Table of Contents
Google Sheets is a spreadsheet program that can be used to extract the top N values from a range of cells. This can be done by using the function ‘QUERY’ which allows the user to specify the number of results to be returned in the form of a query. The user can also specify criteria to narrow down the results. This is a useful way to quickly analyze data and extract meaningful insights from it.
You can use the following syntax to extract the top N values from a data range in Google Sheets:
=query(A1:C16, "Select A,B,C Order by B Desc Limit 5")
This particular query looks at the data in the range A1:C16 and returns the rows with the 5 highest values in column B.
To extract the top 10 values, simply replace the 5 with a 10 in the formula.
The following example shows how to use this syntax in practice.
Example: Extract Top N Values from Range in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the points and rebounds for 15 different basketball teams:
We can use the following query to extract the rows with the 5 highest points values:
=query(A1:C16, "Select A,B,C Order by B Desc Limit 5")
The query in cell E1 returns the rows with the five highest points values, automatically sorted in descending order.
Note that we can use the following query to extract the rows with the 10 highest points values:
=query(A1:C16, "Select A,B,C Order by B Desc Limit 10")
The query in cell E1 returns the rows with the 10 highest points values, automatically sorted in descending order.
We could also use the following query to simply return the 10 highest points values without any of the other columns:
=query(A1:C16, "Select B Order by B Desc Limit 10")
The query in cell E1 returns the 10 highest points values, automatically sorted in descending order.