# Round Robin Tournament Scheduling

## Schedules - You must register to Post and Download => Requests => Topic started by: Richard A. DeVenezias on March 12, 2006, 10:11:52 AM

Title: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Richard A. DeVenezias on March 12, 2006, 10:11:52 AM
Ian Wakeling's spreadsheet will generate a balanced schedule for up to 100 items. Thanks Ian!

Title: Court Balanced Round Robin
Post by: Richard A. DeVenezias on March 12, 2006, 10:11:52 AM

The spreadsheet will generate a balanced schedule for up to 100 items. Thanks Ian!  [smiley=thumbup.gif]

For background material, read his discussion of "Court Balanced Round Robin".  Or, check out Richard's page (https://www.devenezia.com/downloads/round-robin/rounds.php?it=8&v=b1) which implements the balanced algorithm found in Ians Excel file.  Of visual interest -- compare the Grid format of Balanced to the other algorithms.

Screenshots:
(https://www.devenezia.com/images/ianrr-field.png)
Note: The sides are also balanced.  For example -- Team A plays on the left side of court 2 in round 1 and plays on the right side of court 2 round 6.

(https://www.devenezia.com/images/ianrr.png)

(https://www.devenezia.com/images/ianrr-inst.png)
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on December 10, 2006, 05:57:29 AM

Here is an update to the balanced round robin spreadsheet.

New in version 3.5

10 court balanced schedules, that could not be generated previously, have been added.  The mathematics for constructing these schedules is not easy to program, so complete schedules are read directly from the hidden worksheet called "library". There are library schedules for 10,16,22,28,34,40,46,52 and 64 players.

In Home/Away format the cells of schedule are linked using cell formulae to the row names in column A.  So editing column A will let you rewrite the schedule in terms of actual team names.  Note that any edits will be lost the next time a schedule is generated.

Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on January 08, 2007, 01:57:57 PM
Here is a further update.

New in version 4.3

The ability to randomize your schedule.  A different version of the schedule each time you press generate.

The team with a bye is shown when there is an odd number of teams in court/field format.

Title: Re: Court Balanced Round Robin
Post by: mkranitz on January 28, 2007, 08:27:42 PM
Ian, hello and hats off to a great little applet you built.

I am trying to build one for Coldfusion and the Web and was looking at your "Library" sheet.  I'm assuming that is encrypted??  If not, my mind will melt.   :-[

Can you help a guy out?  8-)

Specifically, I need to wrap my head around either a pure mathematical way of generating the tables OR doing it mechanically with arrays and loops.

Oh, this is a nice one: http://www.hometeamsonline.com/sportswebsites/?scheduler

I would like to build something that accepts variables like these.

Michael
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on January 29, 2007, 03:56:52 AM
Michael,

There's no encryption! The library sheet which you can view by clicking on Format/Sheet/Unhide is arranged both to save space and to be easily readable by the VBA macro code.  For the difficult round-robins (10, 16,22... teams), the full schedule is given in the library where each round is represented by a string contained in a woksheet cell.  The team numbers in a round can be read by subtracting 47 from the ASCII character code of each character in the string.  If you have the randomization feature turned off, this is all the macro does, writing the resulting rounds to the main worksheet.

If you don't want one of the difficult schedules, the balanced round robin is relatively easy to construct mathematically.  Details of an algorithm are given here.  At the bottom of that posting is a reference to a text book which presents some general mathematical constructions that cover all the balanced round robins.

The extra variables that you introduce on your website, the variable number of locations, days per week and time slots all add considerable complexity to the problem, and  it's unlikely that combinatorial mathematics is going to be of any help.  For this you will need an algorithm that searches for reasonable solutions to the problem.  The algorithm would need to be "intelligent" in some way since the total number of schedules that are possible rapidly becomes astronomical, so searching them all is not an option.

Hope that helps,

Ian.
Title: Re: Court Balanced Round Robin
Post by: SGT_Buckeye on October 31, 2007, 12:06:50 PM
This is a great source of information.  I was asked by a friend if I could create an Excel spreadsheet for her that does exactly what yours will do except that I need to list dates instead of rounds, the number of game sites will vary, and the number of teams will probably vary as well from season to season.  Can you point me in the right direction with coming up with my own spreadsheet that will do this.  I have already started reading up on the cyclic algorithm that you have links for.  Thank you.
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on November 02, 2007, 03:17:18 AM
If the number of sites and the number of teams are flexible then you will need to use a different approach, as my Excel spreadsheet only deals with the case when all teams, or all but one of the teams, plays in each round.  It's not really possible to generalise the algorithm you have seen to other situations and you will need to come up with an alternative.  Probably an algorithm that searches many different alternative schedules and picks the best one.

Hope that helps.
Title: Re: Court Balanced Round Robin
Post by: MRMD on December 22, 2007, 04:19:16 PM
Hello,
This is a very useful tool. Thank you for make it available. I work with kids and have tournaments all the time. When I came across this program it helped me organize it better. Thank you so much.
Is there a possibility in this program to have choice on number of courts? We cannot afford so many courts, but we usually have 8-12 teams, playing round robin with sometimes only 1 court or 2 courts. Very diffcicult to spread games out equally. If you can implement choice in numbers of courts we can use it would make our schedule even better.

MRMD
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on February 16, 2011, 12:49:52 AM
Off topic replies have been moved to This Thread
Title: Re: Court Balanced Round Robin
Post by: Strike_Ump on April 01, 2011, 01:08:30 PM
Quote
Hello,
This is a very useful tool. Thank you for make it available. I work with kids and have tournaments all the time. When I came across this program it helped me organize it better. Thank you so much.
Is there a possibility in this program to have choice on number of courts? We cannot afford so many courts, but we usually have 8-12 teams, playing round robin with sometimes only 1 court or 2 courts. Very diffcicult to spread games out equally. If you can implement choice in numbers of courts we can use it would make our schedule even better.

MRMD

You make some very good points. It would be incredible if we could have number of courts/fields and add multiple rounds as well. Then this would be flawless...
Title: Re: Court Balanced Round Robin
Post by: Bob_R on June 13, 2012, 05:26:39 AM
For those interested in scheduling tennis groups that play each week and
rotate with different partners & opponents, please check out the attached
PDF file that is a full season schedule for such a group.

Bob R
Title: Re: Court Balanced Round Robin
Post by: zzobe14 on September 12, 2012, 06:45:36 PM
I just became scheduler for my horseshoe league,I need help with a random schedule. We have 16 players and 3 pits and a 16 week schedule. So we play 3 games each week so eveyone has a by and we have four rounds to make it happen. Can you help me?
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on September 13, 2012, 08:47:52 AM
If you follow the schedules link above and dial up a whist schedule for 16 players, then I have rearranged that to provide a full round-robin in 5 weeks:

`(16  1 v  9 14)  ( 3 10 v 12 13)  ( 6 15 v  7 11)(16  6 v 14  4)  ( 8 15 v  2  3)  (11  5 v 12  1)(16 11 v  4  9)  (13  5 v  7  8)  ( 1 10 v  2  6)( 2  4 v  5  8)  ( 7  9 v 10 13)  (12 14 v 15  3)    (16  2 v 10 15)  ( 4 11 v 13 14)  ( 7  1 v  8 12)(16  7 v 15  5)  ( 9  1 v  3  4)  (12  6 v 13  2)(16 12 v  5 10)  (14  6 v  8  9)  ( 2 11 v  3  7)( 3  5 v  6  9)  ( 8 10 v 11 14)  (13 15 v  1  4)    (16  3 v 11  1)  ( 5 12 v 14 15)  ( 8  2 v  9 13)(16  8 v  1  6)  (10  2 v  4  5)  (13  7 v 14  3)(16 13 v  6 11)  (15  7 v  9 10)  ( 3 12 v  4  8)( 4  6 v  7 10)  ( 9 11 v 12 15)  (14  1 v  2  5)    (16  4 v 12  2)  ( 6 13 v 15  1)  ( 9  3 v 10 14)(16  9 v  2  7)  (11  3 v  5  6)  (14  8 v 15  4)(16 14 v  7 12)  ( 1  8 v 10 11)  ( 4 13 v  5  9)( 5  7 v  8 11)  (10 12 v 13  1)  (15  2 v  3  6)    (16  5 v 13  3)  ( 7 14 v  1  2)  (10  4 v 11 15)(16 10 v  3  8)  (12  4 v  6  7)  (15  9 v  1  5)(16 15 v  8 13)  ( 2  9 v 11 12)  ( 5 14 v  6 10)( 6  8 v  9 12)  (11 13 v 14  2)  ( 1  3 v  4  7)`

You can use this three times, each time making a different random assignment on the 16 players' names to the numbers 1 to 16.  This gives you 15 weeks of scheduling.  Perhaps have 'finals' in the last week were you match people up based on their scores in weeks 1 to 15.
Title: Re: Court Balanced Round Robin
Post by: Jono on December 30, 2013, 02:40:06 PM
Wow, what a great resource this site is.

I downloaded the excel file and it doesn't work for me, running Excel for Mac 2011. I get an error in the script, I think around a combo-box. I'm a programmers, but have never programmed excel. I'm going to fiddle around and try to fix it, or seek out a Windows machine.

Just thought I'd post and see if anyone has a fix.

In my league we have 8 teams, each will play each other 3 times, for 21 games. I think I can just use this 3 times, permuting the team order each time. I do need to balance home/away across the three sets of 7 games so, for instance, team A is home team at least once in it's three games against B.
Title: Re: Court Balanced Round Robin
Post by: Dingo on March 06, 2014, 01:37:03 AM
Is there a version of this file that will work on Office 2011 for Mac?
Title: Re: Court Balanced Round Robin
Post by: Kaper on March 15, 2014, 04:31:02 AM
Hi,
I was missing table showing games if Team/court is selected.
So added a short piece of code at the end of CommandButton1_Click Sub, just before final application.screenupdating = true.
it is described (as well as rationale shown) in the thread http://www.excelforum.com/excel-formulas-and-functions/995572-need-help-with-a-round-robin-tournament-schedule.html
Code: [Select]
`        ' code for listing added by Kaper, see thread:        ' http://www.excelforum.com/excel-formulas-and-functions/995572-need-help-with-a-round-robin-tournament-schedule.html          If Not OptionButton1.Value Then            Dim gameno As Integer            For j = 1 To ((nplayers - 1) \\ 2) * 2 + 1              gameno = 0              For i = 1 To ((nplayers - 1) \\ 2) * 2 + 1                If WorksheetFunction.CountIf(Cells(nplayers + j + 4, 1).Resize(1, 1 + gameno * 2), Cells(i + 2, 1)) = 0 Then                  gameno = gameno + 1                  If Cells(i + 2, j + 1).Interior.ColorIndex = 36 Then                    Cells(nplayers + j + 4, 2 * gameno) = Cells(i + 2, j + 1)                    Cells(nplayers + j + 4, 2 * gameno + 1) = Cells(i + 2, 1)                  Else                    Cells(nplayers + j + 4, 2 * gameno) = Cells(i + 2, 1)                    Cells(nplayers + j + 4, 2 * gameno + 1) = Cells(i + 2, j + 1)                  End If                  Cells(nplayers + j + 4, 2 * gameno).Resize(1, 2).Interior.ColorIndex = 34 + ((gameno + j) Mod 2) * 3                End If              Next i              Cells(nplayers + j + 4, 1) = Cells(2, j + 1)            Next j            For i = 1 To ((nplayers - 1) \\ 2) + 1              Cells(nplayers + 4, 2 * i) = "Game " & i            Next i          End If`For those who do not feel competent enough to play with Excel VBA I also attach modified file.
Hope it could be usefull for some users.

Best Regards,

Kaper
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on April 02, 2014, 02:54:45 PM
Here is version 5.1 of the generator.  There are two significant changes.

In Home/Away mode, use the checkbox 'Multi-Table layout for H/A' to produce a schedule with one small table for each round.  Thanks to Kaper(above), and BeachRock over at Excel Forum for suggesting this improvement.  The new layout is much more suitable for passing around to participants.

5.1 also implements a double round-robin option.  The same opponents are repeated, but the schedule is tweaked to retain best possible court balance in court/field mode, and tweaked in H/A mode to give both best possible alternation of venues and to ensure that when each game is repeated the team that previously played away, now plays at home.

Let me know if you find any problems.
Title: Re: Court Balanced Round Robin
Post by: gerrit098 on August 28, 2014, 04:37:59 PM
Hello, Thank you very much this sheet is very helpful.

I have 2 questions,

is it possible to set the number of fields to a maximum?

is it possible to a third player to every game (arbiter)

if this is possible, I would be very VERY grateful .

kinds regards
Gerrit
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on August 29, 2014, 03:38:07 AM
Unfortunately I have no plans to add options for referees/arbiters or to vary the maximum number of fields :'(

Note that sometimes it is possible to slice the round-robin.  For example with 12 players and 6 fields, you can easily make each round of 6 games in to two mini-rounds of 3 games each.
Title: Mixed doubles, rotating partners and opponents
Post by: Ian Wakeling on September 01, 2014, 03:10:02 PM
Off topic replies have been moved to This Thread
Title: Re: Court Balanced Round Robin
Post by: rtmoore on October 17, 2015, 04:43:30 PM
I am using version 5.1 of your very useful spreadsheet for preparing schedules for a curling league and assigning ice sheets (i.e. courts) to games. To do this I generate a schedule in both Home/Away and Court/Field formats and copy these to my spreadsheet. This has worked well until today when I was generating a schedule for a 6 team round-robin league. My assumption has been up to this point that the actual game schedule would remain the same as I switched format. I guess I have been lucky until now, as that assumption has worked out. However today, it didn't work.

What I have found is that when switching formats the rounds are shuffled. For example for a 6 game round-robin (not randomized) I get the following:

`Court/Field (CF) formatround   court 1    court 2    court 3  1      (3 6)      (1 2)      (4 5)  2      (6 2)      (5 3)      (1 4)  3      (4 3)      (2 5)      (6 1)  4      (2 4)      (3 1)      (5 6)  5      (1 5)      (6 4)      (2 3)Home Away (HA) formatteam     R1     R2     R3     R4     R5  1      2      3      4      5      6  2      1      4      6      3      5  3      6      1      5      2      4  4      5      2      1      6      3  5      4      6      3      1      2  6      3      5      2      4      1`

So, in CF format round 1 is the same as HA round 1. However, CF format round 2 is actually HA round 3. Other rounds are also shuffled.

So, my questions:
1. Am I missing an option that makes the round assignments consistent between the formats?
2. Am I the first person to need the 2 formats to be consistent?
3. Is there a work-around?
4. Could a future version of the scheduler make the 2 formats consistent?

Tom Moore
Title: Re: Court Balanced Round Robin
Post by: Ian Wakeling on October 18, 2015, 07:22:57 AM
Tom,

This is rarely asked for in my experience. Usually when HA format is used each team has their own home venue, so the assignment to courts or fields is no longer useful as this is something associated with a central location shared by all teams.

I think up to now you may have been lucky as it depends on the number of teams (n).  For odd values of n, then I think there will always be the correspondence between the rounds of the two formats. Perhaps you have only had odd n up to now?

If n is of the form 6i + 4, where i >= 1, that is to say n = 10,16,22,...  Then the court schedule will be read from the hidden 'library' worksheet, and will be a completely different schedule to the HA format.

Otherwise when n = 4,6,8,12,14,18, etc., then as you have found above there will be a correspondence, but the rounds will be in different orders.  In these cases you need to reorder the rounds of the court schedule as follows.  Round 1 remains the same, then play round 1 + d, where d = (n-2)/2, then play round 1 + 2d, then round 1 + 3d, etc.

For example if n = 6, then d = 2 and

1 = 1
1+d = 3
1+2d = 5
1+3d = 7
1+4d = 9

where this number exceeds the maximum number of rounds, (n-1) then keep subtracting (n-1) until it is back in range.  So to un-shuffle the rounds of the court schedule above, rearrange them in the order R1,R3,R5,R2,R4.  Similarly for n = 8, play the rounds in the order R1,R4,R7,R3,R6,R2,R5.

Ian.
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: lilshan23 on March 05, 2020, 09:20:33 AM
Hi Ian,

Hope you doing well.

I have been trying to edit your macro to suit  my weekly Family Badminton sessions.

We have a participants anywhere from 12-16 with 2 courts available at one time.

The idea is to have each player pair once with each player(Whist Algorithm) by the end of the session with ref the link below.

Shan
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Ian Wakeling on March 05, 2020, 10:48:39 AM
The Excel macros only deal with singles play, so you will find it very hard to achieve any balance for the pairs of players who oppose each other.  I think your best strategy is to mould the whist schedules into something that will work with 2 courts.  For example if you use the generator that you have linked to, then you need to cut each round down by removing one game, and then pair up the removed games into new rounds - for example with 12 players [2 8 v 3 6] & [9 5 v 10 1] can form a new round, as can the two games underneath and so on.  16 players is really easy, simply partition each round of 4 games into 2 rounds of 2 games.  There is a Whist schedule for 13 players (see half way down the Durango Bill page (http://www.durangobill.com/BridgeCyclicSolutions.html)) and you could probably do something similar.  14 or 15 players is problematic, there is no way to balance partners and opponents fairly.

Ian
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: keeweekid on June 26, 2020, 04:10:50 PM
I am trying to download the spreadsheet that was paperclipped but it downloads as a Word file.  Any thoughts on how to get this file correctly as I assume it is an Excel file (I have excel on my computer), so not sure what is going wrong.  Thanks!
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Ian Wakeling on June 27, 2020, 02:01:33 AM
That's weird.   I just tried myself and I get the zipped Excel file (xlsm) as intended.  If you would like to click on the envelope icon under my name and then send me an e-mail, then I can reply with the Excel file attached.
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: raggamuffin on October 11, 2020, 09:16:14 AM
Thanx very much!
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: mortarman007 on October 27, 2020, 12:02:46 PM
I need to schedule a 12-person Mixed Doubles Round Robin with only 3 Rounds with interchanging partners, and I don't want to duplicate partners nor have anyone play against the same person more than once. I have almost got it, but I am always left with one set of two women who oppose each other twice. Please help.
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Ed77 on November 07, 2021, 07:45:58 AM
I need a matrix that will show 6 to 15 players on 4 courts playing 10 games with no 2 players being paired. I realize their may be 2. players paired more than once in the latter games.
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Excel Eternal Student on November 29, 2021, 03:46:04 AM
My search and my weeks of breaking my head over randomising players into pool buckets and then creating a schedule for each pool and then creating a score sheet to be maintained by the coordinator on game days is now !

every where on the internet i go, i somehow land up on this page. I am trying to organise a community sports event with 6 individual games and 5 team games for 900 players divided across 18 categories in next two months. (its for our apartment complex)

Step by step, i am done through figuring out who wants to play what, what team compositions are and what teams will be playing what games, then using https://www.randomresult.com/tournament.php worked on creating randomised pools across those categories for each game.

now my excel sheet is ready to keep track of everything.

one thing which was missing was creating a schedule for the round robin stage of every game event. i attempted using formulae and then switched to macro. somehow it didnt work. i have resorted to manual creation of round robin schedule for each pool in each event game for each category of play. :(

But this sheet by Ian is a life saver. I am only stumped by the limitation of having an macbook so the activeX thing on v5.1 version excel file is causing excel to not load up on a Mac.

Any workaround on this?

I dont have a windows laptop to try this out. Can anyone help?

Ian?
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Ian Wakeling on November 30, 2021, 03:57:10 AM
You are correct that the Active-X controls are the reason the Excel file does not work on the Mac.  Unfortunately there is no way to work around this. As I said in my offline message, you are welcome to ask for specific outputs from the Excel file and I will send them to you.
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: GoldenOldie on April 29, 2022, 08:09:12 PM
Hi Ian,

A fantastic and very useful tool. Thank you for making it available.
If you're planning on doing another update, are you able to tweak it so that we're able to select
the number of courts, as the club that I play at only have 4. Many thanks you in anticipation.
Title: Re: Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook
Post by: Ian Wakeling on April 30, 2022, 10:11:30 AM
I am unlikely to do this as it would need a major change to the way it works.  I have other ways of finding a schedule for 4 courts so if you want to see some, then start a new thread in the the 'Requests' section and post your requirements.