On Jul 28, 1:25*am, Lars-Åke Aspelin
wrote:
On Mon, 27 Jul 2009 14:29:58 GMT, Lars-Åke Aspelin





wrote:
On Mon, 27 Jul 2009 03:34:59 -0700 (PDT),DubboPete
wrote:

Hi all,

I have been racking my brains for the last three days trying to work a
logical sequence to setting up fixtures for a 19 week league
competition for 20 players.

Here's what I want to do:

20 players (numbered 1 thru 20 for ease of use) each play a game per
week, so there's 10 games per week. * Each player only plays an
opponent once every 19 weeks. * Each player obviously plays 19 games -
one player plays 19 opponents.

I just can't work out a formula to set the fixtures automatically
without any duplications. * Can anyone help or point me in the right
direction?

cheers in anticipation

DubboPete

Here is one example of pairing of players for the 19 weeks.

week no * * pair 1 *pair 2 *pair 3 *pair 4 *pair 5 *pair 6 *pair 7 *pair 8
pair 9 * * *pair 10
week 1: * * 1-20 * *2-19 * *3-18 * *4-17 * *5-16 * *6-15 * *7-14 * *8-13
9-12 * * * *10-11
week 2: * * 2-20 * *3-1 * * 4-19 * *5-18 * *6-17 * *7-16 * *8-15 * *9-14
10-13 * * * 11-12
week 3: * * 3-20 * *4-2 * * 5-1 * * 6-19 * *7-18 * *8-17 * *9-16 * *10-15
11-14 * * * 12-13
week 4: * * 4-20 * *5-3 * * 6-2 * * 7-1 * * 8-19 * *9-18 * *10-17 * 11-16
12-15 * * * 13-14
week 5: * * 5-20 * *6-4 * * 7-3 * * 8-2 * * 9-1 * * 10-19 * 11-18 * 12-17
13-16 * * * 14-15
week 6: * * 6-20 * *7-5 * * 8-4 * * 9-3 * * 10-2 * *11-1 * *12-19 * 13-18
14-17 * * * 15-16
week 7: * * 7-20 * *8-6 * * 9-5 * * 10-4 * *11-3 * *12-2 * *13-1 * *14-19
15-18 * * * 16-17
week 8: * * 8-20 * *9-7 * * 10-6 * *11-5 * *12-4 * *13-3 * *14-2 * *15-1
16-19 * * * 17-18
week 9: * * 9-20 * *10-8 * *11-7 * *12-6 * *13-5 * *14-4 * *15-3 * *16-2
17-1 * * * *18-19
week 10: * *10-20 * 11-9 * *12-8 * *13-7 * *14-6 * *15-5 * *16-4
17-3 * * * *18-2 * *19-1
week 11: * *11-20 * 12-10 * 13-9 * *14-8 * *15-7 * *16-6 * *17-5
18-4 * * * *19-3 * *1-2
week 12: * *12-20 * 13-11 * 14-10 * 15-9 * *16-8 * *17-7 * *18-6
19-5 * * * *1-4 * * 2-3
week 13: * *13-20 * 14-12 * 15-11 * 16-10 * 17-9 * *18-8 * *19-7
1-6 2-5 * * 3-4
week 14: * *14-20 * 15-13 * 16-12 * 17-11 * 18-10 * 19-9 * *1-8
2-7 3-6 * * 4-5
week 15: * *15-20 * 16-14 * 17-13 * 18-12 * 19-11 * 1-10 * *2-9
3-8 4-7 * * 5-6
week 16: * *16-20 * 17-15 * 18-14 * 19-13 * 1-12 * *2-11 * *3-10
4-9 5-8 * * 6-7
week 17: * *17-20 * 18-16 * 19-15 * 1-14 * *2-13 * *3-12 * *4-11
5-10 * * * *6-9 * * 7-8
week 18: * *18-20 * 19-17 * 1-16 * *2-15 * *3-14 * *4-13 * *5-12
6-11 * * * *7-10 * *8-9
week 19: * *19-20 * 1-18 * *2-17 * *3-16 * *4-15 * *5-14 * *6-13
7-12 * * * *8-11 * *9-10

Same thing in a more condensed format to (hopefully) avoid line length
problems

