Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

How to create a game schedule

Latest post Thu, Sep 9 2010 11:11 PM by Ian_T. 1 replies.
  • Mon, Aug 31 2009 10:36 PM

    How to create a game schedule

    I am not an expert at Excel (casual use for a few years making simple reports) but I've taken on a task of creating a spreadsheet showing the game schedule for teams in our club.

    This spreadsheet is a tab in a workbook that keeps track of scores and stats. I'll describe the best I can.

    We have a different number of teams per month, ie month 1 might have 9 teams, month 2 might have 20 teams, etc. Team names are all listed in another tab so these formulas can reference another tab to find out how many teams are registered for this month.

    Each team only pays 5 games therefore not all teams play all other teams.

    So there will always be 5 columns to generate but not always the same number of rows.

    One catch is, if team 1 plays team 3 then the row for team 3 must have team 1 in it.

    Each team only plays another team once.

     

    This is an example of monthly schedule with 8 teams.

    Team               

    Team 1  plays   Team 2      Team 3      Team 4      Team 5      Team 6

    Team 2    "       Team 1      Team 4      Team 5      Team 7      Team 8

    Team 3    "       Team 4      Team 8      Team 1      Team 6      Team 7

    Team 4    "       Team 1      Team 3      Team 2      Team 6      Team 8

    Team 5    "       Team 1      Team 2      Team 8      Team 6      Team 7

    Team 6    "       Team 1      Team 3      Team 4      Team 5      Team 7

    Team 7    "       Team 2      Team 3      Team 5      Team 6      Team 8

    Team 8    "       Team 2      Team 3      Team 4      Team 5      Team 7

    As you can see it doesn't matter which teams play against each other as long as each team gets 5 games so the more random the better.

    Next time, about a month later when I publish the next schedule, there will be a different number of teams that will listed in another tab.

    I'd really appreciate it if there was a method of generating this schedule each month so I don't have figure it out each time.

    Thanks in advance for any responses. I've puzzled through this for a couple of weeks now.

    Filed under: , ,
    • Post Points: 21
  • Thu, Sep 9 2010 11:11 PM In reply to

    • Ian_T
    • Not Ranked
    • Joined on Fri, Sep 10 2010
    • Posts 1
    • Points 5

    Re: How to create a game schedule

    There is a general approach to this for odd and even numbers of teams. I have a spreadsheet that does this and calculates schedules in a couple of different ways. However it is open office and may be a little different from Excel (see http://templates.services.openoffice.org/en/node/4035). There is also a link to an online page that calculates schedules there.

    1. Setting up matches for an ODD number of teams

     

    The general approach (good for doing this manually) is to:

     

    a) Write out an initial set of matches in two columns – as 'A vs B', 'C vs D', etc with the last team at the bottom left. See 'Round 1' below.

    b) To generate Round 2, move each team one cell 'clockwise'. See 'Round 2' below.

    c) Continue this movement until the teams return to their original places.

     

    In this example case below (7 teams), you generate 7 rounds of '3 games plus a bye' per round. Each teams plays each of the others and also has 1 bye. The pattern can be repeated for additional games in the season. 

    Round 1

    Round 2

    A

    B

    C

    A

    C

    D

    E

    B

    E

    F

    G

    D

    G

     

    F

     

     

    2. Setting up matches for an EVEN number of teams (see tab 'Even number of teams')

     

    The general approach (good for doing this manually) is to:

     

    a) Write out the teams in two columns – as 'A vs B', 'C vs D', etc for 'n' teams. These will be the games for 'Round 1'.

    b) To generate Round 2, keep 1 team in place and move the other teams one cell 'clockwise'. See 'Round 2' below.

    c) Continue this movement until the teams return to their original places.

     

    In this example case (8 teams), you generate 7 rounds of 4 games per round. Each teams plays each of the others once. The pattern can be repeated for additional games in the season.

    Round 1

    Round 2

    A

    B

    A

    C

    C

    D

    E

    B

    E

    F

    G

    D

    G

    H

    H

    F

     

    Filed under: , ,
    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.