number grouping

[drltr6]

I have a list of numbers from smallest to largest. The number list varies from 12 to 32 numbers. I want to assign the letter "A" to the lowest 25% of the numbers, a "B" to the next lowest 25%, a "C" to the next lowest 25%, and a "D" to the top 25%. In other words, I want to break down my list into 4 separate groups no matter how many numbers are in the list.
Any help would be greatly appreciated.

 

[lrobbo314]

Something like this?

drl
	A	B	C	D	E
1	1	A		8	A
2	2	A		19	C
3	3	A		7	A
4	4	A		29	D
5	5	A		30	D
6	6	A		1	A
7	7	A		4	A
8	8	A		27	D
9	9	B		32	D
10	10	B		2	A
11	11	B		13	B
12	12	B		23	C
13	13	B		9	B
14	14	B		31	D
15	15	B		6	A
16	16	B		11	B
17	17	C		22	C
18	18	C		17	C
19	19	C		10	B
20	20	C		12	B
21	21	C		18	C
22	22	C		16	B
23	23	C		20	C
24	24	C		5	A
25	25	D		24	C
26	26	D		3	A
27	27	D		21	C
28	28	D		14	B
29	29	D		28	D
30	30	D		26	D
31	31	D		25	D
32	32	D		15	B
Data

Cell Formulas
Range	Formula
A1:A32	A1	=SEQUENCE(32)
B1:B32	B1	=XLOOKUP(A1#,QUARTILE.INC($A$1#,SEQUENCE(4)),CHAR(SEQUENCE(4,,65)),,1)
D1:D32	D1	=RANDOMIZE(A1#)
E1:E32	E1	=XLOOKUP(D1#,QUARTILE.INC(D1#,SEQUENCE(4)),CHAR(SEQUENCE(4,,65)),,1)
Dynamic array formulas.

 

[drltr6]

Thanks! This would work if I always had 32 numbers, but I could have any multiple of 4 numbers. Such as 28 or 24 or 20. How can I adapt this to account for changes in the amount of numbers in the list? For example, if I have 24 numbers, then I need 6 A's, 6 B's etc. If I have 16 numbers, I need 4 A's, 4 B's etc..

 

[jasonb75]

Based on the mini sheet in post 2, try this in E1 and fill down.

=IF(D1="","",CHAR(65+MATCH(D1,SMALL($D$1:$D$32,{0,1,2,3}*COUNT($D$1:$D$32)+1)))

 

[drltr6]

Thank you! I will try to fit that in.

 

[Ezguy4u]

drit Because you can never have too many solutions, here is one more. Now this does have a limit of 40 but if you need more just change the $A$40 to $A$400 or whatever you need. Just make sure the number is divisible by 4.

22-11-1.xlsx
	A	B	C
1	2		A
2	3		A
3	5		A
4	7		A
5	8		A
6	9		A
7	11		A
8	15		A
9	17		B
10	19		B
11	20		B
12	21		B
13	22		B
14	24		B
15	25		B
16	27		B
17	30		C
18	35		C
19	36		C
20	37		C
21	37		C
22	37		C
23	37		C
24	37		C
25	37		D
26	37		D
27	37		D
28	37		D
29	37		D
30	37		D
31	37		D
32	37		D
Data for 40

Cell Formulas
Range	Formula
C1:C32	C1	=IF(A1<>"",IF((COUNT($A$1:$A$40)*0.75)<ROW(A1),"D",IF((COUNT($A$1:$A$40)*0.5)<ROW(A1),"C",IF((COUNT($A$1:$A$40)*0.25)<ROW(A1),"B","A"))),"")

 

[lrobbo314]

I don't know what you mean that the example I posted would only work for 32.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	A		1	A		1	A			1	A
2	2	B		2	A		2	A			2	A
3	3	C		3	A		3	A			3	A
4	4	D		4	B		4	A			4	A
5				5	B		5	A			5	A
6				6	B		6	A			6	A
7				7	C		7	B			7	A
8				8	C		8	B			8	A
9				9	C		9	B			9	B
10				10	D		10	B			10	B
11				11	D		11	B			11	B
12				12	D		12	B			12	B
13							13	C			13	B
14							14	C			14	B
15							15	C			15	B
16							16	C			16	B
17							17	C			17	C
18							18	C			18	C
19							19	D			19	C
20							20	D			20	C
21							21	D			21	C
22							22	D			22	C
23							23	D			23	C
24							24	D			24	C
25											25	D
26											26	D
27											27	D
28											28	D
29											29	D
30											30	D
31											31	D
32											32	D
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=SEQUENCE(4)
B1:B4,L1:L32,H1:H24,E1:E12	B1	=XLOOKUP(A1#,QUARTILE.INC(A1#,SEQUENCE(4)),CHAR(SEQUENCE(4,,65)),,1)
D1:D12	D1	=SEQUENCE(12)
G1:G24	G1	=SEQUENCE(24)
K1:K32	K1	=SEQUENCE(32)
Dynamic array formulas.

 

[drltr6]

Players​	Hdcp​	Tee​	A,B,C,D​	TEAM​
zBlind Draw	X​	g​	​	​
zBlind Draw	X​	g​	​	​
Martin, Steve - 37	0​	G​	​	​
DiLaura, Tom - 13	4​	W​	​	​
Lancaster, Don - 171	4​	G​	​	​
Wayt, Tim - 403	4​	G​	​	​
Baun, Dan - 179	5​	W​	​	​
Randall, Chuck - 313	5​	G​	​	​
Wallner, Dave - 304	5​	G​	​	​
McDuff, Mark - 851	7​	G​	​	​
Lopez, Tony - 362	8​	G​	​	​
Wilson, Larry - 900	8​	G​	​	​
Lussier, Denis - 185	8​	W​	​	​
Geyer, Rolly - 145	9​	W​	​	​
Plummer, Bob - 126	9​	G​	​	​
Pfeiller, Bill -1113	10​	G​	​	​
Hourigan, Bob - 32	11​	W​	​	​
Krumrie, Harry - 189	11​	W​	​	​
Thurman, Mike - 76	11​	W​	​	​
Fleischmann, Jacques - 1229	13​	W​	​	​
Schoendorf, Tom - 298	13​	W​	​	​
Goldade, Ron - 91	14​	G​	​	​
Schrauben, Fred - 6251	14​	W​	​	​
Connors, Don - 152	15​	W​	​	​
Feliks, Don - 388	16​	B​	​	​
Williams, Tom - 81	17​	B​	​	​
Mizell, Leslie - 499	17​	W​	​	​
Dupuis, Bob - 236	18​	G​	​	​
	​	​	​	​
	​	​	​	​
	​	​	​	​
	​	​	​	​

This is my golf spreadsheet. I want to divide the players into 4 equal groups (A,B,C,D) based on Handicap. Blind Draw players will be in the "D" group. The number of players will always be a multiple of 4. In this example there are 28 players, so the "A" group will have the first 7 players based on Handicap. The "B" group will have the next 7 players etc. I need a formula that will calculate this when the total number of players changes. Thanks!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[drltr6]

2011 MacOS