Making Teams in the Blind - Schedule Known

[Jeff]

I have a totally different problem and maybe it is out of the realm of these posts.  I have a situation where people sign up for a competition with already established averages between 50 and 100.  I want to divide those that sign-up into teams of three so that each team has roughly the same average.  This oftentimes will pair a 100 average with two with a 50 average.   Or there will be three with a 70 average making up a single team.
Is there an excel spreadsheet that could do this automatically for me if I just place the name and the average into the columns?
Thanks,
Bald at Last

[Ian Wakeling]

Hi Jeff,

I think you probably need some sort of algorithm to optimize the assignments to threesomes, so Excel may not be the best choice here.  If you have to do it "by hand" then use Excel to sort the values into order and carefully pick the threesomes.  Once you have one, it is likely that you can find another by shifting up or down the line one position.

Ian.

[Richard A. DeVenezias]

How many people are signing up for the competition ?

For N people there are this many combinations that would have to be searched for the 'best balanced'

     N x (N-1) x (N-2) / 6

The best balanced might be the arrangement where the standard deviation of the mean of the threesomes is the smallest amongst all arrangements.

[Ian Wakeling]

But that's just for the first threesome, for the next one there are still

(N-3)*(N-4)*(N-5) / 6 ways, and so on.....

An N of 18 may already be too big to carry out an exhaustive search.

[Jeff]

Well, so far it has been a trial and error set of attempts.  Currently the data (player and average) are entered onto an excel spreadsheet.  Then the listing is sorted in desending order.  Then just by inspection, the top, bottom, and middle players are paired.  It is not perfect and sometimes there are wide variances in the threesomes that have to get additional work.  It takes several hours for the individual to perform the work.  Since this is all mathematical manipulation there should be an easier way to let the computer grind through the numbers more efficiently.  Oftentimes there are anywhere from 30 to 60 players (10-20 teams) involved.  Additionally, there is interest in expanding this to include 2, 4, or 5 players.  But no one wants to go through the work.  This is where the computer program or an excel macro would benefit.
Thanks.
Jeff