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    nbsp   nbsp   A  nbsp   nbsp   nbsp   nbsp   nbsp    B  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    C 1   name  nbsp    nbsp   position   points 2   person1   1  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    10 3   person2   2  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    9 4   person3   2  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    9 5   person4   2  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    9 6   person5   5  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    8 7   person6   6  nbsp   nbsp   nbsp   nbsp   nbsp   nbsp    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

User · Answer

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

User · Answer

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

User · 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

User · Answer

Try this in your forth column   COUNTIF B B    gt    amp  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

User · Answer

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

User · Answer

The way I ve done this  which is a bit convoluted  is as follows    Sort rows by the points in descending order Create an additional column  D  starting at D2 with numbers 1 2 3     total number of positions 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  Copy this formula down the entire column Copy the positions column C   then paste special    values to overwrite the formula with positions Resort the rows to their original order   That s worked for me  If there s a better way I d love to know it

[excel] Excel formula to get ranking position

Examples related to excel