Description
Our Qatar World Cup 2022 Spreadsheet (built in Excel) has the ability to:
Update: See all our latest fun office pool sweepstake spreadsheets for sporting events
- Automatically update the actual scores when the tournament is on, direct to the spreadsheet (no need to capture them yourself!)
- NEW: Auto predict button for people who want to play in the Qatar sweepstakes but don’t want to have to work through all the games.
- World rankings included for countries you are not familiar with. This will assist you with the comparative rankings to make better predictions for the tournament
- Input your predictions and compare them to what actually happens
- See the likely group winners after adjusting your predictions for the actual results taking into account the group tiebreaker rules.
- Automatically addresses the tiebreaker rules for the 2022 world cup (points, net goals, goals for, and then head to head result- unlike the Euro rules).
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 or Excel for Mac's.
Screenshots and instructions for use
For the Organizer- Initial Setup
Watch the video clip or read the instructions below for how this works.
Once you purchase the spreadsheet, you will be able to download a zip file which contains 2 files as shown below. We recommend that you unzip the files and save them onto your computer.
The first file (‘2022-World-Cup-Soccer-Spreadsheet-v1-SweepstakesInput2003’) is what you need to send to the other players.
The second file (‘MASTER2022-Qatar-World-Cup-Spreadsheet-v1-Sweepstakes-upto-50_2003’) is for the organizer 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 system.
When you open up the ‘MASTER2022-Qatar-World-Cup-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:
Group Stages
- Getting the right result irrelevant of the score (i.e. win, loss, draw)
- Getting team 1’s correct number of goals (e.g. you predicted 1-1 and the score was 1-0)
- Getting team 2’s correct number of goals (e.g. you predicted 3-2 and the score was 2-2)
- Correctly guessing the net goals even if it is the wrong way (e.g. you predicted 2-0 i.e. a +2 margin and the result was 0-2 i.e. a -2 margin)- Depending on your feeling you may switch this off by making it zero.
- Correctly guessing the net goals and the correct way e.g. you choose 0-2 and the score is 2-4 you get points.
Knockout Stages
- Each correct team in the last 16 bracket stage of the tournament
- Each correct team in the Qtr Final bracket stage of the tournament
- Each correct team in the Semi Final bracket stage of the tournament
- Each Correct team in the 3rd place bracket stage of the tournament
- Correct 3rd Place
- Each Correct Team in the Final bracket stage of the tournament
- Correct Winner
Once you have decided this, you can inform the players of how it will work and send them the ‘2022-World-Cup-Soccer-Spreadsheet-v1-SweepstakesInput2003’ spreadsheet.
Instructions for the Players
Watch the video clip or read the instructions below. Note that there is a new feature which allows the players to use our ‘Auto Fill’ option which, based on the teams ranking, will fill in the scores and allow more people to play without having to have detailed knowledge of soccer.
When the players receive their spreadsheets they need to make their predictions. On the ‘Player’ sheet (shown below)
They must:
- (1) Enter their name in cell A1 (if the group has similarly named people use a unique name)
- (2) & (3) Enter their predictions of the scores in the blue cells (column G and I)
- (4) As they enter their scores the group standings in column O will be updated so that they can see who is going to progress to the next round (top 2 of each group).
- (5) New feature allowing players to let Excel, using the teams rankings, predict the match outcomes. More of this later.
- Note: per the competition rules, teams on the same number of points will be separated by (in order) comparing their net goals, goals scored, then head to head results (unlike the recent Euro’s) . The spreadsheet will take this into account.
- Note: Because of the complex rules to decide who progresses, we suggest players look at the resultant knockout matches and make sure they are happy with the teams progressing. If it is not what they want, they should change some of the scores.
- Once they have completed predicting the group stages, the knockout bracket stage of the tournament will auto populate and they must predict the scores in these matches.
- Note: at the knockout bracket stage of the tournament they only get points for who progresses. Predict a winner in each match (even just a 1-0) instead of a draw and penalties.
- The player must complete the knockout bracket stage of the tournament until they get to a winner.
- They must then save the file and send it back to the organizer.
Auto Predict Qatar 2022 World Cup Results
A new feature this year, for players you don’t want to have to fill in all the match results, is an auto predict button.
Key to using this is to enable the macros when the player opens the spreadsheet. As shown below it will look something like the below (1). If you want to use the buttons (2), the macros will need to be enabled.
The buttons available are:
- Random Full prediction- auto fills results in the group AND knockout phases and predicts a winner
- Random Predict Group Stages only- auto fills the group stages only. The player will then need to fill in the scores for the knockout phase all the way up to the winner.
The player can push the buttons as often as they want and each push will generate a random set of results, but based on the teams rankings.
Changing the chances of a team win with Auto Predict
As per the below image, on the ‘Rankings’ sheet in the Players spreadsheet we have included the teams and their points per the official rankings. This is what is used to forecast match results.
The bigger the difference in points between 2 teams (1 below), the more likely that the better side will win (but there is always a chance of a reverse result).
We have allowed you to change these odds by adding/ subtracting points for whatever reason (2).
For example, in red text (3) we have highlighted the teams that have previously won a World Cup, as these teams always seem to be in the mix. You may want to add some points for them.
Teams that seem to always come up short, like Belgium or Netherlands, you may want to deduct some points.
If you have a favorite team and want them to win it, add 1000 points to them and they will almost always win.
You can then go back to the ‘Player’ sheet and now when you use one of the Auto Predict buttons, it will use your new rankings.
We will update some views on what the rankings should be on a regular basis which you can copy to your spreadsheet, on 2022 World Cup Sweepstakes Predictions page.
Organizer- finalize the sweepstakes
Watch the video clip or read the instructions below
When the organizer receives back the spreadsheets, they need to capture the players predictions into the master file. This is very easy:
- Open the master file and the player predictions spreadsheets
- In the player predictions spreadsheet, copy from cell F9 to cell J92 (must include the knockout stages as well).
- Go to the masterfile, choose one of the open player sheets (e.g. player 1) and PASTE AS VALUES in cell T9 (it is very important you paste as values).
- In case it isn’t emphasized enough, PASTE AS VALUES.
- Do this for all the players.
- You can change the sheet names to reflect the player’s name but don’t delete any unused sheets.
Once you have done this we have included some stats which may be interesting even before the tournament starts!
On the Master spreadsheet there is a sheet called Masterfile. In column K is a summary of all the predictions made for each match. So in cell K10 it says 2-0-1 for the Senegal v Netherlands opening match. This means that of the 3 people playing, 2 expect a Senegal win, 1 a Netherlands win and 0 a draw.
There is also another sheet called Stats which shows a summary of the players predictions of who will make the various knockout rounds. So below the 3 players seem to expect the final winner to come out of Brazil, Argentine or Spain.
During the Qatar 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 black (1) and become Actual (2). The tables (3) will also automatically update.
Each player’s unique sheet on the Master spreadsheet will update as the real scores come in. You will see the matches that are finished (shaded cells (1)), the total sweepstake points scored (2) and a breakdown of how the points were earned (3).
On the ‘Overall Pnts’ sheet you can see a table ranking all the players.
On the Pnts Chart sheet you can see the players scores and how the leaderboard changes as the tournament progresses
Feedback from previous sweepstakes spreadsheet
Games have been played all over the world since 2010 for the World Cup Soccer, Euro Soccer and World Cup Rugby tournaments. Some feedback from a pub in New Zealand during the Rugby World Cup.
Well all finished and what a great result for us. [NEW ZEALAND WON 🙁 ]. The winner of our picks was April Barnett. We had 40 entries which was a good number.
Attached is a photo of her holding the cash in our rugby club rooms at Dunsandel. Thanks for all your help
Russell
FAQ
Q: Is there points awarded for actually predicting the scores of the knockout bracket stage of the tournament or just the teams playing?
- It is just for the teams playing. Getting the scores right does not add to the points
Q: Lastly, I put in some results just to see how it worked and I put a draw for some of the knockout bracket stage of the tournament. It seemed to randomly select one of the teams to go though. How does this work?
- For the knockout bracket stage of the tournament you need to have a winner.
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: Will you score points for correct team through to the bracket stage of the tournament (1/4, 1/2 and final) regardless of if the team advanced from a first or second place in the group stage? Or do you have to predict the correct placing in the group stage to score points?
- You get points for your chosen teams getting to the bracket stage of the tournament. How they got there is not important
Q: Can you confirm that for the knockout bracket stage of the tournament, we can enter decimal places and the master file will be able to recognize that because the actual scores are going to update themselves so will it be done in a the format to give points correctly etc
- Yes, use a decimal if you want to indicate that there was a penalty shootout. See the note below about decimals though
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