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

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.

Answer 2

If your C-column is sorted, you can check whether the current row is equal to your last row. If not, use the current row number as the ranking-position, otherwise use the value from above (value for b3):

=IF(C3=C2, B2, ROW()-1)

You can use the LARGE function to get the n-th highest value in case your C-column is not sorted:

=LARGE(C2:C7,3)

Answer 3

Try this in your forth column

=COUNTIF(B:B; ">" & B2) + 1

Replace B2 with B3 for next row and so on.

What this does is it counts how many records have more points then current one and then this adds current record position (+1 part).

Answer 4

The way I've done this, which is a bit convoluted, is as follows:

1. Sort rows by the points in descending order
2. Create an additional column (D) starting at D2 with numbers 1,2,3,... total number of positions
3. In the cell for the actual positions (D2) use the formula if(C2=C1), D2, C1). This checks if the points in this row are the same as the points in the previous row. If it is it gives you the position of the previous row, otherwise it uses the value from column D and thus handle people with equal positions.
4. Copy this formula down the entire column
5. Copy the positions column(C), then paste special >> values to overwrite the formula with positions
6. Resort the rows to their original order

That's worked for me! If there's a better way I'd love to know it!

Answer 5

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.