• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

Hui

Excel Ninja
Staff member
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

Excel Ninja
Staff member
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:
upload_2017-4-30_14-16-39.png

Goto the Data, Analyse, Solver Tab and select Solver
Complete the Solver sections as per below:
upload_2017-4-30_14-19-48.png

Click Solve

After a few seconds you should get a solution
upload_2017-4-30_14-21-11.png

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/
 

Attachments

bosco_yip

Excel Ninja
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

Last edited:

Hui

Excel Ninja
Staff member
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

upload_2017-4-30_22-43-54.png

see attached file that has both solutions included:
 

Attachments

afennings

New Member
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

Luke M

Excel Ninja
Staff member
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.
Top