Random Scheduling

[cgreen]

I have 14 teams playing for 26 weeks. The teams play each other only twice in the 26-week frame. Is there a way to do a random schedule per week, then freeze (or do manual calculation) so that the schedule does not change for the 26 weeks unless I command it to? There will be 7 games per week, and again, each team can only play each other twice in the 26-week frame. The number listed first is understood to be where the game will be played, so it has to be random as well.

example(teams are identified as 1 thru 14):

Week One
2 and 13
3 and 4
11 and 6
5 and 14
1 and 7
8 and 12
10 and 9

Week Two
14 and 1
2 and 6
3 and 8
5 and 4
7 and 10
12 and 9
11 and 13

 

[cgreen]

I have an error in my problem. There is 28 weeks of games (not 26). The weeks of games are double the teams. 14 teams / 28 weeks. Does this help make the solution easier?

 

[Mark O'Brien]

Are you sure? If there are 28 weeks of games? If everyone only plays everyone twice then that's 26 games. (teams can't play against themselves)

 

[Mark W.]

Bummer! 28 weeks spoils the symmetry. 14 teams playing each other twice during 26 weeks of play fit nicely into a scheme of 182 games -- 7 played each week.

182 games divided by 28 weeks is 6.5. Which suggests that 7 games must be played during 14 of the 28 weeks, and 6 games must be played in each of the remaining 14 weeks. So how should the schedule look? Do we alternate weeks of 6 and 7 games? Do we front-end load 14 consecutive weeks of 7 games each? What's up?

 

[cgreen]

Sorry for the confusion. 26 is right – I made the mistake of working on this before my coffee. I’m thinking to hard.

 

[Mark W.]

Okay, here's a valid, randomly generated pairing solution set...

Week,Home,Visitor
1,1,3
1,2,13
1,5,6
1,8,9
1,10,11
1,12,7
1,14,4
.
.
.

You can randomly assigned named teams to these team numbers using this model...

Book1
	A	B	C	D	E	F	G
1					Team	Name
2	0.291229301	11	TeamA		1	TeamN
3	0.37429577	7	TeamB		2	TeamF
4	0.170089056	13	TeamC		3	TeamJ
5	0.655942576	4	TeamD		4	TeamD
6	0.223363774	12	TeamE		5	TeamG
7	0.866950849	2	TeamF		6	TeamH
8	0.535616103	5	TeamG		7	TeamB
9	0.497077207	6	TeamH		8	TeamL
10	0.006578406	14	TeamI		9	TeamK
11	0.817850311	3	TeamJ		10	TeamM
12	0.319226606	9	TeamK		11	TeamA
13	0.335821273	8	TeamL		12	TeamE
14	0.31205176	10	TeamM		13	TeamC
15	0.871660426	1	TeamN		14	TeamI
16
Sheet1

This message was edited by Mark W. on 2002-08-29 14:29
This message was edited by Mark W. on 2002-08-29 16:47

 

[cgreen]

I'm having a hard time understanding. I am assuming you used the model to get the random selection.

Your example:
Week, Home, Visitor
1,1,3
1,2,13
1,5,6
1,8,9
1,10,11
1,12,7
1,14,4

How did you pick team 1 and team 3, team 2 and 13 and so on? I’m sorry for being such a dunce.

 

[Mark W.]

My spreadsheet model starts with 91 data rows representing every possible team pairing for 14 teams over 13 weeks...

Home,Visitor
1,2
1,3
1,4
1,5
1,6
1,7
1,8
1,9
1,10
1,11
1,12
1,13
1,14
2,3
2,4
2,5
2,6
2,7
2,8
2,9
2,10
2,11
2,12
2,13
2,14
3,4
3,5
3,6
3,7
3,8
3,9
3,10
3,11
3,12
3,13
3,14
4,5
4,6
4,7
4,8
4,9
4,10
4,11
4,12
4,13
4,14
5,6
5,7
5,8
5,9
5,10
5,11
5,12
5,13
5,14
6,7
6,8
6,9
6,10
6,11
6,12
6,13
6,14
7,8
7,9
7,10
7,11
7,12
7,13
7,14
8,9
8,10
8,11
8,12
8,13
8,14
9,10
9,11
9,12
9,13
9,14
10,11
10,12
10,13
10,14
11,12
11,13
11,14
12,13
12,14
13,14

