World Cup Office Pool
- 2'645 Downloads
- 23.2 Mb File Size
- June 5, 2010 Updated
- 0 Number of comments
World Cup Office Pool is a spreadsheet to play prediction or comparison game with many of your friends and relatives. This spreadsheet allow you to compare score prediction between you and your friends against actual results. It is designed with leaderboard worksheet that allow all players get points for their correct prediction and compete to become the best predictor.
Office Pool Scenario
There are several office pool scenario you can set and implement with this World Cup Office Pool spreadsheet. You can apply a Set and Forget Prediction game scenario or Score Prediction game scenario. You can read more detail explanation below. This spreadsheet should be your good companion during World Cup tournament event that will be held in Russia, from 14th June - 15th July 2018.
Set and Forget Prediction scenario
The first scenario is a Set and Forget Prediction scenario. It requires you to get prediction from all of your players once and see the Leaderboard standings after any matches are completed. It fits you who don't have much time to manage score prediction during the event.
Score Prediction Only Scenario
The second scenario is a Score Prediction Only scenario requires you to monitor and manage your players during the event. You might don't have much issues in group rounds since you can ask your players to submit all group matches scores. But, you might stand by in knock out rounds since your players may submit scores after knowing teams who will meet in the next knock out rounds.
You can set points for any correct/incorrect prediction for both scenarios. You can also activate bonus points by typing your own values or deactivate it by typing zero on related bonus point option.
All development are based on several feedback for the past spreadsheets used in World Cup 2010 and 2014 as well as Euro Cup 2012, and 2016 that have made me to add more features in this World Cup Office Pool. As a result, you can utilize this spreadsheet to suit your prediction game setup.
World Cup Office Pool Template Setup
See image below to get a rough figure about setup worksheet. You must set it before starting your World Cup office pool game.
Setting Up Group Stage Points
There are no parameters needs to be set except points in this group stages for both scenarios. You can modify points values to meet your policy. There are 4 points levels you can set as follows :
1. Three Different Points Level
This is the default setting. Any players who predict the match result and the score correctly will get maximum points. Any players who predict the match result correctly but not the score, will get points as follows :
- similar score difference
- incorrect score difference
You can see sample below to get a clear description on how it works.
You can change the score with your own scores, and below are some of point scenarios you can implement for your office pool game.
2. Two Different Points Level
Any players who predict the match result and the score correctly will get maximum points. Any players who predict the match result correctly but incorrect score results will get lower points as you can see in the sample below.
3. One Point Level
3.1 Correct Score Prediction Get Points
Any players who predict the match result and the score correctly will get maximum points. None of players will get points if their match and score prediction is incorrect. You can do that by putting values for the first point value and zeroing the second and third point values.
3.2 Correct Match Winner Prediction Get Points
Any players who predict match results will get similar points regardless of their score differences. You can do it by setting all points values to be the same as you can see in image below.
You can pick your scenario and set your own policy by setting up all available parameters in this spreadsheet.
Setting Knockout Stage Rule
In the Knock Out phase, you can continue the game by setting it based on your selected scenario.
If you prefer to have Set and Forget scenario, you can select "Player's Prediction Matches" as your knock out rule option. You can select "Tournament's Actual Results" if you prefer to implement Score Prediction Game scenario.
I put two samples on how these two scenarios works.
Set and Forget Scenario by Setting KO Stages to follow Player's Prediction Matches
You can watch video simulation for this scenario below:
You can compare earning points by observing both images above. Also, you can simulating it in your own office pool spreadsheet to find the most suitable one to your game.
There is a youtube video to see simulation on this scenario :
Then, you can set either to include penalty shoot out prediction in your game to get more points or not. You must keep in mind that scores in player's FT (Full Time) boxes will be compared with actual tournament FT box. Full Time is Normal Time + Extra Time. It means if you predict the result ended with one team won in Normal Time but the actual result produced the winner in Extra Time, then you will get points. It works the other way around as well.
Points will be given for correct prediction regardless the number of minutes needed to complete the match. You can predict draw for Full Time score to get maximum points if result of actual matches are draw.
How this penalty shoot out inclusion work
If you select this option, it will compare the PSO table between PSO table in player prediction board and PSO table in actual tournament result box. If you don't select it, it won't compare the result but you still need to fill the score to find the winner to advance to the next round.
You won't get points if you predict a team won in Full Time but that team won by Penalty Shoot Out. Remember, the formula only compare tournament FT to player FT tables and tournament PSO with tournament PSO tables.
What about if I want to get points for predicting winners?
- Use bonus point #15 for this purposes.
Similar with points table in group stage, you can continue to modify point values. Steps to modify it are similar with explanation in group stages.
There is one note you should consider, if you pick "Player's Prediction Matches" scenario, players will get points if their pairing bracket prediction are similar with actual pairing brackets. Image above also show you example on this condition.
This bonus points will be useful if you implement Set and Forget scenario since if knock out pairing matches are not the same with actual results, players still have opportunities to add points by predicting qualified teams, champion, 2nd place, 3rd place, best player and top scorer correctly. You may also zeroing them or some of them if you don't want to implement it in your game.
Here are the explanation of each bonus points:
- Group Winner and Runner Up correct prediction. You will get bonus points if your predicted group winners and runner ups are similar with actual group winners and runner ups.
- Group Winner correct prediction only. You will get bonus points if your predicted group winners are similar with actual group winners.
- Group Runner Up correct prediction only. You will get bonus points if your predicted group runner ups are similar with actual group runner ups.
- Qualified 2 teams (swapped position). You will get bonus points if your qualified teams are similar with actual results regardless of their position.
- Qualified 1 team. You will get bonus points if your one of qualified team is similar with actual results regardless of their position.
- 16 qualified teams. You will get bonus points if the total number of your qualified teams are similar with actual results regardless of their position.
- 12 - 15 qualified teams. You will get bonus points if the total number of your qualified teams fall within 12 - 15 teams.
- 8 - 11 qualified teams. You will get bonus points if the total number of your qualified teams fall within 8 - 11 teams.
- Correct Knock Out Pairing Matches (only applicable in Player's Prediction Matches scenario). You will get bonus points if your predicted knock out pairing brackets are similar with actual brackets.
- Champion correct prediction (only applicable in Player's Prediction Matches scenario). You will get bonus points if your predicted champion is similar with actual result even though they face different opponents on their journey to the final. It is not applicable in Tournament's Actual Result scenario because players will always get points if they predicted score correctly.
- 2nd Place correct prediction (only applicable in Player's Prediction Matches scenario). Similar with bonus item #10, except it is for 2nd place position.
- 3rd Place correct prediction (only applicable in Player's Prediction Matches scenario). Similar with bonus item #10, except it is for 3rd place position.
- Best Player correct prediction. You will get bonus points if your best player prediction is similar with actual result.
- Top Scorer correct prediction. Similar with bonus item #13, except it is for Top Scorer category
- Match Winner correct prediction. As you know, the excel formula will give you points by comparing Full Time boxes between FT boxes in Player Prediction table and Actual Result table. It means if you predict the match will end in Penalty Shoot Out, but in the actual result table the match is finished in Normal Time, then you got no points at all since the Full Time score boxes contains different score, even though the outcome of the winner is the same. This bonus point is designed to compensate that. If the winner is the same between your prediction and actual result, then you will get points regardless the scores filled in both score boxes.
You can update points above with your own points. Also, if you don't need that, you can type zero on those point cells.
After completing the setup, you can go to Player Scoreboard worksheet to type your player names.
You must fill unique player names. Duplicate player names will cause errors. You can add "1" and "2" at the end of player names to differentiate their names.
Cells where you can type your player names have white and grey colors (C column). In this worksheet you can see summary of your player's prediction scores and points. You will see your player's names at the top of each player boxes in All Players Board worksheet. In paid spreadsheet, you can type until 100 players to join your prediction game.
To switch between scores prediction and awarded points, you can go to cell M3, cell with "Switch to Point View" text. Select "Switch to Score Prediction View" to view the scores.
Entry Player's Prediction Scores
You will receive one separate excel spreadsheet that you can distribute to your players to collect their score prediction. You can copy their scores and put it in All Players Board worksheet.
Incorrect score values inputted in player score boxes will yield error values in formula calculation.
In the past, I received complain about this type of errors where their standings have some errors. And after I analyze their sheets, I found that they type incorrect values in player score boxes. Common ones are incorrectly type "o" for zero and type "l" for one.
I have put a color marker in these boxes so you can spot it quickly if you type it incorrectly. Cell with those text values will turn into red which require you to put correct score format.
You can fill or modify cells with white colors only. Other cells are having formulas, including cells with colors only where it could have dummy values for calculation purposes. I suggest you not to add rows or columns since it could ruin team standing calculation and your player leaderboard rankings.
In knock out stages, Penalty Shoot Out (PSO) boxes will be shown automatically if you type equal score in Full Time score boxes.
No language automatic translation or timezone automatic conversion in this spreadsheet. But, you can modify team names from Team Setup worksheet by replacing team names in Country Name table (D column). To replace match time, you can manually retype it in Tournament's Actual Results table in E column in All Players Board worksheet.
Entry Player's Prediction Best Player an Top Scorer
In this spreadsheet, there are new options to predict best player and top scorer of tournament. Go to the bottom of each table to type the prediction. But, before that, you need to define the player in Best Player n Top Scorer worksheet.
There are 101 lines in this Best Player and Top Scorer tables. I allocate 100 lines if you have 100 different soccer star names from 100 players and 1 line if actual best player and top scorer is different from those 100 names. You must type it to avoid incorrect point calculation due to incorrect name comparison for similar player.
Get Player Prediction Scores
I provide a specific player prediction sheet in paid version. It will have outlook like image below. For you who run a free version, you can use the World Cup Schedule and Scoresheet version to collect your player's scores.
In this new World Cup Office Pool spreadsheet, you must type actual results in All Players Board directly. You can type the Full Time (Normal Time in Group Stages and Normal + Extra Time in Knock Out Stages) scores in G and H columns in Tournament's Actual Result table. Penalty Shoot Out score boxes will be shown automatically if Full Time scores are draw.
To match FIFA official tie-breaker, there is a fair play points table you can fill in Team Setup worksheet. If the result is still the same, you can manually retype group winners and runner ups in Tournament Actual Result table in this All Players Board worksheet.
In this year tournament, Japan has been placed above Senegal because of Fair Play points tie-breaker policy. To put fair play points correctly, you need to put 4 for Japan (without minus sign) and 6 for Senegal in respective Fair Play points column in Team Setup worksheet.
Track Players' Standing
Okay. Now, you already set them up and you can buy snacks and track movement of your players in Player Leaderboard worksheet every time after you updating tournament table with actual results. They will be ranked by :
- Total match and bonus points. It is applicable for all group and knock out round matches.
- Total correct prediction. It is applicable for all group and knock out round matches as well.
- Total match points only. Bonus points have less priorities than match points.
- Entry order in Player Scoreboard.
You can see screenshot of player leaderboard below.
You can watch youtube video below if you are not clear enough on how to use it with the explanation above on How to Use World Cup Office Pool.
- accommodate until 15 players
- no bonus points for champion, 2nd and 3rd picks as well as bonus points for best player, top scorer's and match winner picks.
- all worksheets and workbook are protected
- no google spreadsheet version
- no separate player prediction sheets
- accommodate up to 100 players
- all features are unprotected
- background player group standing calculation formulas are still protected
- separate player prediction sheet
- all worksheets are unprotected and all formulas and functions can be modified to suit your needs
We are sorry 🙁
Help us improve!
How we can improve this template?