Ranking in three groups

[John 4348]

Hello everyone
I am trying to rank students in 3 groups, for example (A2:A21,A22:A43,A44:62).I don't use table and I also would like the Range works dynamic.
If two students have the same score they will receive the same rank.

 

[awoohaw]

try:

I'm not sure what you mean by dyamic in this context. But this will update when you put new values in column A.

mr excel questions 12.xlsm
	A	B
1	15	18
2	27	7
3	19	15
4	28	5
5	17	16
6	28	5
7	12	19
8	20	14
9	22	11
10	23	10
11	25	8
12	33	2
13	33	2
14	11	20
15	17	16
16	21	12
17	25	8
18	21	12
19	29	4
20	37	1
21	15	18
22	29	12
23	38	1
24	14	19
25	38	1
26	36	4
27	34	7
28	30	10
29	24	15
30	12	21
31	15	18
32	36	4
33	33	9
34	17	17
35	19	16
36	13	20
37	36	4
38	25	14
39	28	13
40	34	7
41	37	3
42	10	22
43	30	10
44	24	7
45	13	15
46	13	15
47	13	15
48	11	19
49	40	1
50	40	1
51	15	13
52	13	15
53	40	1
54	20	8
55	16	12
56	37	4
57	18	11
58	33	5
59	20	8
60	32	6
61	15	13
62	20	8
Sheet12

Cell Formulas
Range	Formula
B1:B21	B1	=RANK.EQ(A1,$A$2:$A$21,0)
B22:B43	B22	=RANK.EQ(A22,$A$22:$A$43,0)
B44:B62	B44	=RANK.EQ(A44,$A$44:$A$62,0)

 

[John 4348]

Thank you
1. By Dynamic, I mean when the range increases from (B1:B22) to (B1:B25),it will update.
2. By the same rank, I mean for example: score 20 = 1 , 19 = 2 ,18 =3
not 20=1, 20=1, 20= 1, 19 =4

 

[John 4348]

B22:B43 >B26: B46

 

[John 4348]

B44: B62 > B47:B64

 

[awoohaw]

@John 4348 , i have no idea what you mean by the last two inequalities?
And you have never had any data in column B, so I'm not sure what youre asking when you say the range increases.
My column B is the rank.eq function you asked for in the three ranges in column A.

 

[Peter_SSs]

Since your ranges are
a) Not the same size as each other, and
b) Not fixed in size
you would need some way to indicate which rows belong to which of the 3 groups.
Here is one way

23 03 02.xlsm
	A	B	C
1	Group	Value	Rank
2	1	1	20
3	1	19	15
4	1	61	6
5	1	55	7
6	1	23	13
7	1	17	16
8	1	95	1
9	1	40	9
10	1	8	18
11	1	40	9
12	1	39	11
13	1	24	12
14	1	16	17
15	1	95	1
16	1	63	5
17	1	23	13
18	1	2	19
19	1	67	3
20	1	66	4
21	1	45	8
22	2	85	1
23	2	57	9
24	2	78	3
25	2	12	20
26	2	81	2
27	2	39	11
28	2	23	17
29	2	31	13
30	2	17	19
31	2	47	10
32	2	28	16
33	2	30	14
34	2	30	14
35	2	73	5
36	2	68	8
37	2	38	12
38	2	12	20
39	2	18	18
40	2	2	22
41	2	77	4
42	2	70	7
43	2	73	5
44	3	24	14
45	3	79	4
46	3	28	11
47	3	82	2
48	3	67	6
49	3	53	8
50	3	1	18
51	3	4	16
52	3	32	10
53	3	1	18
54	3	66	7
55	3	26	13
56	3	98	1
57	3	19	15
58	3	69	5
59	3	48	9
60	3	80	3
61	3	28	11
62	3	4	16
63
64
65
66
67
68
69
70
Rank in groups

Cell Formulas
Range	Formula
C2:C70	C2	=IF(A2="","",SUMPRODUCT((A$1:A$100=A2)*(B2<B$1:B$100))+1)

 

[John 4348]

I apologize all.
I explain. I rank the three groups of students, once each group may be 18 students, more or less. when they increase or decrease, I exchange the range manually. for example: group 1 from A2:A20 to A2:A25 and group 2 from A21:A42 to A26:A47. I would like the range exchanges automatically. I send 3 pictures.

 

[Peter_SSs]

Looks like my previous suggestion was close but that when there are equal ranks you do not want to skip any numbers before the next rank value.
In that case try this formula. Note that this is an array formula and in your 2010 Excel version it must be confirmed with Ctrl+Shift+Enter, not just Enter before you copy it down.

23 03 02.xlsm
	A	B	C
1	Group	Value	Rank
2	1	20	1
3	1	17	4
4	1	16	5
5	1	19	2
6	1	18	3
7	1	20	1
8	2	20	1
9	2	15	5
10	2	18	3
11	2	17	4
12	2	19	2
13	3	19	2
14	3	14	5
15	3	16	4
16	3	17	3
17	3	20	1
18
19
20
Rank in groups (2)

Cell Formulas
Range	Formula
C2:C20	C2	=IF(A2="","",COUNT(IF(A$2:A$100=A2,IF(B$2:B$100>B2,IF(MATCH(B$2:B$100,IF(A$2:A$100=A2,B$2:B$100),0)=ROW(B$2:B$100)-ROW(B$2)+1,1,NA()))))+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[John 4348]

Thank you.
that is perfect.