Suppose you have a dataset that includes students' names and their corresponding mathematics scores. It is possible that some students have got the same score, resulting in a tie. The goal is to rank the students based on their scores without breaking sequence in the dataset. In short, we need to assign the same rank to the students with the same score.
In the example shown below, highlighted ones are the students with their same scores.
For descending rank : In cell D3, type the following formula.
=SUMPRODUCT(($C$3:$C$10>$C3)/COUNTIF($C$3:$C$10,$C$3:$C$10))+1
In descending rank, the student with the highest score is assigned the rank of 1, the second-highest score is assigned the rank of 2, and so on.
For ascending rank : In cell E3, type the following formula.=SUMPRODUCT(($C$3:$C$10<$C3)/COUNTIF($C$3:$C$10,$C$3:$C$10))+1
In ascending rank, the student with the lowest score is assigned the rank of 1, the second-lowest score is assigned the rank of 2, and so on.
The above formula calculates the rank of the value in cell C3 compared to the other unique values in the range $C$3:$C$10. The rank is determined by counting the number of values greater than (or less than) the value in cell C3, divided by the count of each unique value. The sum of these fractions, plus 1, gives us the rank. The students with the same score have been assigned same rank.
- The formula uses the SUMPRODUCT function, which multiplies arrays and returns the sum of the products.
- The first part of the formula, ($C$3:$C$10>$C3), compares each value in the range $C$3:$C$10 to the value in cell C3. It returns an array of TRUE/FALSE values, where TRUE represents values greater than (or less than) the value in cell C3.
- The second part of the formula, COUNTIF($C$3:$C$10,$C$3:$C$10), calculates the count of each unique value in the range $C$3:$C$10. It returns an array of counts for each unique value in the range.
- Dividing the array from step 2 by the array from step 1 gives us a fractional array. Each element in this array represents the fraction of values greater than the value in cell C3 compared to the total count of each unique value in the range.
- By summing up the fractional array using the SUMPRODUCT function, we get the sum of all the fractions.
- Finally, adding 1 to the sum gives us the rank of the value in cell C3 within the range $C$3:$C$10.
Related Article : Rank duplicates and assign different ranks
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Hello..This is very helpful to me in ranking a range which has duplicate values. But when I copy the rank to another blank excel with values I get decimal value also ..For Ex. rank 3 showing as 3.000012 and 4 as 4.000036 aswell..how can I prevent this and get only the rank as 3 and 4 ..How to resolve it without a Round(,0)function wrapped your formula? BJ
ReplyDeletethanks , this solved a big problem ,
ReplyDelete