This model is a Monte Carlo simulation. Column D [with the formula, =IF(SUM(OFFSET(G2,-E2,):OFFSET(G2,F2,)),RAND(),1)] is re-sorted by a macro consisting of nothing more than a Do...Until loop. When the constraints (in column G) for each weekly group is satisfied the formula in column D returns 1 and that week's pairings are sorted (intact) towards the bottom of the data list where they remain undisturbed. This sorting process is very much like shuffling a deck of cards, dealing them as 7 pairs, and removing 14-card sets from the deck when specific conditions are satisfied. A control section on the worksheet counts the number of errors for each week which serves has a progress indicator, and when the sum of remaining errors reaches 0 the macro's Do...Until loop is terminated.
This message was edited by Mark W. on 2002-08-29 16:50

 

[cgreen]

Using the ‘Brute Force Model’ I can get all the formula’s to work except column G. I’m getting a result of ‘0’ in all 182 cells. After this problem is solved, what two cells am I getting the information from showing the teams playing each other? I was thinking column E and F, but I do not have a ‘0’ team and the team numbers repeat themselves.

Column A – entered 1 (seven times), then 2 (seven times), etc.
Result – A2=1

Column B – entered 1 thur 14 (less the number in Column A)
Result – B2=2

Column C – {=IF(ROW()<9,1,OFFSET(C2,-7,)+1)}
Result – C2=1

Column D – {=RAND()}
Result – D2=0.451914344 (this changes)

Column E – {=MOD(ROW()-2,7)}
Result – E2=0

Column F – {=6-E2}
Result – F2=6

Column G – {=SUM((A2=OFFSET(A2,-E2,):OFFSET(A2,F2,))+(B2=OFFSET(A2,-E2,):OFFSET(A2,F2,))+(B2=OFFSET(B2,-E2,):OFFSET(B2,F2,)))-2}
Result – G2=0

 

[Mark W.]

There are a number of changes that I made to the model shown in my original posting. Here are the 1st 15 rows of the latest model which consists of 91 data rows (13 weeks)...

Schedule.xls
	A	B	C	D	E	F	G	H	I	J
1	Home	Visitor	Week	Rand	O1	O2	Error			419
2	1	2	1	0.73900988	0	6	6		1	42
3	1	3	1	0.93382801	1	5	6		2	30
4	1	4	1	0.44315461	2	4	6		3	42
5	1	5	1	0.34253986	3	3	6		4	18
6	1	6	1	0.64115672	4	2	6		5	42
7	1	7	1	0.6520071	5	1	6		6	30
8	1	8	1	0.11134018	6	0	6		7	18
9	1	9	2	0.14376424	0	6	5		8	30
10	1	10	2	0.49928319	1	5	5		9	42
11	1	11	2	0.02399661	2	4	5		10	42
12	1	12	2	0.91522262	3	3	5		11	33
13	1	13	2	0.69065599	4	2	5		12	24
14	1	14	2	0.14048432	5	1	5		13	26
15	2	3	2	0.05119771	6	0	0
Sheet1

I noticed from your last posting that you were entering all of the formulas as array formulas. Only the 'Error' formula entered in column G should be entered into individual cells as an array formula. You should populate columns A:B with the 91 rows of data listed in my previous posting where each team is paired with a succeeding team number (i.e., 1,2; 1,3;... 2,3; 2,4;... 12,13; 12,14; 13,14). The following macro is used to control the model...

Sub Macro1()
Do
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Loop Until Worksheets("Sheet1").Range("J1").Value = 0
End Sub

BTW, I reduced the size of the model to 13 weeks (as suggested by Mark O'Brien) for two reasons: 1) faster execution, 2) it provides for a 13-week period before each re-match. The earlier 26-week model allowed a re-match to occur the following week. I believe it makes for a more interesting competition if a re-match occurs later in a season.

With the 13-week model the 2nd half of the season is merely the 1st half with the Home/Visitor designation switched, and the 'Week' value incremented by 13.
This message was edited by Mark W. on 2002-09-03 09:27

 

[cgreen]

Ok Mark, sometimes I feel very stupid. I have been playing with your model and to be honest I have never entered an array formula and therefore I couldn’t get my model to work. After doing some research, I finally understand what you have been trying to tell me. I entered an array formula in column G and I understand how the macro works in column D, now my model does what your model does  thanks! Now comes my next step in understanding. How am I picking my group of 7 per week?