A challenge for someone much smarter than myself (Please Help!)

[shenricus]

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

 

[Hui]

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

 

[Hui]

If you want a real solution try the following

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

Add a Header Row to your data
Add the following Columns to your Model
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

Your model should look like:


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:

You may also want to read my post about this type of solution at:
http://chandoo.org/wp/2011/05/11/using-solver-to-assign-item/

 

[bosco_yip]

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

 

[Hui]

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:

 

[afennings]

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!!

 

[Luke M]

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.