creating a sports schedule

[Lesa]

Hi All,
I am using works 6.0, and need a formula to make it do
the scheduling. I am trying to create a sports schedule
for 8 teams, they will play for 14 weeks and play each
other twice, a first half and a second half. If i can
have them play home one week and away the next that would
be great.
Thanks

 

[Harlan Grove]

I am using works 6.0, and need a formula to make it do
the scheduling. I am trying to create a sports schedule
for 8 teams, they will play for 14 weeks and play each
other twice, a first half and a second half. If i can
have them play home one week and away the next that would
be great.

First, it's impossible to have all teams play each other twice if they must
alternate home and away every week. All week 1 home teams could never face
any other week 1 home team because they'd always be home or away when the
other teams were home and away. You can't avoid some 2 or 3 game home/away
sequences.

Unlikely Works could do this like Excel would, so I'm going to approach this
in simple, nonspreadsheet terms.

First, ABCD fixed, EFGH rotate.

A B C D
H G F E

A B C D
G F E H

A B C D
F E H G

A B C D
E H G F


Then AB and EF fixed, DC and HG rotate

A B E F
D C H G

A B E F
C D G H


Finally, the remaining match-ups.

A C E G
B D F H


Let the upper rows be home teams and the lower rows away teams. Rewrite
these on single lines for each week in space-separated pairs with the first
letter the home team and the second letter the away team. Include a space
before the first pair and another space after the last pair.

AH BG CF DE
AG BF CE DH
AF BE CH DG
AE BH CG DF
AD BC EH FG
AC BD EG FH
AB CD EF GH

Copy this and paste it into B2:B8. Enter the following formula in B9.

=" "&MID(B2,2,1)&MID(B2,1,1)&" "&MID(B2,5,1)&MID(B2,4,1)&" "
&MID(B2,8,1)&MID(B2,7,1)&" "&MID(B2,11,1)&MID(B2,10,1)&" "

[For the Excel crowd: note that Works' string functions use *offset*
arguments for character position within strings.]

Copy B9 and paste into B10:B15. This gives the same pairings but in opposite
order within pairs. Copy B9:B15 and paste-special as values only. Next enter
the formula =RAND() in A2, copy A2 and paste into A3:A15. Then enter A in
C1, B in D1, C in E1, . . ., G in I1 and H in J1. Enter the following
formula in C2.

=IF(ISERR(FIND(" "&C$1,$B2,0))," Away","Home")

Copy C2 and paste into C2:J15. Now select A2:B15 and run Tools > Sort...,
sorting column A in ascending order, repeatedly until the home/away patterns
in C2:J15 are acceptable. All that's left is replacing letters with team
names.

 

[Lesa]

Well Harlan,
Thank you very much that worked perfectly once I got
everything typed in correctly. Are there changes I need
to make if there are only 4 teams or if there were 10.
Lesa

-----Original Message-----

I am using works 6.0, and need a formula to make it do
the scheduling. I am trying to create a sports schedule
for 8 teams, they will play for 14 weeks and play each
other twice, a first half and a second half. If i can
have them play home one week and away the next that would
be great.

First, it's impossible to have all teams play each other twice if they must
alternate home and away every week. All week 1 home teams could never face
any other week 1 home team because they'd always be home or away when the
other teams were home and away. You can't avoid some 2 or 3 game home/away
sequences.

Unlikely Works could do this like Excel would, so I'm going to approach this
in simple, nonspreadsheet terms.

First, ABCD fixed, EFGH rotate.

A B C D
H G F E

A B C D
G F E H

A B C D
F E H G

A B C D
E H G F


Then AB and EF fixed, DC and HG rotate

A B E F
D C H G

A B E F
C D G H


Finally, the remaining match-ups.

A C E G
B D F H


Let the upper rows be home teams and the lower rows away teams. Rewrite
these on single lines for each week in space-separated pairs with the first
letter the home team and the second letter the away team. Include a space
before the first pair and another space after the last pair.

AH BG CF DE
AG BF CE DH
AF BE CH DG
AE BH CG DF
AD BC EH FG
AC BD EG FH
AB CD EF GH

Copy this and paste it into B2:B8. Enter the following formula in B9.

=" "&MID(B2,2,1)&MID(B2,1,1)&" "&MID(B2,5,1)&MID(B2,4,1) &" "
&MID(B2,8,1)&MID(B2,7,1)&" "&MID(B2,11,1)&MID(B2,10,1) &" "

[For the Excel crowd: note that Works' string functions use *offset*
arguments for character position within strings.]

Copy B9 and paste into B10:B15. This gives the same pairings but in opposite
order within pairs. Copy B9:B15 and paste-special as values only. Next enter
the formula =RAND() in A2, copy A2 and paste into A3:A15. Then enter A in
C1, B in D1, C in E1, . . ., G in I1 and H in J1. Enter the following
formula in C2.

=IF(ISERR(FIND(" "&C$1,$B2,0))," Away","Home")

Copy C2 and paste into C2:J15. Now select A2:B15 and run Tools > Sort...,
sorting column A in ascending order, repeatedly until the home/away patterns
in C2:J15 are acceptable. All that's left is replacing letters with team
names.


.

 

[Harlan Grove]

. . . Are there changes I need
to make if there are only 4 teams or if there were 10.

In coming up with the first set of match-ups, yes. For 4 teams,

A B
D C

A B
C D

A C
B D

giving rewritten records of

AD BC
AC BD
AB CD

At this point, the approach would be identical to the 8 team case, but with
obvious changes in cell and range addresses due to fewer rows and columns needed
to store the match-ups for fewer teams.

For 10 teams, again the work is in coming up with the original match-ups. Simple
rotation schemes don't work unless the number of teams are a power of 2. So
other numbers of teams requires a bit of trial & error.