Even Distribution Formula

[Daigodai]

Say I have 120 bowling players, their id_number in col_a,
they all have different skill level, indicated by his avg_score in col_b

I need to allot them into n teams (say 15), of equivalent strength on the
TEAM level
so no team ends up with mostly high-scorers and vic-versa.

I'm not sure the title is right. How do you call this ?
And does Excel 2003 has a formula foir this ?
thanks!

ID AVG_SCORE TEAM_NUMBER
-------- ---------------- --------------------
1 287
2 103
3 139
4 100
5 243
6 207
7 130
8 233
9 213
10 199
11 80
12 153
13 185
14 189
15 249
16 106
17 253
18 244
19 103
20 240
21 203
22 181
23 184
24 122
25 202
26 187
27 92
28 179
29 189
30 194
31 234
32 198
33 287
34 230
35 163
36 120
37 244
38 191
39 284
40 178
41 253
42 286
43 184
44 222
45 107
46 108
47 193
48 291
49 268
50 281
51 125
52 204
53 285
54 126
55 174
56 194
57 214
58 156
59 203
60 137
61 106
62 289
63 137
64 205
65 134
66 168
67 198
68 134
69 85
70 238
71 208
72 210
73 235
74 212
75 167
76 236
77 214
78 82
79 118
80 175
81 173
82 143
83 88
84 178
85 245
86 112
87 98
88 206
89 91
90 291
91 182
92 275
93 185
94 125
95 88
96 91
97 159
98 87
99 281
100 117
101 271
102 107
103 206
104 208
105 198
106 218
107 145
108 204
109 133
110 216
111 294
112 92
113 224
114 216
115 240
116 276
117 106
118 159
119 116
120 150

 

[Bill Kuunders]

One way......

sort them all in ascending order
group the result in groups of 15 and place them next to each other

sort group 2 4 6 and 8 in descending order
your teams are now listed horizontally with total scores between 1480 and
1460
or per average player of between 182.5 and 185

the overall average is 183.95

 

[T. Valko]

Very clever! Good job!

Biff

One way......

sort them all in ascending order
group the result in groups of 15 and place them next to each other

sort group 2 4 6 and 8 in descending order
your teams are now listed horizontally with total scores between 1480 and
1460
or per average player of between 182.5 and 185

the overall average is 183.95