2021 Rugby Six Nations Sweepstakes Spreadsheet for up to 50 players
Related: Have a look at all the latest sports related spreadsheets
Our 2021 Rugby Six Nations Predictions Spreadsheet enables up to 50 participants to predict the round robin and final winners (overall, grand slam, triple crown and wooden spoon) and compete to see who can be closest in predicting the overall result and the scores of each team. The spreadsheets are ideal for anyone wanting to run a sweepstakes or Six Nations pool around the results of the tournament.
Overview on how to use it
- Download from this page
- Send the input sheet to all the people who want to play
- Ask them to complete it
- Send it back to you
- Copy and Paste the predictions into the master file
Now every time you open the file during the tournament it will automatically update the latest match scores and results and update the sweepstake scores
You can watch a brief video clip of how it works below (the 2021 instructions are the same as the last year 3 years, so the 2018 Six Nations Instructions video clips are still relevant)
- Separate templates for the organiser and participants
- Participants predict the round robin matches and then, based on the predicted group standings, the various categories will be predicted (overall, grand slam, triple crown and wooden spoon).
- Organiser updates the master template with all the predictions received from participants.
- The spreadsheet will automatically update the actual scores itself when the tournament is on, direct to the spreadsheet (no need to capture them yourself!)
- The default sweepstake scoring is as follows (you can change them if you wish as the organizer)
The spreadsheet is built in Microsoft Excel 2016 but will work in all versions with some loss of functionality if you are using Excel 2003.
Screenshots and Instructions for use
Below a video clip explaining how to use the 2021 Rugby Six Nations Sweepstakes Spreadsheet. As the method is the same, this video clip uses the 2018 version. There are also detailed instructions and screenshots below the video clip.
For the Organiser- Initial Setup
Once you purchase the spreadsheet, you will be able to download a zip file which contains 2 files as shown below.
The first file (1) (‘2021-Rugby-Six-Nations-Spreadsheet-v1-SweepstakesInput2003’) is what you need to send to the other players.
The second file (2) (‘2021-Rugby-Six-Nations-Spreadsheet-v1-Sweepstakes-upto-50_2003’) is for the organiser and is where all the predictions are saved, where the match scores are automatically updated, and the sweepstake points are compiled.
Before you send the files out, you will need to decide on the point scoring.
When you open up the ‘2021-Rugby-Six-Nations-Spreadsheet-v1-Sweepstakes-upto-50_2003’ spreadsheet, and go to the instructions sheet, you will be able to decide how the points work.
As noted above you can decide on the points scored by each player for predicting certain aspects of the matches and tournament. We have inserted some defaults. Your options are:
Rugby Six Nations Group Stages Sweepstakes scoring
- Getting the right result irrelevant of the score (i.e. win, loss, draw)- 3 points
- Getting team 1’s correct number of tries (e.g. you predicted 1-1 and the try count was 1-0)- 1 point
- Getting team 2’s correct number of tries (e.g. you predicted 3-2 and the try count was 2-2)- 1 point
- Correctly predicting a bonus point( or not) for team A tries (e.g. Team A scores 5 tries and you predicted 4 or more)- 2 points
- Correctly predicting a bonus point( or not) for team B tries (e.g. Team B scores 2 tries and you predicted 4 or less)- 2 points
- Correctly predicting whether or not the losing team will get a ‘within 7’ bonus point i.e.
- the prediction is a score of 20-25 (<7) and the actual score was 20-21 (<7) they get points – 2 points
- prediction of 20-32 (>7) and actual score was 20-40 (>7) they get points – 2 points
- prediction of 20-25 (<7) and the score was 20-40 (>7) – 0 points
- Winner- 15 points
- Your top 2 are correct but in any order- 6 points
- Grand Slam (either a team or none)- 5 points
- Triple Crown (either a team or none)- 4 points
- Wooden Spoon- 10 points
Instructions for the Players
When the players receive their spreadsheets they need to make their predictions. On the ‘Player’ sheet (shown below)
- Enter your name (if the group has similarly named people use a unique name) (1)
- Enter your predictions of the scores in the blue cells (column G and I for score and K and L for tries) (2)
- As you enter your scores the group standings in column O will be updated . (3)
When the organiser receives back the spreadsheets, they need to capture the players predictions into the masterfile (this is a one time thing). This is very easy:
- Open the masterfile spreadsheet and the player predictions spreadsheet
- In the player predictions spreadsheet, copy from cell F10 to cell L80.
- Go to the masterfile, choose one of the player sheets (e.g. player 1) and PASTE AS VALUES starting in cell T10.
- In case it isn’t emphasized enough, PASTE AS VALUES.
- Be careful to not paste over the F to L columns as these contains the formulas for the sweepstake scoring
- Do this for all the players.
- You can change the sheet names to reflect the player’s name.
Once you have done this you are all done!
During the 2021 Six nations tournament
During the tournament, all you have to do is open the master spreadsheet and let it connect to the internet. We will update the match scores periodically and these will be pulled through to the spreadsheet (to see how we do this, look at the video on Importing data from the web ).
As shown below, on the Masterfile sheet, as the real scores come through the cell colours will change to show that it is an actual score.
On the individual player’s sheets in the master file, as the real scores come in, you will see the matches that are finished (dotted cells), the total sweepstake points scored and a breakdown of how the points were earned.
On the ‘Overall Pnts’ sheet you can see a table ranking all the players.
There is also a chart sheet showing the player rankings as each match progresses (worm chart). As shown below with one from the men’s tournament in Russia in 2018, it is generally tight!
Keeping the files linked (week to week predicting)
We recommend you use the file as shown above. However, some people have asked about allowing the predictions to come in weekly. Below how you can make this a bit more efficient then copy and pasting it in each week.
Q: Can you use the spreadsheet on Google Docs
- It wasn’t built with Google Docs in mind, but some users have tried it and say it works. As an idea get all the information via Excel and then upload the masterfile to Google Docs and allow the players to see what is happening. Only limitation is that it won’t automatically update so you will need update the scores
Q: Hello. I’m having issues opening the spreadsheet on my Mac.
It gives me an error message and just opens as a “read-only” file.
How can I go about fixing this issue?
- Try and expand it out of the zip files and save it in a separate location
Q: Using an Apple Mac, the live scores updated the first time I ran it and then didn’t update?
- This seems to be a bug in the Apple version of Excel. It allows the initial update and then for some reason it caches this one update and refuses to go and get the latest version. We recommend that you rather get the version that doesn’t automatically update and rather enter the scores yourself (or use the spreadsheet on a Windows machine)