Note 29-7-2014:
I used this Excel (version 2) for an office-pool, which some small changes. I updated this post to show the results of the office-pool, and also included the final Excel v2 in par. Downloads. I refer to this Excel with V2 and to the original Excel with V1.
For big sports events like the upcoming 2014 FIFA Worldcup Soccer in Brazil, a lot of people organize with friends or collegues at work a pool to bet who will win the cup. In this post I'll show you how you can do this with Excel, see fig.0 for the end-result (V2)
fig.0: Input office-pool V2
In the table below you can see what you (a gambler) must predict and how many points you can win for each prediction.
Table 1a: Point-system Pool in V1.
V2:
Table 1b: Point-system Pool in V2.
Points #2 are only calculated when there are more than 1 gamblers with max. points (4), so to tiebreak. The Excel-files with name '*test_Round*' are the result of little demo I made to show how this system calculates the points for 8 gamblers with 8 different predictions, ranging from nothing correct (so 0 points for finalist 1 and 2) to everything correct (finalist 1, 2 winner final and goals winner and loser final all correct). See fig.1 for the end-result of this simulation (row 48, 'Total Points').
V2:
In stead of the finalists (rank 1 and 2) you have to predict which teams end in the top 4 (rank 1-4), and tie-breaking is not the result of the final (goals) but the topscorer of the tournament and his country (so in case no one has predicted the correct topscorer, we look at the country of the top-scorer). In V2 the posibilty of a draw (gamblers with same amount of points) is smaller (that is: the posiblity there is only 1 winner (and not more than 1) is higher). As in V1, a binary point system is used, so that a gambler who predicted correctly e.g. team with rank 1 (16 points) wins from another gambler who predicted correctly the teams with rank 2,3 and 4, but not team with rank 1 (8+4+2=14 points).
In V2, phase 2 is to tie-break in case there are more than 1 gamblers with the same amount of points (no necesary the max. amount of points like in V1)
Fig.1: simulation WorldCup-pool with 8 gamblers, with result after final
Suppose after the Group-round (1), it appears nobody has predicted any of the teams who go to the next round (2) (1/8 finals) correctly (or in other words: all the teams which the gamblers predicted as finalists are eliminitad after round 1), you could say the game is over. But in stead, you could also give everybody a new possibility, so every gambler could give his new predictions (before the start of round 2).
How to use this Excel?
The idea is that it is used by 2 types of users:
- gamblers, who fill in their predictions
-administrator, who checks the gamblers input, and updates the Excel after every round with the results.
NB: WorldCup has 1 Group/Qualification-round and 4 final-rounds: 1/8 finals, 1/4 finals, 1/2 finals, final. (I exclude the match for place/rank 3-4, because for this pool it is not relevant).
How can they do this in the Excel:
* Gambler:
He must fill a '1' and '2' in his column (F7:F38 for gambler G1) on the row of the team he predicts will play the final and win (rank 1) or lose (rank2) and also his predicton of the result of the final (F40: goals winner, F41: goals loser)
* Admin:
- After gamblers completed their input:
Admin. Must check if on row 39 and 41 in the columns with the gambler-input (right to the word 'CHECK') there are no red-cells, only green cells (sum of forecast of teams with rank 1 and 2 must be 3).
- Before every round:
Admin must fill in cel C3 round-nr: 32 (Group-Qualifications), 16 (1/8 finals), 8 (1/4 finals), 4 (1/2 finals), 2 (final). This round-number should be copied also into column (C7:C38) for each team which is still in the competition and for each eliminated team he should fill a '0'. The total number of teams which are still in the competition you can see in C4, which must be the same as cell C3, round-nr. And if there is a difference (between C3 and C4), you can see it in cell E4 (red if error, green if OK).
Admin must fill in cel C3 round-nr: 32 (Group-Qualifications), 16 (1/8 finals), 8 (1/4 finals), 4 (1/2 finals), 2 (final). This round-number should be copied also into column (C7:C38) for each team which is still in the competition and for each eliminated team he should fill a '0'. The total number of teams which are still in the competition you can see in C4, which must be the same as cell C3, round-nr. And if there is a difference (between C3 and C4), you can see it in cell E4 (red if error, green if OK).
In the demo-files (name *test*), I copied the result of every round in a file which name ends with a number which indicates who many teams are in that round, so for the Group-round (with 32 teams), that is 32, for round 2 (1/8 finals) that is 16 ('best of 16) (see fig.2) etc.
- For final:
Admin. must fill a '1' and '2' in column D7:D38 on the row of the team which will play the final and win (rank 1) or lose (rank2) and also the result of the final (D40: goals winner, D41: goals loser).
Downloads
V1: 1 file (without demo)
Cells where user must input a value (column D for admin, columns F, G etc. for gambler G1, G2 etc) have these data-validations:
- F7: Forecast result, check: allowed values: 1, 2 (for rank 1 and 2, winner and loser of final)
- F39: total forecast result rank 1 and 2 must be 3, manual check (conditional formatting: red = error, green = OK)
- F41: Goals loser, check: Goals loser < Goals winner (F40) (value between 0 and =F40-1)
fig.2: competition in round 2 (1/8 finals, 'best of 16') in simulation V1
In the demo/simulation you can e.g. see:
- Gambler G1 forecasted MEX and CMR are the finalists (cells F7,F8) with rank 1 (winner) and 2 (loser) and with result MEX-CMR: 1-0 (cell F40, F41 in see fig.1)
- Gambler G2 has after round 1 has been played (so at the start of round 2 (1/8 finals) only 1 team which is still in the competition (cell G45 is orange): his forecast for rank 1 and 2 where ESP and FRA, and after round 1, FRA has been eliminitad (cell C23 has value 0) and ESP is in the next round (best of 16) (cell C13 has value 16), see fig.2.
- After the final has been played, gambler G1 had none of its 2 teams which he predicted as finalist correct (cell F45 is red), see fig.1
- After the final has been played, there were 4 gamblers with the maximum number of points (4): G5, G6, G7, G8, and G8 is the winner after the tiebreak, with 7 points, see cell M48 fig.1
The Excel has also a statistic (graphic): Total of betts for rank 1 (winner final) and rank 2 (loser final) per team, see fig.3.
fig.3: Statistics betts in simulation V1
Well, I hope it is clear how this Excel works and that you can use it for your World-Cup pool.
Note 29-7-2014: Results my office-pool:
In the office-pool, there were 12 participants. In fig.4 you can see their predictions for the 32 teams (before start worldcup) and in fig.5 the end-result for the top 4 teams. As you can see, there was only 1 gambler who predicted that the Netherlands would end in the top 4 and it wasn´t me.. but SER7, who in the end also won the pool.
After every phase of the worldcup, I updated column C with 0 o N (16, 8, 4,..) if a team did or did no pass to the next round, and a graphic showed how many teams of each gambler´s prediction were still in the competition (not eliminated), see fig. 6 for result at the end of the worldcup.
And the end-result (total points per gambler) was:
As you can see, there were 3 potential winners, so gamblers with the same amount of points (17 of 32) (all 3 had predicted Germany as winner and 1 other team in top 4 although not with the correct rank), also after the tie-break (none of them had the topscorer or his country correct), so we decided to add 1 extra rule: the winner is the one whose prediction of the topscorer is the one with the highest number of goals, and that made SER7 the final winner, which in my opinion he deserved because as I said before, he was the only one who predicted that the Netherlands would end in the top 4.
How to win next time the office-pool for the Worldcup?
*1: Know your ´classics´:
Football is a simple game. Twenty-two men chase a ball for 90 minutes and at the end, the Germans always win. - Gary Lineker
see:
http://www.brainyquote.com/quotes/quotes/g/garylineke422219.html
*2: Get a Windows smartphone:
It´s voice-assistent Cortana predicted 15 of the 16 teams in the elimination-phases (1/8 final until final) correctly, using Big Data techniques (e.g. using dat from prediction-markets), so it is the sucesor of octopus Paul of the 2010 Worldup. See:
http://www.maximumpc.com/microsofts_cortana_voice_assistant_correctly_predicts_world_cup_winner150
*3: Use a more scientific approach, e.g. use game-theory, see e.g. :
http://www.minyanville.com/special-features/sports-business/articles/march-madness-final-four-game-theory/2/27/2012/id/39600
Note 29-7-2014: Results my office-pool:
In the office-pool, there were 12 participants. In fig.4 you can see their predictions for the 32 teams (before start worldcup) and in fig.5 the end-result for the top 4 teams. As you can see, there was only 1 gambler who predicted that the Netherlands would end in the top 4 and it wasn´t me.. but SER7, who in the end also won the pool.
fig.4: predictions before start in V2
fig.5: predictions after end in V2
After every phase of the worldcup, I updated column C with 0 o N (16, 8, 4,..) if a team did or did no pass to the next round, and a graphic showed how many teams of each gambler´s prediction were still in the competition (not eliminated), see fig. 6 for result at the end of the worldcup.
fig.6: total teams still in competition at end worldcup (so who ended in top 4)
And the end-result (total points per gambler) was:
fig.7: total points per gambler
As you can see, there were 3 potential winners, so gamblers with the same amount of points (17 of 32) (all 3 had predicted Germany as winner and 1 other team in top 4 although not with the correct rank), also after the tie-break (none of them had the topscorer or his country correct), so we decided to add 1 extra rule: the winner is the one whose prediction of the topscorer is the one with the highest number of goals, and that made SER7 the final winner, which in my opinion he deserved because as I said before, he was the only one who predicted that the Netherlands would end in the top 4.
How to win next time the office-pool for the Worldcup?
*1: Know your ´classics´:
Football is a simple game. Twenty-two men chase a ball for 90 minutes and at the end, the Germans always win. - Gary Lineker
see:
http://www.brainyquote.com/quotes/quotes/g/garylineke422219.html
*2: Get a Windows smartphone:
It´s voice-assistent Cortana predicted 15 of the 16 teams in the elimination-phases (1/8 final until final) correctly, using Big Data techniques (e.g. using dat from prediction-markets), so it is the sucesor of octopus Paul of the 2010 Worldup. See:
http://www.maximumpc.com/microsofts_cortana_voice_assistant_correctly_predicts_world_cup_winner150
*3: Use a more scientific approach, e.g. use game-theory, see e.g. :
http://www.minyanville.com/special-features/sports-business/articles/march-madness-final-four-game-theory/2/27/2012/id/39600
Downloads
#Mirror 1:
V1: 1 file (without demo)
No comments:
Post a Comment