League Fixtures

[DubboPete]

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

 

[Stefi]

Earlier there was a similar thread, maybe you can make use of my answer given
to that post. I copy here the thread:


Do you mean something like this:
A B C D E F
week pair1 pair2 pair3 pair4 pair5
1 t1-t2 t3-t4 t5-t6 t7-t8 t9-t10
2 t1-t3 t3-t5 t5-t7 t7-t9 t9-t1
3 t1-t4 t3-t6 t5-t8 t7-t10 t9-t2
4 t1-t5 t3-t7 t5-t9 t7-t1 t9-t3
5 t1-t6 t3-t8 t5-t10 t7-t2 t9-t4
6 t1-t7 t3-t9 t5-t1 t7-t3 t9-t5
7 t1-t8 t3-t10 t5-t2 t7-t4 t9-t6
8 t1-t9 t3-t1 t5-t3 t7-t5 t9-t7
9 t1-t10 t3-t2 t5-t4 t7-t6 t9-t8

The formula in B2:

="t"&INT(2*COLUMN()-1/2)-2&"-t"&IF(MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10)>0,MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10),10)

Fill it to the right and down!

t1, t2, etc. stands for team1, team2, etc.

Regards,
Stefi


„manchester united†ezt írta:

I need help trying to set a format for 10 teams playing each other in 10
weeks. eg...team1 vs team 2, team 3 vs team 4, team 5 vs team 6...etc...
Also for 12 teams playing against each other for 10 weeks.

Thanks for your help....

„DubboPete†ezt írta:

 

[Lars-Åke Aspelin]

On odd weeks all teams are paired, buy on even weeks only teams with
odd numbers are paired, and they are paired twice.

This can not be the expected result from the pairing.

Lars-Åke

 

[DubboPete]

On odd weeks all teams are paired, buy on even weeks only teams with
odd numbers are paired, and they are paired twice.

This can not be the expected result from the pairing.

Lars-Åke







- Show quoted text -

thanks Lars, Stefi I could not get your fromula to work at all...

 

[Stefi]

You are right, I didn't notice this error, trying to fix it.
Stefi


„Lars-Åke Aspelin†ezt írta:

 

[Lars-Åke Aspelin]

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/round-robin/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

 

[Lars-Åke Aspelin]

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/round-robin/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()*2>CEILING($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()*2>CEILING($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

 

[DubboPete]

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()*2>CEILING($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()*2>CEILING($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

 

[Lars-Åke Aspelin]

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

Could you please expand a bit on "didn't work".
Was it possible to enter the formula at all or did you get some error?
If formula was entered, what was the result you got?

Here is what I got when I used the formula with number of players = 8.

Alfa-Hotel Bravo-Golf Charlie-Foxtrot Delta-Echo
Bravo-Hotel Charlie-Alfa Delta-Golf Echo-Foxtrot
Charlie-Hotel Delta-Bravo Echo-Alfa Foxtrot-Golf
Delta-Hotel Echo-Charlie Foxtrot-Bravo Golf-Alfa
Echo-Hotel Foxtrot-Delta Golf-Charlie Alfa-Bravo
Foxtrot-Hotel Golf-Echo Alfa-Delta Bravo-Charlie
Golf-Hotel Alfa-Foxtrot Bravo-Echo Charlie-Delta

Do you get correct results if you test your named range with the
following formula?

=INDEX(Players,1)
=INDEX(Players,2)
etc

Lars-Åke