Excel Formula for Position Ranking

Question

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

  | A | B | C
1 | name   | position | Points
2 | person1 | 1 | 10
3 | man2 | 2 | 9
4 | people3 | 2 | 9
5 | people4 | 2 | 9
6 | people5 | 5 & ​​nbsp; | 8
7 | man6 | 6 | 7

Using an Excel formula, how can I automatically determine the position? I am currently using an IF statement that works great for 5 or 6 matching positions, but I cannot add 30+ if statements because there is a formula constraint.

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

So, if the column of points coincides with the position above, then this is the same value of the position. If the points are smaller than the higher, then he omits the position, so the previous position of the line is +1. But if the line is higher than the same, then this is the previous position +2, etc.

Answer 1

You can also use the RANK function.

=RANK(C2,$C$2:$C$7,0)

It will return data, for example, 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 should be sorted in descending order.

Answer 2

B3, :

=IF(C3=C2,B2,B2+COUNTIF($C$1:$C3,C2))

:

• , .
• , , .

Answer 3

C- , , . , , ( b3):

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

LARGE n- , C- :

= (2: C7,3)

Answer 4

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

B2 B3 ..

, , , , (+1 ).

Answer 5

, , , :

• (D), D2 1,2,3,...
• (D2) if (C2 = C1), D2, C1). , . , D , , .
• (C), →
• .

! , !

Answer 6

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 to C7 by dragging the small node to the right end of the cell cursor.