Excel formula to get ranking position

Question

I have a table of people with points. The more points, the higher your position. If you have the same points you are equal first, second etc.

   | 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

Using an Excel formula, how can I automatically determine the position? I'm currently using an IF statement that works fine for 5 or 6 matching positions, but I can't add 30+ if statements because there's a limit to the formula.

=IF(C7=C2,B2,IF(C7=C3,B2+5,IF(C7=C4,B3+4,....

So if the points column is the same as the position above then it's the same position value. If the points are less than above then it drops a position so the previous row position +1. But if the row above that is the same then it's the previous position +2 and so on.

Answer 1

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.

Answer 2

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 player's position.