RANK Function – Google Sheets

The RANK function in Google Sheets is a statistical function that assigns a rank to a value within a set of values. It is helpful for ranking data in ascending or descending order and identifying the top or bottom values in a dataset.

Syntax

The syntax for the RANK function is as follows:

=RANK(value, data, [is_ascending)

• value – The value whose rank you want to determine.
• data – The range of cells containing the dataset to rank.
• is_acending – An optional parameter that specifies the ranking direction
  • 0 – Default value. The largest value is ranked 1.
  • 1 – The smallest value is ranked 1.

Similar Functions

RANK.AVG – Shows the average rank of a value in a set of values. RANK.AVG is a newer replacement function for RANK.

RANK.EQ – Shows the rank of a value in a set of values. This function is one of the two newer replacements for RANK.

SORTN – Creates a sorted list of the top n values from a data set

Common Errors

#VALUE! – An input is something other than a number. Check the values to ensure they are all numbers.

#N/A! – No valid input data. One cause is the value is not in the data.

Examples

Here are several examples of how to use the RANK function in Google Sheets:

Example 1 – Simple Ranking

Suppose you have a dataset of sales figures for different products and want to rank the products based on their sales. You can use the following formula to rank the products in descending order:

Formula in cell C2 =RANK(B2, B$2:$B4)

Where:

• B2 is the cell reference for the sales figure of the first product.
• B$2:B$4 is the cell range containing the sales figures for the products.

There is no need to specify the is_ascending argument to have the largest amount ranked first. Therefore, we only need the value and data inputs for this example. Note the $s used in the range. The $s fix the reference so the row numbers do not change when you copy the formula into D3 and D4.

Example 2 – Rank Smaller Numbers First

Now, let’s say you have a dataset of race times and want to rank the runners based on their scores. You can use the following formula to order the runners’ times in descending order:

Formula in cell C2 =RANK(B2,B$2:B$4,1)

Where:

• B2 is the cell reference for the race time the first runner.
• B$2:B$4 is the cell range containing the race times for all the runners.
• 1 specifies you want to rank the times in descending order.

Example 3 – How to Rank Equal Amounts (Ties)

Suppose you have a dataset of student grades, and you want to identify the top students in the class. You can use the following formula to identify the top students.

Formula in cell C2 =RANK(B2,B$2:B$5)

Where:

• B2 is the cell reference containing the grade of the first student
• B2:B5 is the cell range containing the student grades.

However, notice that there are two students with an 87%. The rank function gives both students a rank of 2, skips a rank, and assigns a 4 to 75% since it is the fourth highest score.

See the RANK.AVG function if you need average rankings instead of this technique. RANK.AVG would return 2.5 for the two 87%s.

Example 4 – Rank All Amounts with One Formula

The previous examples have one formula for each row in examples 1 through 3. However, you can use the ARRAYFORMULA to repeat the ranking formula for multiple rows. ARRAYFORMULA lets to provide a range for the value argument instead of one cell.

Let’s redo example 3 with one formula.

Formula used in cell C2 =ARRAYFORMULA(RANK(B2:B5,B2:B5))

Live Examples in Google Sheets

Make a copy of this Google Sheet to see the formulas in action.

Tips for using the RANK function

• If the data range includes duplicate values, the RANK function will assign the same rank to the identical values.
• If the data range contains empty cells, the RANK function will ignore the empty cells.

Video Tutorial

Conclusion

You can use the RANK function as a powerful tool to analyze data in a variety of ways. By understanding how to use the RANK function, you can perform more sophisticated calculations and identify trends and patterns in your data that you may not have been able to see before.