A calc n00b trying to do something out of my reach.

[Evan]

Hi guys. I've been putting off a project that needs this for some time, because I couldn't figure out how it would work, while being as automatic as possible. So I brought it here.

Basically, what I'm trying to do is create a spreadsheet where numbers are input, while elsewhere, those numbers are then grouped together into different lists that are averaged to be as close as possible (given the original data).

It would start looking something like this:

Original data:
A 79
B 32
C 10
D 84

Then be automatically broken up into groups sort of like this.

Group 1:
C 10
D 84
Avg: 47

Group 2:
A 79
B 32
Avg: 55.5

[Villeroy]

See attachment.
And http://user.services.openoffice.org/en/ ... php?id=361 for alternatives.

[Evan]

Thank you for the quick reply.

I took a look at this, played with it for a while, and I don't think it was what I was looking for unfortunately.

I'll tell you what I'm trying to do. I'm looking to take a list of players and a number (their overall ability) and for the spreadsheet formulas to break them into as equal of teams as possible.

More like this on a large scale:
Original data:
John 76
Mike 61
Dave 73
Joe 58

And end up with this:
Team A
John 76
Joe 58
Avg: 67

Team B
Mike 61
Dave 73
Avg: 67

[keme]

One viable approach (if teams always have an even number of members):

• Make a sorted list (use the RANK() function to establish a sequence number if you don't want to alter the list sequence, but then you need to catch the situation of several people with equal ranking).
• Pick pairs that are at the same distance from the middle of the list. (So if 8 people are listed, the first pair would be number 4+5, the next number 3+6, etc.)

[keme]

A suggestion is attached.

Edit: Removed. See later posting...

There are a few things you need to consider before you modify the attached spreadsheet:

• If you want to have more or fewer participants, insert/delete rows inside the list. Don't just blank them out or add new rows at the bottom.
• Don't move the Teams list away from column A.
• If there are duplicate names, the calculation of average team strength is not reliable.

A useful exercise would be to locate the reasons for the above caveats...

[Evan]

Thank you keme, excellent. I'm going to mark this as resolved but, I'd like to ask you, how do you modify it. Add, subtract players and alter the size and number of teams?

[keme]

First, the original submission has a bug in the range used for "rank arbitration", making it fail sometimes. Corrected here (I hope...)

I made a few shortcuts to facilitate copying of formulas.
When selecting teams, the "rank displacement" depends on the team column, so in column A you will have the 1st above and below the middle of the ranking. In column B the 2nd, etc. That means if you want to extend the teams list, you just copy the 3 bottom rows of column H as far as you need to the right. If you want a teams list elsewhere, you probably need to alter the formulas. You can safely overwrite the team deviation box when copying, as it doesn't serve any purpose other than displaying the standard deviation in team strength.

If you insert rows below a range, you start outside the range. That means the range is not expanded in formulas. If you insert new rows inside the range (between row 2 and 17 in the original file), all formulas using that range will adjust to the new range. Right click in the row numbers to insert new rows.

As for larger teams, if they have even numbers you could just combine the pairs to make teams of 4,6,8..., or you could insert the pairs as source of a new (identical) selector sheet to have better weighted setups for teams of 4 (and with one step further you have teams of 8, etc.)

In any case, with more than two players there are more options than the pairing that we examine here, so you're not certain to get a "best match" anymore ("fairly good match" at best). For that you need something more advanced than the list approach.