Abstract
In general you will use named ranges in Excel to avoid hard coded references to input cells or to output cells. If you assign names to important cells, the references to them will be automatically updated in case of insertions or deletions of rows or columns.
Here we like to use named ranges in Excel to efficiently implement a simple solution with nested worksheet formulas which do not need future amendments:
We want to organize a simple round tobin tournament of an (almost) arbitrary number of players, where each player will play exactly one game against each other player. For ease of reference we define that player 1 will play with White in round 1 (i.e. has a home game).
The simple nested formulas we can implement with named ranges. All necessary parameters are the column and row positions relative to the anchor point in the top left corner of the pairings table.
Please note: A named range is evaluated whenever a formula that refers to it is evaluated. This means it will be evaluated as often as it appears in different formulas which are being evaluated. Unreferenced named ranges will not be evaluated, not even by a full recalculation (CTRL + ALT + F9).
Developing a Formula Solution for a Round Robin Tournament
After defining the kind of tournament we just need one parameter for this round robin tournament: the number of players.
We like to present all games to play in a simple table. Example:
| Table 1 | Table 2 | Table 3 | |
|---|---|---|---|
| Round 1 | 1 - 6 | 5 - 2 | 3 - 4 |
| Round 2 | 5 - 1 | 4 - 6 | 2 - 3 |
| Round 3 | 1 - 4 | 3 - 5 | 6 - 2 |
| Round 4 | 3 - 1 | 2 - 4 | 5 - 6 |
| Round 5 | 1 - 2 | 6 - 3 | 4 - 5 |
The pairings per round are shown in each row, and at which table a game needs to take place you will see in each column. The player which is named first has White (a home game), the second one plays with Black (an away game).
The tab name which contains our input (player number) and outputs (the pairings) is Input_and_Output.
The cell containing the number of players we will call Player_Count. It has the value =Input_and_Output!$B$1.
To be able to move the pairings table easily we define an anchor with the name Table_Top_Left (with the value =Input_and_Output!$A$10). All further names we define in relation to this anchor to avoid absolute references.
The current table (column) we call This_Table with the value =COLUMN() - COLUMN(Table_Top_Left). The current round (row) we call This_Round with the value =ROW() - ROW(Table_Top_Left).
The column headers (Table 1, Table 2, …) we then can easily name Table_Title with the value =IF(ISEVEN(Player_Count), IF(This_Table <= Player_Count / 2, “Table " & This_Table, “"), IF(This_Table = 1, “Frei”, IF(This_Table <= (Player_Count + 1) / 2, “Table " & This_Table - 1, “"))) . The row headers (Round 1, Round 2, …) will get the name Round_Title with the value =IF(This_Round <= (Player_Count - IF(ISODD(Player_Count), 0, 1)); “Round " & This_Round, “").
Finally we define the current pairings with the general name This_Game and with the value =IF(AND(This_Round <= (Player_Count - IF(ISODD(Player_Count), 0, 1)), This_Table <= (Player_Count + 1) / 2), IF(ISEVEN(Player_Count); Even_This_Game, Odd_This_Game), “") .
Note: The missing names Even_This_Game and Odd_This_Game we explain further down in separate chapters for even resp. odd numbers of players.
Now it is easy to create the pairings table: In row one, column two we enter Table_Title. In column one, row two we enter Round_Title. The first pairing in the second row and second column we name This_Game. We copy the last column to the right and the bottom row down until we get empty cells as an output.
Example for an Even Number of Players
To create pairings for an even number of players we do: We see in the sample table for 6 players shown above that player 1 stays at table 1 and plays against 6, 5, 4, … with alternating colours, starting with White. In round 1 the first player plays against the last, the second against the last but one, etc., with alternating colours. In the following rounds at tables greater 1 the players will be substituted with the player with the next lower player number (the formula shows the summand - This_Round). Exception: Player 2 will be folowed by the last player - in this case 6.
So the following names are clear now:
| Name | Refers to |
|---|---|
| Even_Equal_One_PlayerA | =1 |
| Even_Equal_One_PlayerB | =(1 + Player_Count - This_Round) |
| Even_Greater_One_PlayerA | =(2 + MOD(Player_Count - This_Table - This_Round, Player_Count - 1)) |
| Even_Greater_One_PlayerB | =(2 + MOD(This_Table - This_Round - 1, Player_Count - 1)) |
| Even_This_Game | =IF(This_Table = 1, IF(ISODD(This_Round), Even_Equal_One_PlayerA & " - " & Even_Equal_One_PlayerB, Even_Equal_One_PlayerB & " - " & Even_Equal_One_PlayerA), IF(ISEVEN(This_Table), Even_Greater_One_PlayerA & " - " & Even_Greater_One_PlayerB, Even_Greater_One_PlayerB & " - " & Even_Greater_One_PlayerA)) |
Example for an Odd Number of Players
| Free | Table 1 | Table 2 | Table 3 | |
|---|---|---|---|---|
| Round 1 | 7 pauses | 1 - 6 | 5 - 2 | 3 - 4 |
| Round 2 | 6 pauses | 7 - 5 | 4 - 1 | 2 - 3 |
| Round 3 | 5 pauses | 6 - 4 | 3 - 7 | 1 - 2 |
| Round 4 | 4 pauses | 5 - 3 | 2 - 6 | 7 - 1 |
| Round 5 | 3 pauses | 4 - 2 | 1 - 5 | 6 - 7 |
| Round 6 | 2 pauses | 3 - 1 | 7 - 4 | 5 - 6 |
| Round 7 | 1 pauses | 2 - 7 | 6 - 3 | 4 - 5 |
To create pairings for an odd number of players we do: In round 1 the last player pauses, in the second round the last but one does not play, etc. In round 1 player 1 plays against the last but one player, the second players starts against the antepenultimate player, etc., with alternating colours. In the following rounds at tables greater 1 each player will be substituted by the player with the next lower player number (the formula shows the summand - This_Round again), and player 1 will be substituted by the last one.
Then the following names are:
| Name | Refers to |
|---|---|
| Odd_Equal_One_PlayerA | =(Player_Count - This_Round + 1) |
| Odd_Greater_One_PlayerA | =(1 + MOD(This_Table - This_Round - 1, Player_Count)) |
| Odd_Greater_One_PlayerB | =(1 + MOD(Player_Count - This_Table - This_Round + 1, Player_Count)) |
| Odd_This_Game | =IF(This_Table = 1, Odd_Equal_One_PlayerA & " pauses”, IF(ISEVEN(This_Table), Odd_Greater_One_PlayerA & " - " & Odd_Greater_One_PlayerB, Odd_Greater_One_PlayerB & " - " & Odd_Greater_One_PlayerA)) |
A small quiz question: Why does the name Odd_Equal_One_PlayerB does not exist?
Answer: Because the formulas treat the pausing player as table 1, and in case of an odd number of players the table numbers are artificially reduced by 1 - please refer to the formula Table_Title.
Another small quiz question: How can you extend this solution to calculate the pairings for several tournaments in one Excel file?
Answer: Create all named ranges as local names in the current tab. Then copy this tab as often as necessary. Unfortunately you cannot just define the named ranges Player_Count and Table_Top_Left locally. All named ranges need to exist as often as the number of tournaments you want to calculate.
Appendix – Solution with Excel Worksheet Functions
An interesting fact: You can use this approach for (almost) any number of players. Just copy the rows down as far as necessary and the columns to the right until you see empty cells.
These formulas even work for pathological cases of 0 players, 1 player, and 2 players.
Download
Please read my Disclaimer.
sbRoundRobin.xlsx [20 KB Excel file, open and use at your own risk]
Note: A more general solution for round robin tournaments you can find under sbRoundRobin. You can choose the colour of the first game for player 1 and an additional output matrix will be created.