Excel formula to get ranking position

Excel formula to get ranking position

You could also use the RANK function

=RANK(C2,$C$2:$C$7,0)

It would return data like your example:

  | A       | B        | C
1 | name    | position | points
2 | person1 | 1        | 10
3 | person2 | 2        | 9
4 | person3 | 2        | 9
5 | person4 | 2        | 9
6 | person5 | 5        | 8
7 | person6 | 6        | 7

The Points column needs to be sorted into descending order.

Type this to B3, and then pull it to the rest of the rows:

=IF(C3=C2,B2,B2+COUNTIF($C$1:$C3,C2))

What it does is:

  • If my points equals the previous points, I have the same position.
  • Othewise count the players with the same score as the previous one, and add their numbers to the previous players position.

Excel formula to get ranking position

You can use the RANK function in Excel without necessarily sorting the data. Type =RANK(C2,$C$2:$C$7). Excel will find the relative position of the data in C2 and display the answer. Copy the formula through to C7 by dragging the small node at the right end of the cell cursor.

Leave a Reply

Your email address will not be published.