I need to create balanced teams based on average and gender. I have a data base that includes that information. I have approximately 56 people and would make 4 person teams. I know I can sort by average and then manually make the teams. I was wondering if there were any functions that would do this? If you have any suggestions, I would appreciate hearing them.
Thanks,
Linda
One way would be to use RANDBETWEEN (I donāt think you would need to add salt for collisions) in a companion column and then break apart your data into 14 chunks after sorting. Calculate an AVERAGE for each chunk, and a sum of squares for the averages. Then goal seek with some number of iterations for a small sum of squares, re-firing the RANDBETWEENs each time.
Hereās a version that leaves the goal seeking up to you. Just get used to the variance for awhile, then decide on ālow enoughā. I really donāt know the implications of this methodā¦but it was fun to put into practice without macro code.
Teamator9000.ods (38.1 KB)
PS: If you really just want to group on ability Rank by ability instead of the random value then create four columns instead of Start and End that alter the modular arithmetic to pick every 14th player, instead of every chunk with 1/14th of the players. Of course, it would be nice to then randomize within those to pick actual teams to eliminate the cascading biasā¦
Wow! I am very impressed. It is so far beyond my ability with functions, it is going to take me awhile to understand what you are doing. Thanks for spending the time to put this together.
@LindaPro, can you post the actual 56 ability scores, no names or other data attached?
Using my random values:
If picking every 14th player from the list of 56 players sorted by ability, it looks like the team variance is in the 200 range. That is without doing anything to avoid the cascade bias, which is, in fact, very noticeableāhere are the team abilities using sort-and-pick by ability:
99.225
104.975
106.8
108.35
110.1
114.35
120.4
125.15
130.625
132.2
133.55
136.65
139.65
141.35
Notice how the team ability goes up very regularly.
So, for now, if a person gets to a variance on the example spreadsheet of, say, 350 or below, they have an ability-balance on par with sort-and-pick alone, and, probably, game-theoretically better because of the randomizationā¦they have no foreknowledge like they do with the cascade bias.
The Teamator 9100 will come out as soon as work slows down a little. 
It will feature a choice of sorting methodsāI hope.
You are spending a lot of time on this. The formulas are so far over my head, I would need an expert to break them down for me. Here are the averages that I have. I need to have the teams balanced so they are within a few points of each other. It isnāt easy to do manually and I appreciate all your help.
AVG
174
172
162
159
157
156
153
152
149
148
148
145
145
143
143
143
143
142
142
139
138
138
133
129
129
129
129
128
127
126
123
123
123
122
121
121
121
119
119
119
118
115
114
114
111
111
110
109
108
106
106
104
99
96
95
83
Given the listing of players associated with the abilities you gave:
|Player 1|174|
|Player 2|172|
|Player 3|162|
|Player 4|159|
|Player 5|157|
|Player 6|156|
|Player 7|153|
|Player 8|152|
|Player 9|149|
|Player 10|148|
|Player 11|148|
|Player 12|145|
|Player 13|145|
|Player 14|143|
|Player 15|143|
|Player 16|143|
|Player 17|143|
|Player 18|142|
|Player 19|142|
|Player 20|139|
|Player 21|138|
|Player 22|138|
|Player 23|133|
|Player 24|129|
|Player 25|129|
|Player 26|129|
|Player 27|129|
|Player 28|128|
|Player 29|127|
|Player 30|126|
|Player 31|123|
|Player 32|123|
|Player 33|123|
|Player 34|122|
|Player 35|121|
|Player 36|121|
|Player 37|121|
|Player 38|119|
|Player 39|119|
|Player 40|119|
|Player 41|118|
|Player 42|115|
|Player 43|114|
|Player 44|114|
|Player 45|111|
|Player 46|111|
|Player 47|110|
|Player 48|109|
|Player 49|108|
|Player 50|106|
|Player 51|106|
|Player 52|104|
|Player 53|99|
|Player 54|96|
|Player 55|95|
|Player 56|83|
the best balanced teams I got are (where each row is a different team with total team ability at the left):
502 Player 53 @ 99,Player 41 @ 118,Player 4 @ 159,Player 30 @ 126
509 Player 48 @ 109,Player 34 @ 122,Player 9 @ 149,Player 24 @ 129
527 Player 56 @ 83,Player 44 @ 114,Player 1 @ 174,Player 6 @ 156
503 Player 38 @ 119,Player 54 @ 96,Player 14 @ 143,Player 12 @ 145
529 Player 47 @ 110,Player 18 @ 142,Player 42 @ 115,Player 3 @ 162
500 Player 36 @ 121,Player 29 @ 127,Player 40 @ 119,Player 23 @ 133
524 Player 33 @ 123,Player 50 @ 106,Player 32 @ 123,Player 2 @ 172
527 Player 35 @ 121,Player 25 @ 129,Player 26 @ 129,Player 10 @ 148
552 Player 43 @ 114,Player 5 @ 157,Player 27 @ 129,Player 8 @ 152
503 Player 20 @ 139,Player 45 @ 111,Player 46 @ 111,Player 19 @ 142
524 Player 21 @ 138,Player 55 @ 95,Player 7 @ 153,Player 22 @ 138
500 Player 28 @ 128,Player 49 @ 108,Player 37 @ 121,Player 16 @ 143
535 Player 15 @ 143,Player 52 @ 104,Player 17 @ 143,Player 13 @ 145
516 Player 15 @ 143,Player 51 @ 106,Player 39 @ 119,Player 11 @ 148
Thatās a maximum team difference of 52.
You might take a look at these, and see if thatās good enough. Perhaps a little hand-tweaking thereafter. Please post an honest evaluation of usability.
This looks a lot closer to what I was looking for. Once I replace numbers with names, I would probably have to do some tweaking because there are some guidelines (spouses canāt bowl on the same team, genders are balanced as best as possible, etc.) Could you please post the spreadsheet you used to get these results? I would really like to take a look at it. There will be additions and deletions by the time the league starts. If I could make the changes, I would like to start with this.
The calculations you used for this are quite a bit over my head. I feel like it is amazing that you were able to put this together so quickly. Thanks again for all your hard work in creating this spreadsheet.
Iām sure youāll have to tweak some. You can also [Seek] until you see a balance of team scores and gender, before you copy out the roster to begin hand editing.
You can set the generator to pick:
You can set the level of salting you want for the salted ranking.
You can target a particular cross-team āvarianceā [derived from actual variance].
The roster now includes gender so your eye can just scan for Fās and Mās (or whatever).
Note: This now uses a macro, obviously, to do the seeking. So, youāll have to make sure you allow that (after checking!). To use the [Seek] button, of course, Tools>Forms>Design Mode has to be off.
Teamator9100.ods (37.1 KB)
I am super impressed! You have done a lot more than I ever expected. Hopefully, I have the macro settings correct. The Design Mode didnāt appear to have a way to turn it on/off. It seems to be fine. I will work with it tomorrow.
Thank you so much!
Hello joshua4,
you have built a really great tool. However, you should adjust your formula in column B a little to prevent the same random numbers from arising. That can happen with only 10,000 numbers. In your result from 12.April, for example, you used player 15 twice. Player 31 is missing for this.
Your formula could therefore be:
=RANDBETWEEN(1;10000)+ROW()/100
My best result was this one, by the way (modificated 16.april):
Determined in a Python adaptation of your calculation. Starting with a random order, I always exchanged the members within the best and the worst team until a more balanced ratio emerged. I then repeated this until no improvement was possible. This usually leads to a variance of the mean of less than 1, which is always good enough. In the example above, the result was 0.0168.
Thanks. 30,000,000. And yes, a person can really see the gap closing.
.
For the record, that flaw was knowingly built in:
but Iām surprised that it became a thing with 10,000ā¦guess thatās what you get for assuming! Itās also a great lesson about āprogressive projects,ā where a person builds in shortcuts when they are doing a rough-up but then as they get a little more serious about the project they donāt go back and solidify things. Iād love to have pay for the hours Iāve spent on that scenarioā¦
The OQ is a great problem since Iām pretty sure that the question itself is NP-complete. Compare with having a team of 14 sales makers each needing to visit 4 towns, and then wanting each sales maker to drive the same distance to cover all their towns.
I hadnāt gotten far enough yet to see any number hadnāt been used. Should I change the formula in column B. I really like the way the number have become so close in the team ability column. Thanks for looking at this.
Yes, @dscheikey is correct. That is a solid collision avoidance method. Technically it should be something like:
=RANDBETWEEN(1,10000)+(ROW()-ROW(FirstSortedAbility)+1)/COUNT(UnsortedAbilities)
(untested)
so that the number of players could expand to beyond 100 in a general solution. However, given where you are in the specific task at hand, if you donāt have any repeated players, Iād just go with what you have started hand-adjusting, or just add the ROW()/100 if you havenāt started hand-adjustingā¦it wonāt matter unless a player actually repeats in the roster. Otherwise, there is no difference.
I havenāt started adjusting yet, but I will leave it alone. If it goes over 100 players, they will need to get another āvolunteerā. Again, thanks for your time and great solution!
To contributors:
I removed the base tag since this is obviously Calc only.
Happy to be corrected.
