If you use the RANK function in a range to find the rank of numbers, it returns duplicates if there is any number repeated. Then how to Rank without duplicates in Google Sheets?
Rank is not on my list of favorite functions in Google Sheets. I did some experiments with this function in my leisure and realized that there is no way to return unique ranks in an array.
In other words, Ranking without duplicates is not possible with the RANK function in Google Sheets. But I have made a custom formula to do this.
Please see the below table. In column B, I have the Rank function, and I have my custom formula in Column C. See how both these formulas treat the numbers and return the Rank.
Since the number in cell A2 repeats twice (you can see that the number is the same in Cell A8), the Rank formula in column B returns the same rank in Cell B2 and B8.
But my custom formula in Column C intelligently returns Unique Ranks. What is that formula to Rank without duplicates in Google Sheets?
The Formula to Rank Without Duplicates in Google Sheets
First, see the RANK formula which I have used in Cell B2.
=ArrayFormula(rank(A2:A8,A2:A8))Here is my formula in Cell C2 that returns the Rank without duplicates.
=ArrayFormula(if(len(A2:A),array_constrain(sort({row(A2:A)-1,sort({row(A2:A)-1,A2:A},2,false)},2,true),9^9,1),))This formula has the following features. Please go through it if you want to use it in any column and any range.
- The Rank without Duplicates formula checks the values in the range A2:A and expands the result up to the last non-empty cell.
If you don’t want that flexibility, that means, if you want the formula to work only up to a certain number of rows, you can remove the first part of the formula, i.e., up to the Array_Constrain, and remove the end part from the last comma.
For example, if you want to find the ranks of numbers in the range A2:A20 without duplicates, you can use the formula below.
=array_constrain(sort({row(A2:A20)-1,sort({row(A2:A20)-1,A2:A20},2,false)},2,true),9^9,1)- I have set the formula to work from Row 2. If your range is A5:A, then change the -1 with -4.
This time, I am skipping the formula explanation part. If you want to learn the functions involved in my formula, here are the links.
Main Formulas:
- Array_Constrain
- Sort
- Row
Formulas to Restrict the Formula Expansion:
- ArrayFormula
- IF
- Len
That’s all about the formula to Rank without duplicates in Google Sheets. Enjoy!
Update: You can find one more formula here – How to Rank without Ties in Google Sheets.
Related Reading: How to Find Rank of a Non-Existing Number in an Existing Data Range.
