Excel formula to get ranking position

Tag: excel Author: smithlzh Date: 2009-08-12

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.

Best Answer

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.

Other Answer1

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.

comments:

Perfect. Other suggestions required adding other columns which are fine if they worked, this is doing exactly what is needed. The COUNTIF() needs to be extended longer to get a better range but otherwise spot on. Thanks.

Other Answer2

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).

Other Answer3

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)

Other Answer4

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!

Other Answer5

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.

comments:

Elkannah Whettey answer is excellent. If the data is not sorted and if the Points are such that the lower the points the higher the position then use the solution offered by Elkannah Whettey. But deduct the RANK from 1 plus the number of entries. =7-RANK(C2,$C$2:$C$7).