This article explains the process of ranking and assigning different ranks to duplicates in Excel.
Let's say you have a dataset containing students' names along with their english scores. There are instances where some students have achieved the same score. You need to rank these students based on their scores. Make sure you assign different ranks to the students with the same score.
For descending (decreasing) order :
In cell D3, type the following formula.
=RANK(C3,$C$3:$C$10,0)+COUNTIF(C3:$C$10,C3)-1
For ascending (increasing) order :
In cell E3, type the following formula.
=RANK(C3,$C$3:$C$10,1)+COUNTIF(C3:$C$10,C3)-1
The formula above calculates the initial rank using RANK and then adjusts the rank based on the number of duplicates for the value in C3. The result is the final rank for the value in C3 within the range $C$3:$C$10.
- The RANK(C3,$C$3:$C$10,0) part calculates the rank of the value in cell C3 within the range $C$3:$C$10. It assigns a rank based on the descending order of values in the range.
- The COUNTIF(C3:$C$10,C3) part counts the number of occurrences of the value in cell C3 within the range $C$3:$C$10. It determines the number of duplicates for the value in C3.
- The +COUNTIF(C3:$C$10,C3)-1 part adjusts the rank by subtracting 1 for each duplicate occurrence. This ensures that each duplicate value receives a unique rank.
Related Article : Rank duplicates without breaking sequence
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.
Share Share Tweet