Using CountIf function along RANK.EQ to have unique rank values
RANK.EQ function
This function tells us the rank of a number in the list of numbers. If there are repetitive numbers in the list the top rank of that set of values is returned.
The below is the function syntax. Here number is the value whose rank we wanna know, ref is an array or reference of the list and order is option. 0 or omitted Order sorts in descending order and any other non-zero in ascending order.
RANK.EQ(number,ref,[order])
Marks
|
Rank
|
80
|
1
|
85
|
2
|
86
|
3
|
86
|
3
|
87
|
5
|
88
|
6
|
88
|
6
|
89
|
8
|
The function will give us rank in above way; we can see that for repetitive marks, the rank is top one and same.
RANK.EQ function combined with CountIF Function.
If we want unique rank results then by combining the Countif function, we can do that. Below is the formula.
=RANK.EQ(A2,$A$2:$A$9,1)+COUNTIF($A$2:$A2,A2)-1
Marks
|
Rank
|
Unique Ranks
|
80
|
1
|
1
|
85
|
2
|
2
|
86
|
3
|
3
|
86
|
3
|
4
|
87
|
5
|
5
|
88
|
6
|
6
|
88
|
6
|
7
|
89
|
8
|
8
|
