I’m in a pool at the moment for Euro 2016. Every entrant predicts the results of the group stage games, and then of the knock-out stages, with increasing points for a correct call in each round.
Since there are only 8 games left (4x quarter-finals, 2x semis, 3rd place, and the final), I though it would be interesting to see who would win in the different scenarios. The result was the ‘Matrix of Triumph’ below, which shows who will win for every remaining outcome. It’s pretty easy to put together (<30 mins work) by the steps below – if you’re in a pool too, you should give it a try.
(For example, the top left box shows that if Portugal win QF1, Belgium win QF2, Italy win QF3, Iceland win QF4, Portugal win SF1, Italy win SF2, Belgium (losers of SF1) win the 3rd place play-off, and Portugal (winners of SF1) win the final, NG will win the pool.)
First, there are 8 games to play, each with 2 possible outcomes, so you can encode the full set of results as a string of 8 0s and 1s. One quick way to do this (there are many) is by creating a column of numbers starting from 0, then using the DEC2BIN function to convert them to binary (use 8 as the second argument to make sure the resulting string is 8 characters long rather than cutting off leading zeroes). The blue section in the screenshot below shows this. To cover all scenarios, you need 256 (2^8) rows.
A screenshot from the working sheet I used to put this together, with sections color-coded.
The orange section has some other details per game (the game number, to determine which position in the binary string decides the result of that game, and the number of points a correct call is worth), and the green section shows the results in each scenario. The formulas in the green section can all be something like this:
where $B12 is the cell with the binary code (column locked so it will copy across), C$9 is the cell with the game number (row locked so it will copy down), and C$7 and C$8 are the two teams playing. For the quarter finals I put the competing teams at the top of the columns, and the later matches will refer to the outcomes of the earlier matches in the row(the formula for 3rd place requires a little more thinking or some helper columns for the losers of the semis – left as an exercise for the reader…).
Next, the grey section has the details of the players – their name, starting points (from the earlier games), and their calls for each remaining game (if they each filled out an Excel template, you can copy them all into the same book and fill this out using INDIRECT to reference the same cell in everyone’s sheet) – and the yellow section works out what points each player will finish on in each scenario. The formula for the yellow section (again, many ways to do this) is:
where L$10 is the starting score, L$2:L$9 is the range of predictions (row-locked), $C12:$J12 is the outcomes in the scenario (column-locked), and $C$10:$J$10 is the range of points per game (row- and column-locked). This all needs to be entered as an array formula (i.e. press Ctrl + Shift + Enter to confirm).
Once that’s done, you can add a column at the end to work out the winner in each scenario (MAX to find the highest number of points in the row, MATCH to find where in the row it occurs, and INDEX to find the name of the corresponding player – bonus points if you do something clever as a tie-breaker), and you’re ready to generate the matrix.
To do that, lay out a table just like above, 16 x 16, repeatedly splitting in 2 horizontally and vertically for the outcomes of successive games. You can then add the code for each result above the columns / to the left of the rows (e.g. for QF1 the first 8 columns will say 0, corresponding to Portugal winning, and the last 8 will say 1, corresponding to Poland winning). Then, in each cell of the matrix, you can concatentate the 8 codes to form the string corresponding to that scenario, and INDEX / MATCH or VLOOKUP the corresponding winner. I just use the initials to make it fit in a small box.
Optionally, you can then color in each player differently (I went with brighter colors for those who have more ways to win), merge cells together where they have the same name, and voila – a matrix of triumph.
There are lots of other variants you can do with this. A couple I’ve done before:
- More scenarios: Excel can easily handle more scenarios, either in a matrix (I’ve made bigger ones than this for the earlier stages where you need to predict exact scores and there are a lot more possibilities, but at that point you need to just color-code the top few people and have a legend) or just in a table to see things like who needs Team X to win the next game, etc. If you start from the round of 16, there are 65 thousand possible scenarios.
- Probability weightings: the person with the most color on this chart is probably not the most likely to win, because their winning scenarios are not the most likely to happen (e.g. lots of people have picked Germany to win, so the result if Germany wins will depend on getting lots of other picks right; but if only one person picked Iceland to win, they’ll probably win in most scenarios where Iceland does). One way to adjust for this is with a probability-weighted matrix – that is, adjust the row and column heights according to the likelihood of each scenario, so that the total area a person has is proportional to their likelihood to win. Obviously, I would recommend using a macro to do this rather than sizing by hand…
And in case you’re wondering, no, my name does not appear in there, so I won’t be winning the pool!
Edit: It was later pointed out that our pool template had carried over the 3rd place play-off from a previous World Cup pool – the European championship doesn’t have a 3rd place play-off. I still can’t win…