The World Cup is now in group stage. Each group has four teams. The top two teams advance to the knockout stage. This video explains the 7 tiebreaker steps, and uses Microsoft Excel to analyze the 729 permutations that can happen in any one group. How likely is it that the tiebreaker will need to be used?
Table of Contents
(0:00) World Cup Group Stage Scoring
(1:00) Tiebreaker rules
(1:50) Odds of a tiebreaker
(2:20) Excel Model for one group
(2:50) TEXTSPLIT function
(3:22) Excel Trace Dependents
(3:45) Scoring & Sorting
(4:10) Detecting a Tie
(4:41) Why 729 permutations
(4:53) 28% tiebreaker chance
(5:46) Six draws leads to 12 points
(6:04) 18 points total
(6:40) Which results have a tie?
(7:28) Excel behind the scenes
(8:00) 7 answers from 6 inputs
(9:19) Contemplated VBA
(9:39) Multiple input cells to Excel data table
(10:49) Generating 729 input cells
(12:44) Setting up the Data Table
(14:51) Paste results as values
Table of Contents
(0:00) World Cup Group Stage Scoring
(1:00) Tiebreaker rules
(1:50) Odds of a tiebreaker
(2:20) Excel Model for one group
(2:50) TEXTSPLIT function
(3:22) Excel Trace Dependents
(3:45) Scoring & Sorting
(4:10) Detecting a Tie
(4:41) Why 729 permutations
(4:53) 28% tiebreaker chance
(5:46) Six draws leads to 12 points
(6:04) 18 points total
(6:40) Which results have a tie?
(7:28) Excel behind the scenes
(8:00) 7 answers from 6 inputs
(9:19) Contemplated VBA
(9:39) Multiple input cells to Excel data table
(10:49) Generating 729 input cells
(12:44) Setting up the Data Table
(14:51) Paste results as values
Transcript of the video:
Well, it's the most popular sporting event in the world.
The World Cup. We're in the group stage right now. I want to do an analysis in Excel of the odds that any particular group are going to end up in a complicated tiebreaker.
All right, so the World Cup right now, there's 32 teams competing in four team groups. So, there's eight groups of four teams.
Each team gets to play the others.
That means that there are six matches in the group stage and each match has one of three possible outcomes.
Either the team along the top here wins, the team along the left side wins, or it's a draw.
You get three points for a win, just one point for a draw, potentially the most points that could be scored in a group would be you would win all three games, and that's nine.
The most points overall though, among all the teams in the group is 18.
If there's a tie, so the top team advances and then the second team advances to the next stage.
If there's a tie between second and third or even ties between first, second, and third, then there's a very complicated seven step tiebreaker that FIFA uses.
Goal differential, goal scored, head-to-head performance, goal differential just in matches between the tied teams, goal scored in matches between the tied teams.
And then, a fair play system where if you get a yellow card, it's a point.
A second yellow card, which leads to a red is three points, just directly a red card four points, a yellow followed by a red is five points.
Whoever has the fewest number of points in fair play would advance.
That's actually happened once in the 2018 World Cup.
And if that still doesn't solve it, then it's a random drawing.
So, my question was how many times are we likely to end up in this tiebreaker state?
And it's a lot in the eight groups.
It's likely that at least two will be two to three will be thrown into the tiebreaker. Here's how I figured that out.
And in this episode, we're going to talk a little bit about the football, the soccer first, and then come back and look at the awesome what if analysis that allowed me to build these pivot tables that we have.
So, I just built a simple little model here of one group.
We have the four teams and the other four teams across the top.
Of course, team one doesn't play itself, so there's an X there. So, there's really six games that are happening.
And just for a simple notation here, zeros of draw.
One is the team along the top wins.
Two, the side team wins, and I just have a little nice little character string here with the results of A, B, C, D, E, F.
They get broken out here using a TEXTSPLIT function, breaking it at the hyphen.
Thankfully that is out. We didn't have TEXTSPLIT at the last World Cup.
It would've been a lot harder back then.
And then, broke these six results into these six cells right here.
Of course, when team one plays team four, it says here that the side team wins.
That means that when team four plays team one, that the top team is going to win.
So, these six cells are calculations based on these six cells. You can see a little...
Let's see if I come here to formulas and trace dependence, when that answer is zero, one or two, it's going to cause the opposite answer basically to appear in this cell.
And we can see that each of these six go to a different place.
Once I managed to get all six results in. Then we have the score for team one.
In this case, they would have seven points because they had two wins and a draw.
So, three plus three plus one and so on.
And then right here, this section sorts the scores 7, 1, 4, 4 into high to low. Which teams were those?
So team one, team three, team four, team two, and then a little just shortening that.
So, team one had seven, team three had four, team four had four down here.
Was there a tie that's really simple? Are these two cells the same score?
Is there a tie between one and two?
I didn't build this table, but it might be interesting because the top team gets to play the second team in the next group.
So, that's important whether you're first or second, what was the max number of points, what's the points for the second person to advance total points in the group?
And I ended up figuring this out for all 729 permutations, how are there 729 permutations when we have six matches that could be one of three things.
So, we take the number three raised to the six power, we get the 729.
All right, so the answer, how many times is there going to be a tie between second and third out of those 729 possible outcomes?
It's surprising, 28.4% of them are going to end up with a tie between second and third.
So, with eight groups, multiply that 0.284 times eight in you're between two and three. So, it's likely that the tiebreakers will happen.
Once I had all of this data. There's lots of interesting things here.
I really like this pivot table down here, the score of the four teams in the group.
So it is possible, I'm always consulting with my friend Junior Grant, who's my World Cup consultant.
We were driving around, running errands yesterday in the car and I said, what's the lowest that could happen?
I mean, could it just be that every, all six games end up in a draw?
And so, it's a dead tie out of the 729 outcomes that could happen.
There's one chance of a 729 and then on the other end where one team wins all three, another team wins two, and then a third team wins one no draws at all.
You would end up with 24 out of 729 outcomes.
So, how many points can you get total of the six teams getting 18 points, that means there will be absolutely no draws.
It's pretty rare. It's only 8% of the 729.
And down here would mean that you had six draws, a total of 12 points.
That would only happen one out of 729 times pretty rare.
This table here, the yellow I added manually, that shows the places where there's a tie between second and third. And it's not just the number of yellows.
This yellow here 7, 4, 4, 1 occurs 36 times so much more frequently than the others.
So, as we're getting down here deeper into the group stage, we start to think about is it possible that we're going to end up with a tie.
And it seems pretty likely that your team might end up there. All right, so that's the short answer.
28.4% chance that we're going to be thrown into the tiebreakers on group stage.
So probably two or three of the groups seem to be likely for the Excel fans here.
I love the table that I used to build the 729 permutations.
It's pretty cool.
It goes back to the same basic concept we used when we tried to figure out if the asteroid bennu is going to hit the earth back in the Excel hash. It's a complicated bit.
So, if you're just a football or soccer fan, thanks for stopping by.
But for those of you who want to see the Excel behind this, I think it's pretty cool.
And hang on here.
All right, so what I have is a model that's given me, what is it?
One, two, it's given me seven different answers that I thought might be interesting for one group.
So, I have the four teams in the group playing the other teams, you enter the six values here and it will do all the calculations for you.
It answers the questions for you. Is there a tie?
What were the most points scored? What were the second most points scored?
Is there a tiebreaker between two and three? And then, just this little...
I started out with this result using this nomenclature over here, but I decided it wasn't that important really because I don't have the team names here.
And the fact that team one got seven, it would be just as interesting if it was team two had seven, team four had four. I don't really care about the team.
So, I ended up with this alternate result here that just shows the scores of the top team, the second team, the third team, and the fourth team.
And how many times are these scores going to repeat in the 729?
So, it came down to six input cells generating seven output cells. And I wanted to run that 729 times.
It was a crazy day yesterday. MrsExcel was here, junior was here.
We had a lot of deadlines to get done.
And in my head, I said, "I'm going to sneak back to this Excel spreadsheet and write some VBA".
And I'm glad I didn't write the VBA because I realized that if I could just get the six input cells down to a single input cell, then a data table under the what if tools would solve this for us.
And that's when I invented this input cell here where we put A, B, C, D, E, F.
Those six results, either a zero for a draw, one for the top team wins two for the side team wins made that be the input cell, added the TEXTSPLIT function in here to take that one input cell and change it into six input cells.
I initially thought about using a data table, but a data table can't take six input cells along the left-hand side.
That's just not the way that it works. It has to be a single input cell.
So, building this little thing here allowed me to use a data table.
All right, so you understand how this model works.
Usually, when I'm showing a data table, it's always the car loan payment, how to figure out the payments for various prices of cars in terms of loan. And it's a single formula.
I like this model better.
We have six input sales, a total of 41 formulas to calculate the results for just one of the 729 permutations.
So, there's a lot of math that's happening here.
Now, we're going to scroll over to this table here, A, B, C, D, E, F. So, these are the six matches in one group.
And starting right here in this cell, I just have 0, 1, 2 and then a formula that says if the cell above me is equal to a two, reset it back to a zero, otherwise add one to the cell above me.
And that just gets copied down.
And what it does for column F is it just generates 729 rows of 0, 1, 2, 0, 1, 2, 0, 1, 2 over and over and over.
For E, we started out with zeros.
But then this little formula up here in the formula bar, if the cell to the right of me and this formula is going to be used to fill in the rest of the table, if the cell to the right of me is less than the cell above that cell.
So, if X18 is less than X17, that means we just went from a two back to a zero, then look at the cell above me.
If it's equal to a two, set it back to a zero. Otherwise, it's the cell above me plus one.
If this test is not true, we're just going to repeat the number above me.
And what that does is it generates here three ones, then three twos back to three zeros, back to three ones, back to three twos. And that same formula copied over.
Make sure that over here we have nine zeros followed by nine ones, followed by nine twos.
And then over here it is 27.
So, it's powers of three, 3, 9, 27, 81 and so on.
So, this is how I generated the table of 729 permutations here. Number that I'm on the left-hand side.
And now, what I have to do is I have to take these six input cells and get them back into my model.
So, we use text join with a dash in between to generate to take those six input cells, make them into a single input cell.
All right, so these 729 input cells along the left-hand side are things that I want to iterate through the model back on the data tab over here.
Under what if analysis.
This data table is really should be called sensitivity analysis, especially since we have control T tables now, I think it's confusing to be called a data table.
In this particular case, we're going to be using the version where we're just using input cells along the left column.
That means this row of results here is the results that are going to be replicated.
So, for each of the 729 items along the left hand side, I want the...
What I originally called result one.
And then, the shorter result two was the top score in the group. What was the second score in the group?
Is there a tie total points among all teams in the group?
And is there a tie between one and two. All right.
So, what we do is we select the result cells over here into the left column, the input cells, and I'll just page down to get all those controlled down error there would not work nor control shift down arrow.
And then, we'll go control period to go back to the top.
And what if analysis data table is still asking for a row input cell and a column input cell.
But that's okay, we're just going to use the column input cell and come back here and choose that cell.
What's going to happen here when I click okay is amazing.
They're going to take those 41 formulas and run them 729 times logging the results from this row into the table.
So, let's come back here so we can see it happen. All right.
It's just amazingly quick, what is that?
Plus 729 times 41, 30,000 formulas getting run there in the course of one click to actually build 729 versions of the model would just be a monster.
Now, that I have this, I wanted to keep just the results that I could create pivot tables from them.
So basically, copied everything here, control C and then went out and paste it as values to this area.
And then from there, fairly straight pivot tables to be able to generate all of these answers.
All right, so there you have it. There's the Excel math behind the...
What are the chances that we're going to have a tie between second and third in the group stage of the World Cup. Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
The World Cup. We're in the group stage right now. I want to do an analysis in Excel of the odds that any particular group are going to end up in a complicated tiebreaker.
All right, so the World Cup right now, there's 32 teams competing in four team groups. So, there's eight groups of four teams.
Each team gets to play the others.
That means that there are six matches in the group stage and each match has one of three possible outcomes.
Either the team along the top here wins, the team along the left side wins, or it's a draw.
You get three points for a win, just one point for a draw, potentially the most points that could be scored in a group would be you would win all three games, and that's nine.
The most points overall though, among all the teams in the group is 18.
If there's a tie, so the top team advances and then the second team advances to the next stage.
If there's a tie between second and third or even ties between first, second, and third, then there's a very complicated seven step tiebreaker that FIFA uses.
Goal differential, goal scored, head-to-head performance, goal differential just in matches between the tied teams, goal scored in matches between the tied teams.
And then, a fair play system where if you get a yellow card, it's a point.
A second yellow card, which leads to a red is three points, just directly a red card four points, a yellow followed by a red is five points.
Whoever has the fewest number of points in fair play would advance.
That's actually happened once in the 2018 World Cup.
And if that still doesn't solve it, then it's a random drawing.
So, my question was how many times are we likely to end up in this tiebreaker state?
And it's a lot in the eight groups.
It's likely that at least two will be two to three will be thrown into the tiebreaker. Here's how I figured that out.
And in this episode, we're going to talk a little bit about the football, the soccer first, and then come back and look at the awesome what if analysis that allowed me to build these pivot tables that we have.
So, I just built a simple little model here of one group.
We have the four teams and the other four teams across the top.
Of course, team one doesn't play itself, so there's an X there. So, there's really six games that are happening.
And just for a simple notation here, zeros of draw.
One is the team along the top wins.
Two, the side team wins, and I just have a little nice little character string here with the results of A, B, C, D, E, F.
They get broken out here using a TEXTSPLIT function, breaking it at the hyphen.
Thankfully that is out. We didn't have TEXTSPLIT at the last World Cup.
It would've been a lot harder back then.
And then, broke these six results into these six cells right here.
Of course, when team one plays team four, it says here that the side team wins.
That means that when team four plays team one, that the top team is going to win.
So, these six cells are calculations based on these six cells. You can see a little...
Let's see if I come here to formulas and trace dependence, when that answer is zero, one or two, it's going to cause the opposite answer basically to appear in this cell.
And we can see that each of these six go to a different place.
Once I managed to get all six results in. Then we have the score for team one.
In this case, they would have seven points because they had two wins and a draw.
So, three plus three plus one and so on.
And then right here, this section sorts the scores 7, 1, 4, 4 into high to low. Which teams were those?
So team one, team three, team four, team two, and then a little just shortening that.
So, team one had seven, team three had four, team four had four down here.
Was there a tie that's really simple? Are these two cells the same score?
Is there a tie between one and two?
I didn't build this table, but it might be interesting because the top team gets to play the second team in the next group.
So, that's important whether you're first or second, what was the max number of points, what's the points for the second person to advance total points in the group?
And I ended up figuring this out for all 729 permutations, how are there 729 permutations when we have six matches that could be one of three things.
So, we take the number three raised to the six power, we get the 729.
All right, so the answer, how many times is there going to be a tie between second and third out of those 729 possible outcomes?
It's surprising, 28.4% of them are going to end up with a tie between second and third.
So, with eight groups, multiply that 0.284 times eight in you're between two and three. So, it's likely that the tiebreakers will happen.
Once I had all of this data. There's lots of interesting things here.
I really like this pivot table down here, the score of the four teams in the group.
So it is possible, I'm always consulting with my friend Junior Grant, who's my World Cup consultant.
We were driving around, running errands yesterday in the car and I said, what's the lowest that could happen?
I mean, could it just be that every, all six games end up in a draw?
And so, it's a dead tie out of the 729 outcomes that could happen.
There's one chance of a 729 and then on the other end where one team wins all three, another team wins two, and then a third team wins one no draws at all.
You would end up with 24 out of 729 outcomes.
So, how many points can you get total of the six teams getting 18 points, that means there will be absolutely no draws.
It's pretty rare. It's only 8% of the 729.
And down here would mean that you had six draws, a total of 12 points.
That would only happen one out of 729 times pretty rare.
This table here, the yellow I added manually, that shows the places where there's a tie between second and third. And it's not just the number of yellows.
This yellow here 7, 4, 4, 1 occurs 36 times so much more frequently than the others.
So, as we're getting down here deeper into the group stage, we start to think about is it possible that we're going to end up with a tie.
And it seems pretty likely that your team might end up there. All right, so that's the short answer.
28.4% chance that we're going to be thrown into the tiebreakers on group stage.
So probably two or three of the groups seem to be likely for the Excel fans here.
I love the table that I used to build the 729 permutations.
It's pretty cool.
It goes back to the same basic concept we used when we tried to figure out if the asteroid bennu is going to hit the earth back in the Excel hash. It's a complicated bit.
So, if you're just a football or soccer fan, thanks for stopping by.
But for those of you who want to see the Excel behind this, I think it's pretty cool.
And hang on here.
All right, so what I have is a model that's given me, what is it?
One, two, it's given me seven different answers that I thought might be interesting for one group.
So, I have the four teams in the group playing the other teams, you enter the six values here and it will do all the calculations for you.
It answers the questions for you. Is there a tie?
What were the most points scored? What were the second most points scored?
Is there a tiebreaker between two and three? And then, just this little...
I started out with this result using this nomenclature over here, but I decided it wasn't that important really because I don't have the team names here.
And the fact that team one got seven, it would be just as interesting if it was team two had seven, team four had four. I don't really care about the team.
So, I ended up with this alternate result here that just shows the scores of the top team, the second team, the third team, and the fourth team.
And how many times are these scores going to repeat in the 729?
So, it came down to six input cells generating seven output cells. And I wanted to run that 729 times.
It was a crazy day yesterday. MrsExcel was here, junior was here.
We had a lot of deadlines to get done.
And in my head, I said, "I'm going to sneak back to this Excel spreadsheet and write some VBA".
And I'm glad I didn't write the VBA because I realized that if I could just get the six input cells down to a single input cell, then a data table under the what if tools would solve this for us.
And that's when I invented this input cell here where we put A, B, C, D, E, F.
Those six results, either a zero for a draw, one for the top team wins two for the side team wins made that be the input cell, added the TEXTSPLIT function in here to take that one input cell and change it into six input cells.
I initially thought about using a data table, but a data table can't take six input cells along the left-hand side.
That's just not the way that it works. It has to be a single input cell.
So, building this little thing here allowed me to use a data table.
All right, so you understand how this model works.
Usually, when I'm showing a data table, it's always the car loan payment, how to figure out the payments for various prices of cars in terms of loan. And it's a single formula.
I like this model better.
We have six input sales, a total of 41 formulas to calculate the results for just one of the 729 permutations.
So, there's a lot of math that's happening here.
Now, we're going to scroll over to this table here, A, B, C, D, E, F. So, these are the six matches in one group.
And starting right here in this cell, I just have 0, 1, 2 and then a formula that says if the cell above me is equal to a two, reset it back to a zero, otherwise add one to the cell above me.
And that just gets copied down.
And what it does for column F is it just generates 729 rows of 0, 1, 2, 0, 1, 2, 0, 1, 2 over and over and over.
For E, we started out with zeros.
But then this little formula up here in the formula bar, if the cell to the right of me and this formula is going to be used to fill in the rest of the table, if the cell to the right of me is less than the cell above that cell.
So, if X18 is less than X17, that means we just went from a two back to a zero, then look at the cell above me.
If it's equal to a two, set it back to a zero. Otherwise, it's the cell above me plus one.
If this test is not true, we're just going to repeat the number above me.
And what that does is it generates here three ones, then three twos back to three zeros, back to three ones, back to three twos. And that same formula copied over.
Make sure that over here we have nine zeros followed by nine ones, followed by nine twos.
And then over here it is 27.
So, it's powers of three, 3, 9, 27, 81 and so on.
So, this is how I generated the table of 729 permutations here. Number that I'm on the left-hand side.
And now, what I have to do is I have to take these six input cells and get them back into my model.
So, we use text join with a dash in between to generate to take those six input cells, make them into a single input cell.
All right, so these 729 input cells along the left-hand side are things that I want to iterate through the model back on the data tab over here.
Under what if analysis.
This data table is really should be called sensitivity analysis, especially since we have control T tables now, I think it's confusing to be called a data table.
In this particular case, we're going to be using the version where we're just using input cells along the left column.
That means this row of results here is the results that are going to be replicated.
So, for each of the 729 items along the left hand side, I want the...
What I originally called result one.
And then, the shorter result two was the top score in the group. What was the second score in the group?
Is there a tie total points among all teams in the group?
And is there a tie between one and two. All right.
So, what we do is we select the result cells over here into the left column, the input cells, and I'll just page down to get all those controlled down error there would not work nor control shift down arrow.
And then, we'll go control period to go back to the top.
And what if analysis data table is still asking for a row input cell and a column input cell.
But that's okay, we're just going to use the column input cell and come back here and choose that cell.
What's going to happen here when I click okay is amazing.
They're going to take those 41 formulas and run them 729 times logging the results from this row into the table.
So, let's come back here so we can see it happen. All right.
It's just amazingly quick, what is that?
Plus 729 times 41, 30,000 formulas getting run there in the course of one click to actually build 729 versions of the model would just be a monster.
Now, that I have this, I wanted to keep just the results that I could create pivot tables from them.
So basically, copied everything here, control C and then went out and paste it as values to this area.
And then from there, fairly straight pivot tables to be able to generate all of these answers.
All right, so there you have it. There's the Excel math behind the...
What are the chances that we're going to have a tie between second and third in the group stage of the World Cup. Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
