CODEWITHSUNDEEP
excel
Peter Craig
Peter Craig

Reputation: 7279

Excel formula to get ranking position

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.

Upvotes: 5

Views: 173909

Answers (6)

Elkannah Whettey
Elkannah Whettey

Reputation: 21

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.

Upvotes: 2

Robert Mearns
Robert Mearns

Reputation: 11986

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.

Upvotes: 19

macleojw
macleojw

Reputation: 4159

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!

Upvotes: 0

sdfx
sdfx

Reputation: 21713

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)

Upvotes: 0

RaYell
RaYell

Reputation: 70404

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

Upvotes: 1

Zed
Zed

Reputation: 57648

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.

Upvotes: 2

Related Questions