Page Index Toggle Pages: [1] 2  Send TopicPrint
Hot Topic (More than 10 Replies) Court Balanced Round Robin (Read 19204 times)
Richard A. DeVenezia
Forum Administrator
*****
Offline



Posts: 45
Location: Remsen, NY
Joined: Nov 8th, 2005
Gender: Male
Court Balanced Round Robin
Mar 12th, 2006 at 3:11pm
Print Post  
Ian Wakeling's spreadsheet will generate a balanced schedule for up to 100 items. Thanks Ian!

Download version 5.1 of Ians Excel file from the paper clip link  Grin
« Last Edit: Apr 3rd, 2014 at 10:18am by Ian Wakeling »  

The Administrator.
Back to top
WWW  
IP Logged
 
Richard A. DeVenezia
Forum Administrator
*****
Offline



Posts: 45
Location: Remsen, NY
Joined: Nov 8th, 2005
Gender: Male
Court Balanced Round Robin
Reply #1 - Mar 12th, 2006 at 3:11pm
Print Post  
Download Ian's Excel file from the paper clip link at the bottom of this message.

The spreadsheet will generate a balanced schedule for up to 100 items. Thanks Ian!  Smiley

For background material, read his discussion of "Court Balanced Round Robin".  Or, check out Richard's page which implements the balanced algorithm found in Ians Excel file.  Of visual interest -- compare the Grid format of Balanced to the other algorithms.

Screenshots:

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.




« Last Edit: Oct 16th, 2009 at 12:05pm by Richard A. DeVenezia »  

IanBalancedRoundRobin_v3_2.zip ( 26 KB | Downloads )

The Administrator.
Back to top
WWW  
IP Logged
 
Ian Wakeling
Board Moderator
*****
Offline



Posts: 1131
Location: King's Lynn, Norfolk, England
Joined: Dec 30th, 2005
Gender: Male
Re: Court Balanced Round Robin
Reply #2 - Dec 10th, 2006 at 10:57am
Print Post  

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.

Click on the paperclip to download the zipped Excel workbook.
  

IanBalancedRoundRobin_v3_5.zip ( 50 KB | Downloads )
Back to top
 
IP Logged
 
Ian Wakeling
Board Moderator
*****
Offline



Posts: 1131
Location: King's Lynn, Norfolk, England
Joined: Dec 30th, 2005
Gender: Male
Re: Court Balanced Round Robin
Reply #3 - Jan 8th, 2007 at 6:57pm
Print Post  
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.

Click on the paperclip to download the zipped Excel workbook.
  

IanBalancedRoundRobin_v4_3.zip (Attachment deleted)
Back to top
 
IP Logged
 
mkranitz
New Member
*
Offline



Posts: 1
Joined: Jan 29th, 2007
Re: Court Balanced Round Robin
Reply #4 - Jan 29th, 2007 at 1:27am
Print Post  
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.   Embarrassed

Can you help a guy out?  Cool

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.

Thanks in advance.

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

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

Michael
  
Back to top
 
IP Logged
 
Ian Wakeling
Board Moderator
*****
Offline



Posts: 1131
Location: King's Lynn, Norfolk, England
Joined: Dec 30th, 2005
Gender: Male
Re: Court Balanced Round Robin
Reply #5 - Jan 29th, 2007 at 8:56am
Print Post  
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.

  
Back to top
 
IP Logged
 
SGT_Buckeye
New Member
*
Offline



Posts: 1
Joined: Oct 31st, 2007
Re: Court Balanced Round Robin
Reply #6 - Oct 31st, 2007 at 4:06pm
Print Post  
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.
  
Back to top
 
IP Logged
 
Ian Wakeling
Board Moderator
*****
Offline



Posts: 1131
Location: King's Lynn, Norfolk, England
Joined: Dec 30th, 2005
Gender: Male
Re: Court Balanced Round Robin
Reply #7 - Nov 2nd, 2007 at 7:17am
Print Post  
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.
  
Back to top
 
IP Logged
 
MRMD
New Member
*
Offline



Posts: 1
Joined: Dec 22nd, 2007
Re: Court Balanced Round Robin
Reply #8 - Dec 22nd, 2007 at 9:19pm
Print Post  
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.
Thank you for your help in advance

MRMD
  
Back to top
 
IP Logged
 
Ian Wakeling
Board Moderator
*****
Offline



Posts: 1131
Location: King's Lynn, Norfolk, England
Joined: Dec 30th, 2005
Gender: Male
Re: Court Balanced Round Robin
Reply #9 - Feb 16th, 2011 at 5:49am
Print Post  
Off topic replies have been moved to This Thread
  
Back to top
 
IP Logged
 
Strike_Ump
New Member
*
Offline



Posts: 6
Joined: Mar 31st, 2011
Re: Court Balanced Round Robin
Reply #10 - Apr 1st, 2011 at 5:08pm
Print Post  
MRMD wrote on Dec 22nd, 2007 at 9:19pm:
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.
Thank you for your help in advance

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...
  

You're Out!
Back to top
 
IP Logged
 
Bob_R
New Member
*
Offline



Posts: 3
Joined: Jun 12th, 2012
Re: Court Balanced Round Robin
Reply #11 - Jun 13th, 2012 at 9:26am
Print Post  
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
  
Back to top
 
IP Logged
 
zzobe14
New Member
*
Offline



Posts: 1
Joined: Sep 12th, 2012
Re: Court Balanced Round Robin
Reply #12 - Sep 12th, 2012 at 10:45pm
Print Post  
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?
  
Back to top
 
IP Logged
 
Ian Wakeling
Board Moderator
*****
Offline



Posts: 1131
Location: King's Lynn, Norfolk, England
Joined: Dec 30th, 2005
Gender: Male
Re: Court Balanced Round Robin
Reply #13 - Sep 13th, 2012 at 12:47pm
Print Post  
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.
  
Back to top
 
IP Logged
 
Jono
New Member
*
Offline



Posts: 3
Joined: Dec 30th, 2013
Re: Court Balanced Round Robin
Reply #14 - Dec 30th, 2013 at 7:40pm
Print Post  
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.
  
Back to top
 
IP Logged
 
Page Index Toggle Pages: [1] 2 
Send TopicPrint