The RANK.EQ function in Google Sheets returns the rank of a number in a given dataset. It’s a Statistical function that works similar to RANK.

Is there any difference between the two functions?

Nope! You can use either of the ones. But I suggest you use the RANK.EQ over RANK as it seems the latter is kept only for backward compatibility.

The EQ in the former function stands for Equal. That means if there is more than one same number in the given dataset, the ranks of that numbers will be the same (equal).

In such a case, instead of the same rank, you can get the average rank using the function RANK.AVG. But there is no way to break the tie without writing a custom formula. I’ll post that in a later tutorial.

Update: How to Rank without Ties in Google Sheets.

Let’s come back to our topic.

The best way to understand the above RANK.EQ function in Google Sheets is to use it in a sorted list of numbers. By doing so, you can understand two things.

1. The rank of a number is its position in the dataset.
2. We can change the position from top to bottom or bottom to top.

But it’s not necessary to sort the list (data set) in any order (A-Z or Z-A) to use the RANK.EQ formula in Google Sheets. You will get more detail in the example section down below.

Without further ado, let’s go to the RANK.EQ function syntax in Google Sheets. The examples follow.

Syntax – RANK.EQ Function in Google Sheets

It’s a very simple to learn statistical function, and there are only three arguments. The third argument is optional.

Syntax: RANK.EQ(value, data, [is_ascending])

value – any value in the list (data set) whose rank you want to determine (eg. rank of value in cell A1 in the list A1:A10).

data – The list of numbers in an array or range, for example, A1:A10. Non-numeric values in the data will be ignored.

is_ascending – to specify how to rank the value. The default order is FALSE (descending/Z-A). That means the max value will get rank # 1. Use TRUE for ascending order (A-Z). In this case, the min value will get rank # 1.

Example

Assume a small cell range A1:A3 contains the scores 25, 50, and 40, respectively.

The rank of the values will be 3, 1, and 2 in the default order (FALSE) and 1, 3, and 2 in ascending (TRUE) order.

The default one (in B2:B4) is the preferred ranking as the top value gets the rank (position) 1. The reverse of the same is in the cell range D2:D4.

Note:- The array reference in the ‘data’ must be specified as absolute array reference (dollar sign with array reference), and the cell reference in the ‘value’ must be specified as a relative cell reference. It is to avoid issues when you drag the formula down.

To make you clearly understand how to use the RANK.EQ function in Google Sheets, I am using a sorted list of numbers. The following RANK.EQ formula in cell B2 copied down.

=rank.eq(A2,$A$2:$A$11,true)

Since I have used TRUE as the third optional argument, the min value gets the rank # 1.

As you can see, there are duplicate scores in cell range A3:A4. The formula returns the same rank # 2 for both the scores.

If you use the default order in is_ascending (FALSE), you will get a reverse sequence. I mean, the max value will get rank # 1.

=rank.eq(A2,$A$2:$A$11,true)

In this case, the rank of the duplicate scores in A3:A4 will be 8. It is because the number in cell A2 gets the rank # 9.

How to Use Array Formula with RANK.EQ Function in Google Sheets

The RANK.EQ function in Google Sheets supports the function ArrayFormula.

If you follow my last example, you can use the below function in cell B2 to return the rank of all the scores in A2:A.

=ArrayFormula(IFNA(rank.eq(A2:A,A2:A,true)))

The formula should be entered in an empty cell range. I mean, delete all the values in B2:B first, then key in B2. Otherwise, it may return a #REF error.

Please note that the formula covers an open range. So it would return #N/A from row # 12 onwards because of the blank rows.

The IFNA in the formula removes those errors and return blanks.

That’s all about how to use the RANK.EQ function in Google Sheets. Thanks for the stay. Enjoy!

Resources:

• How to Find Rank of a Non-Existing Number in an Existing Data Range.
• Flexible Array Formula to Rank Without Duplicates in Google Sheets.
• How to Rank Group Wise in Google Sheets in Sorted or Unsorted Group.
• Top 10 Ranking without Duplicate Names in Google Sheets.
• Compare and Highlight Up and Down in Ranking in Google Sheets.
• Find the Rank of an Item in Each Column in Google Sheets.
• Highlight Top 10 Ranks in Single or Each Column in Google Sheets.