Status
Not open for further replies.

shenricus

New Member
Hello,

I have 24 people who each have their own score (please see attached data sample). I've been trying to figure out how I can divide these names into 3 even teams - or as close as possible.

I've been doing some research and I think this is called 'Partitioning' and quite frankly, it looks incredibly complicated to me - can anyone please help?

Many thanks

Attachments

• Sample.xlsx
33.3 KB · Views: 23
Shenricus

Firstly, Welcome to the Chandoo.org Forums

I'd argue that with a maximum 1.9% variance of scores from the mean, that they are all so close the distributions into teams will have minimal impact/effect

If you want a real solution try the following

It involves using Solver So ensure that you have that installed and available

C1: 'x-x_mean
C2: =B2-AVERAGE(\$B\$2:\$B\$25)
Copy C2 down to C25
D1: Group 1
E1: Group 2
F1: Group 3
G1: Chk
D2:F25: 0
G2: =SUM(D2:F2)
Copy G2: Down to G25

D26: =SUMPRODUCT(\$C\$2:\$C\$25,--(D2:D25))
Copy across to F26
D27: =ABS(SUM(D26:F26))

D29: =SUM(D2:D25)
Copy across to F29

Goto the Data, Analyse, Solver Tab and select Solver
Complete the Solver sections as per below:

Click Solve

After a few seconds you should get a solution

Enjoy

If you can't follow the above review the file attached:

http://chandoo.org/wp/2011/05/11/using-solver-to-assign-item/

Attachments

• Sample.xlsx
11.3 KB · Views: 20
Hi,

Since Column B "Score value" has sorted, a formula way by :

1] C2 copy down :

=IF(ROWS(\$1:1)<=COUNT(\$B\$1:\$B\$100)/2,MOD(ROW(A1)-1,3)+1,MOD(3-ROW(A1),3)+1)

2] D2 array formula copy down ( confirmed by SHIFT+CTRL+ENTER) :

=IFERROR(INDEX(\$A\$1:\$A\$25,SMALL(IF(\$C\$1:\$C\$25=D\$1,ROW(\$A\$1:\$A\$25)),ROWS(\$1:1))),"")

3] E2 array formula copy down ( confirmed by SHIFT+CTRL+ENTER) :

=IFERROR(INDEX(\$B\$1:\$B\$25,SMALL(IF(\$C\$1:\$C\$25=D\$1,ROW(\$B\$1:\$B\$25)),ROWS(\$1:1))),"")

4] Select D2:E10, copy and paste to F2:G10 and H2:I10

5] E11, enter formula and copy to G11 and I11
=AVERAGE(E2:E10)

6] See attached file

Regards

Attachments

• SampleGrouping3.xlsx
11.9 KB · Views: 30
Last edited:
Bosco

Interesting approach
Your solution has a Standard deviation of the Sum of each Group of 0.00368179

I used an idea from yours and changed the Solver parameters, so that it minimised the Standard Deviation

When I ran it it took about 3 minutes to calculate but came up with a more optimal solution with a Std Dev of teh Sum of each team of: 0.000942809

see attached file that has both solutions included:

Attachments

• Sample.xlsx
12.7 KB · Views: 30
Hi Hui and all,
I am trying to recreate this same scenario but want to create 8 teams of two people (16 participants in total). and can't seem to get my solver to work. I have them ranked from 1-16 in terms of skill and hope to randomly create even teams. I've attached it here. thank you for your help!!

Attachments

• Creating 8 teams of 2.xlsx
13.4 KB · Views: 9
Hi Hui and all,
I am trying to recreate this same scenario but want to create 8 teams of two people (16 participants in total). and can't seem to get my solver to work. I have them ranked from 1-16 in terms of skill and hope to randomly create even teams. I've attached it here. thank you for your help!!

Rather than hijacking a thread (especially an old thread) it would be better to start your own new thread where others will be more likely to see it.

Status
Not open for further replies.