How to rank data in Google Sheets with the RANK function

Reading Time: 4 minutes

The RANK function in Google Sheets is used to rank a list of numbers in a particular order–ascending or descending.

Syntax
Formula to rank numbers in Google Sheets:

=RANK(value, data, [is_ascending])

value – The value whose rank is to be determined.

data – The array or range containing the dataset to consider.

is_ascending – [ OPTIONAL – 0 by default ] Whether to consider the values in data in descending or ascending order.
If this is 0, the greatest value in data will have rank 1; if this is 1, the least value in data will have rank 1.

Sample Usage
=RANK(B2, B2:B6)

//This will give the rank of the value in B2 according to the values in the range B2:B6.

What is the RANK Function in Google Sheets?

The RANK function in Google Sheets allows you to rank a list of numbers in a particular order, i.e., ascending or descending.

Example of the RANK function in Google Sheets
Figure 1: Example of the RANK function in Google Sheets

Important things to keep in mind while using the RANK function in Google Sheets

  • The values placed in the RANK function must be numerical values.
  • The value to be ranked should be in the data range, otherwise, Google Sheets will throw an #N/A error.
  • If the data do not contain the value in any cell or element, RANK will return the #N/A error.
  • Reference your cells if you want to use the same cell range for multiple cell values.

How to use the RANK function in Google Sheets

A step-by-step guide on how to use the RANK function in Google Sheets to rank numbers.

Step 1: Create a dataset

  • Open Google Sheets.
  • Create a dataset with the numbers you want to rank.

Step 2: Use the RANK Function in Google Sheets

  • Take a cell outside the data range.
  • Use the RANK function in Google Sheets as per requirement.
  • It is important to keep the data range fixed and referenced. If you don’t keep the range fixed, the range will be dynamic and the ranking will be affected. An example of this error is covered later in this article.
  • Keep in mind that the value should be in the column range provided.

Example of the RANK Function in Google Sheets

Let’s consider a sample dataset with marks scored by students in a mathematics class. The dataset looks like this:

Sample dataset to rank numbers in Google Sheets
Figure 2: Dataset of interest

Now, we want to rank them based on their scores. Use the RANK Formula in Google Sheets as shown below:

=RANK(B2,$B$2:$B$6)
  • Here, the first parameter is cell B2. 
  • The second parameter is the cell range. If you observe closely, you’ll notice that the dollar sign is placed before the cell numbers which makes them fixed, ie, referenced.
  • We can now drag down the selection to C6 and see the ranks of students, as shown below:
Example of RANK function in Google Sheets with fixed cell range
Figure 3: Example of RANK function in Google Sheets with fixed cell range

If we do not reference the cells, the output will be completely different.

Let’s say we use the following RANK formula in Google Sheets:

=RANK(B2,B2:B6)
  • Here, the cell range isn’t fixed. So if we drop down the selection, the cell range is changed for every cell, thereby giving us incorrect results.
  • For cell B3, the cell range is B3:B7, for cell B4, the cell range is B4:B8 and so on.
  • The output in this case is:
Example of RANK function in Google Sheets with dynamic cell range
Figure 4: Example of RANK function in Google Sheets with dynamic cell range

Using the RANK function in Google Sheets to rank in Ascending Order

The above exercise ranks the numbers in descending order. We can use the RANK function to rank the numbers in Google Sheets in ascending order.

Let us consider the dataset given below.

Dataset of interest to rank numbers in Google Sheets using the RANK function
Figure 5: Dataset of interest

We want to find the rank of anyone who has scored 68, with Rank 1 assigned to the one with the lowest mark.

The formula to rank the numbers in ascending order is:

=RANK(68,$B$2:$B$10,1)
  • Here, 68 is the value you want to rank.
  • B2:B10 is the range of values to consider. (Dollar signs indicate fixed range).
  • The third parameter, 1, is setting the ranking in ascending order. If you want to rank in descending order, change the third parameter to 0.

The formula returns 5 as the result, indicating that 68 is the fifth largest value in the range of numbers.

Conclusion

The RANK function in Google Sheets allows you to rank cells in ascending or descending order. The value should be in the cell range provided else Google Sheets will throw an error. If you want to know more about the RANK function in Google Sheets, go through the official documentation here.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets

Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.

How to View Edit History of Cells in Google Sheets | Easy 2 Minute Guide: Learn how to view the edit history of cells in Google Sheets. Learn to spot mistakes or past changes.

shalinamigo

←
→

Leave a Reply