week 1: 1-20, 2-19, 3-18, 4-17, 5-16, 6-15, 7-14, 8-13, 9-12, 10-11
week 2: 2-20, 3-1, 4-19, 5-18, 6-17, 7-16, 8-15, 9-14, 10-13, 11-12
week 3: 3-20, 4-2, 5-1, 6-19, 7-18, 8-17, 9-16, 10-15, 11-14, 12-13
week 4: 4-20, 5-3, 6-2, 7-1, 8-19, 9-18, 10-17, 11-16, 12-15, 13-14
week 5: 5-20, 6-4, 7-3, 8-2, 9-1, 10-19, 11-18, 12-17, 13-16, 14-15
week 6: 6-20, 7-5, 8-4, 9-3, 10-2, 11-1, 12-19, 13-18, 14-17, 15-16
week 7: 7-20, 8-6, 9-5, 10-4, 11-3, 12-2, 13-1, 14-19, 15-18, 16-17
week 8: 8-20, 9-7, 10-6, 11-5, 12-4, 13-3, 14-2, 15-1, 16-19, 17-18
week 9: 9-20, 10-8, 11-7, 12-6, 13-5, 14-4, 15-3, 16-2, 17-1, 18-19
week 10: 10-20, 11-9, 12-8, 13-7, 14-6, 15-5, 16-4, 17-3, 18-2, 19-1
week 11: 11-20, 12-10, 13-9, 14-8, 15-7, 16-6, 17-5, 18-4, 19-3, 1-2
week 12: 12-20, 13-11, 14-10, 15-9, 16-8, 17-7, 18-6, 19-5, 1-4, 2-3
week 13: 13-20, 14-12, 15-11, 16-10, 17-9, 18-8, 19-7, 1-6, 2-5, 3-4
week 14: 14-20, 15-13, 16-12, 17-11, 18-10, 19-9, 1-8, 2-7, 3-6, 4-5
week 15: 15-20, 16-14, 17-13, 18-12, 19-11, 1-10, 2-9, 3-8, 4-7, 5-6
week 16: 16-20, 17-15, 18-14, 19-13, 1-12, 2-11, 3-10, 4-9, 5-8, 6-7
week 17: 17-20, 18-16, 19-15, 1-14, 2-13, 3-12, 4-11, 5-10, 6-9, 7-8
week 18: 18-20, 19-17, 1-16, 2-15, 3-14, 4-13, 5-12, 6-11, 7-10, 8-9
week 19: 19-20, 1-18, 2-17, 3-16, 4-15, 5-14, 6-13, 7-12, 8-11, 9-10

This is an example of a Round robin algorithm, see
http://www.devenezia.com/downloads/r...bin/index.html

The formula in cell B2 is this:

=1+MOD(ROW()+COLUMN()-4,19)&"-"&IF(COLUMN()=2,20,1+MOD(ROW()-COLUMN(),19))

Copy to the right until cell K2 and then down to row 20

Hope this helps / Lars-Åke

To make this a bit more general.
Put the following formula in cell B2 and copy it right and down as far
as needed. (The number of columns should be half of the maximum number
of players, and the number of rows should be equal to the maximum
number of players minus one).

=IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"",
1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1)&"-"&IF(COLUMN()=2,
CEILING($A$1,2),1+MOD(ROW()-COLUMN(),CEILING($A$1,2)-1)))

Then put the number of players in cell A1 and watch the pairings for
different number of players.

If the number of players are odd, the highest number should be
considered as a "ghost player" and matches with the "ghost" player
should be ignored.

If you want to replace the numbers with player names, try this
formula:

=IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"",
INDEX(Players,1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1))&"-"&
INDEX(Players,IF(COLUMN()=2,CEILING($A$1,2),1+MOD( ROW()-COLUMN(),CEILING($A*$1,2)-1))))

where "Players" is a named range with the names of all players.

Hope this helps / Lars-Åke- Hide quoted text -

- Show quoted text -

Hi Lars,

Both of the first two algorithms worked fantastic, especially where
the number of players is defined. It then allows for more than 20
players! However, algorithm 3 didn't work... with or without named
range for players...
cheers

Pete