How to Use RANK Function in Google Sheets – Example Formula

0

Just like the MODE function, RANK is another useful statistical function in Google Sheets. Let me explain to you how to use the RANK Function in Google Sheets with one example.

While the MODE function returns the most commonly occurring number or date from a range, we can use the RANK function to find the rank of a number from a range. It’s so simple.

Example to RANK Function in Google Sheets

Syntax:

RANK(VALUE, DATA, [IS_ASCENDING])

Here in this function, the value is the value of which the rank to be determined. Data means the array or range containing the dataset to consider.

The other element in the syntax, which is “is_ascending”, is entirely optional. It can decide whether to rank the highest or lowest value as 1. I think you can ignore this element as it’s irrelevant in most cases.

How to Use RANK Formula in Google Sheets

Please see the below example.

rank formula example in google sheets

The above is a sample sales report. There are three salespersons, A, B, and C, and you can see their sales amount against each of their names.

How do we find the Rank of these salespersons based on their total sales (value)?

Summarising Data For Ranking

For that first, I summarise the data as below.

Summary of Sales Report for Rank

As a side note,  I’ve just used the SUMIF function to summarise the data, though it’s not important here. We need the summary to understand the RANK function in Google Doc Spreadsheets.

You can see that there is one column I left blank in the above summary, and that is “Rank” (column M). The number of salespersons is only three here. So you can easily find the rank of salespersons.

Here salesperson “C” holds Rank # 1, “A” holds rank # 2, and “B” holds rank # 3.

Rank Formula

You can use a RANK formula to automate the ranking. It will be useful when the number of salespersons is large, and the sales values are flexible and may change at any time.

rank formula results

I am explaining the first formula. It applies to the subsequent two.

In the first formula, we first determine the rank of L3, i.e. 4560 against the range L3:L5. Similarly, the second and third formula has been used.

Hope you could understand the above use of the RANK function in Google Sheets.

Note:-

It seems the RANK.EQ function replaces the RANK function, and the latter is kept available for backward compatibility. So for more details, please check the former function guide.

Additional Resources:

  1. How to Find Rank of a Non-Existing Number in an Existing Data Range.
  2. Flexible Array Formula to Rank Without Duplicates in Google Sheets.
  3. How to Use the RANK.AVG Function in Google Sheets.
  4. How to Rank Group Wise in Google Sheets in Sorted or Unsorted Group.
  5. Top 10 Ranking without Duplicate Names in Google Sheets.
  6. The PERCENTRANK Functions in Google Sheets.
  7. Percentile Rank Wise Conditional Formatting in Google Sheets.

Prashanth KV
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

LEAVE A REPLY

Please enter your comment!
Please enter your name here