If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Friday, November 7, 2014

Using CountIf function along RANK.EQ for unique rank values

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