SPREADSHEET RUGBY WORLD CUP GAME
Welcome to the Spreadsheet Rugby World Cup game! I often make games using Excel, as I find that it stretches my imagination, and makes me think of new ways to create better spreadsheets (read more about that here). This then helps me to make better spreadsheets for businesses of all shapes and sizes. I have often been asked why I don’t make the games available, so that is what I have decided to do here. If you’d like a copy of Spreadsheet Rugby World Cup, please download it using the available button. Please come back to this page from time to time, as we will put up any new versions of the game (you can see the version number under the order button and on your spreadsheet). There are also instructions and a demo video below. If we have any other competitions, promotions or social interactions about this game, we will promote them on our Facebook page, so please like that! How good are you at Spreadsheet Rugby, and can you lift the trophy? Please note that this game will only work with Excel, and only the most recent version of it. If you use other platforms, or older versions of Excel, it may well not work.
Macro-free – Free to play – Pick your team – Push for tries – Get the kicks over – Win the World Cup!
How to play the game
Click each section below to see the relevant instructions for each tab of the spreadsheet.
There are five aspects to the setup page, and they all need to be addresses before starting the first tournament. There may be more than deemed necessary, but this is a macro-free spreadsheet, so there may be steps usually done by macros. We have done this in purpose so that you can use this spreadsheet using Excel-Online.
Needs to be done
- Assign names to each of the players (who want to play games and control teams during the tournament). No duplicates.
- Assign a team to each player. Each team only assigned to one player. This will mean that each player will control the selected team.
- Give each of the teams a rating between 70 and 90. There are world rugby ratings available, and those scores are a good place to start (although you may need to alter them if any team scores over 90).
- Do the tournament draw. Select each space, and one (random) name will pop up, simply select that name. This draw will determine which team is in which group, and who plays in what fixture.
- Lastly, there is a dark grey patch of (hidden) data. Copy that whole dark grey section, and paste VALUES in the light grey section below. You won’t see the data, as it will still be hidden, but that will supply the spreadsheet with the random numbers needed. DO NOT use a normal paste option here, it will ruin the spreadsheet, use only paste VALUES (usually a 123 icon).
Hints & Suggestions
- When assigning team ratings, try to have the maximum gap (between the strongest and weakest teams) 15 points or less. Any more than that, and the games will be very one-sided. The higher the score, the better the team will be at attacking and defending. You can have all teams with lower scores, or all with higher scores. Higher scores mean that they should score more tries, but should also stop more tries.
- You will see when you make certain country selections, sometimes the cell turns to the country colour, and other times it turns into a blend of grey and the country colour. This is so that when you are clearing data to do a new tournament (once the previous one is complete and all honours have been assigned), it is easier to see which cells were grey, and therefor need to be cleared.
This is where the actual games happen. It is fairly straight forward, the ‘play’ area is in grey, and then next cell which needs to be clicked and selected is in yellow. Follow the yellow cells, and see what happens on the pitch as you do so.
Needs to be done
- Look at the section marked ‘Results to show without playing’, which is under the ‘In play figure selection’ section. Make your preferred choice. If you select ‘All Matches’, the final score will show for all matches as you select the match number. If you select ‘Computer Matches’, the final score will show for computer versus computer games as you select the match number, but the final score of matches involving human players will not show until they are played. If you select ‘No Matches’, then no scores will show automatically, all matches will need to be played. For played matches, read all points below, for results which are shown, read point 2, and then progress to point 6.
- Select the match number. If you’re not sure what it is, check the left hand side of the Fixtures & Results tab, the match numbers are displayed there. As you do so, all the relevant details will show. You can then either play the match, or progress to point 6 if you have selected to see the result without playing (point 1).
- If you wish to play the game, select each cell as it turns yellow (in the grey play area), and see the respective result on the field to the left. The object of the game is to select two numbers to equal between 98.5 and 100 (you can see what your total is on the centre of the scoreboard, above the field, and you can see how much you sill have to go). This sounds easier than it is, as each time, you will not get the exact number that you select. There is an element of ‘luck and chance’ which will change your selected figures. Select the first figure, see how much you progress, then select the second figure accordingly, and try to get a final figure of between 98.5 and 100. If you do so, you stand a chance of scoring a try. When it is the computer players turn to pick a figure, you click on the cell and there will be a figure, select the first figure, and then the second figure in the next column. That is the computer’s selection. You will have the choice of any number between 1 and 100 when it is your turn. You need to physically select for you and the computer opposition. Continue with this process until a try is scored, a penalty is given, or the game ends.
- If someone scores a try, it will say so in the section below the field, in the centre of the advertising board. That team will then have an opportunity to kick a conversion. In order to do this, simply select the yellow cell, and make your selection. The computer will always select 50, so please just make that selection as required. You (as a ‘Player’) can select between 40 and 60. You need to get between 45 and 55 (not including the two values themselves) in order to score the conversion (same for a penalty). There is also wind involved here, if the arrows face left to right (as you would see if you were taking the kick and facing the posts), then the wind will blow the ball right. The other way around will blow the ball left (the ‘home’ side always plays left to right, and the ‘away’ side always right to left, they do not switch at half time). If you want to kick left, pick a lower number, and pick a higher number to kick right. You need to do this to counteract the wind, although the strength of the find is not revealed. If the wind shows as being in the direction of your kick, then just select 50 and go straight down the middle, that means that there is no wind. Once the kick has been taken, it will show what happened, and adjust the score accordingly. Normal rugby points occur here (5 for a try, 2 for a conversion, and 3 for a penalty).
- If your first selection takes you over the 100 mark, you give away a penalty. The other team can then take a kick (as per the conversion in point 4). The computer players will give away the odd penalty, so don’t miss your chance. Once again, the relevant cell will turn yellow to indicate what needs to be done.
- When all plays have been completed, you will see the final score. Once this happens, go to the Fixtures & Results tab, and select the relevant cells (tries for the home team, score for the home team, score for the away team, and tries for the away team). Just click the cells, see the relevant value, and then select them. Once all four have been selected, you can clear the data on the Match Day tab (select the grey cells, right click, and select ‘Clear Contents’). This will then be ready for the next match, so you can start again at point 1.
Hints & Suggestions
- When selecting values for Play 1 and Play 2, you need to get as close to 100 as possible (but not over). The idea is to select the highest number possible, but if it is too high, you may give away a penalty. The rule of thumb is that you can’t go wrong by selecting the value of your team’s rating (on the setup tab), any higher than that, and you’re at risk. Watch your opposition and their selections (based on their ratings). They are set right at the border, so go a little safer than them and you’ll be OK. If you select too low a number to start, you’ll need to select a higher number second. That means that the ‘chance and luck’ aspect will make more of a difference, as it is based on a percentage. The higher the first value selection, the more chance of a try, but also of a penalty.
- Take advantage of conversions and penalties. The computer players go straight down the middle and ignore the wind, this is your opportunity to score some valuable points. Check out the wind, compensate with your selection, and take advantage. Take a look at point 4 above for details of how this works.
- You may be confused by the term ‘Play Success Rate’. Each team has 20 plays to make up the 8 minutes of a match (each play consists of Play 1 and Play 2). Each of those plays either end up in the try area (eligible to score a try), the don’t make it that far, or they go too far. Those that are eligible to score a try are deemed ‘successful’, and then need to compared to the defence to see whether or not they score a try. The ‘successful’ plays (those that made it into the try area, whether they scored a try or not) are then worked out as a percentage of the total number of plays. This is displayed at the end of a match, and is shown for both teams.
This page is fully automated, you don’t need to do anything, just click on the tab and view when you wish. This is the information about the current match.
This information is about the two teams scheduled to play against each other, and is based on the current match. As you select the match number on the Match Day tab, the relevant information will show up on this page. Once you have inputted the completed match data on the Fixtures & Results tab, the information on this sheet will update automatically.
Fixtures & Results
This is where you can see the fixtures and results, this drives the Groups & Knockout tab, and comes from the Match Day tab.
Needs to be done
- These fixtures will show when you do the draw (on the Setup tab). You need to select and complete the results after each match (before clearing the match data). Select the 4 yellow cells, and you will see the required figure, simply select them. Once done, you can clear the Match Day data.
- Repeat the above after each match. Once the tournament is complete, and you have selected the medals and trophy on the Honours page, you can clear the data on this page. Do this by selecting all the grey cells, right clicking, and then selecting ‘Clear Contents’.
- The semi final fixtures will change as you progress, depending on who is in position 1 and 2 of each group.
Groups & Knockout
There are no instructions for this tab, as it is 100% automated. You can see how the groups are progressing, and who is currently going through to the semi finals (based on position). The positions are based on the following criteria, in this order. Points, Points Difference, Normal Points, Bonus Points, Tries. If they are all the same, the team drawn first in the draw will go through. If there is a draw during a knockout match, the group criteria (earned by each team during the group stage) will be used again to determine who goes through.
Season Data Transfer
This is where the data from each completed season is stored. This data feeds various other tabs. This process should be taken after each season is complete, but before the Fixtures & Results tab is cleared of any data.
Needs to be done
- When each season completes (after the final match) simply copy the data in the dark grey section, and paste VALUES into the relevant season under the red headers. Once this is done, you can clear the data on the Fixtures & Results tab, and you can then start the process to start a new season. This data will feed various other tabs, and needs only be cleared after 10 seasons when full (if you want to use the same spreadsheet again and not start a new one).
This is where you can see the trophies and medals for up to 10 seasons. You can see which teams won the Spreadsheet Rugby World Cup each time, and which ‘Players’ won medals each time (for 1st, 2nd, or 3rd place). It is completely automated, and is based on the data in the Season Data Transfer tab.
Team & Player Report
This page is automated, except for where you can select a team. Simply select the team you wish to view, and it will show you that team’s finishing positions over the last (up to 10) seasons. If there are any human players associated with the chosen team, you will see their stats below. If a human player changes teams in the middle of the 10 recorded seasons, it will show their honours regardless of the team they were playing for at the time. The human player will only show up if you select the team that they are CURRENTLY playing for. You can view this at any stage, but it will only show data from completed seasons.
This sheet is completely automated, and based on data in the Season Data Transfer sheet. It shows (amongst other things) how many points have been scored and conceded by each of the teams. This updates as you update the Season Data Transfer page and the Fixtures & Results tab. You can view this at any stage.
Watch the demo video
Like Our Spreadsheet Rugby World Cup Facebook Page
Spreadsheet Solutions Links
Creator of the game
Do you know someone who would like Spreadsheet Rugby? If so, please share about this game. You can use these links to share about it on your favourite social media site. Thanks very much!