Mattea Roach and Andrew He finished the Jeopardy Masters Semi-Finals in a tie. Mattea Roach advanced thanks to a complex tie-breaker. What are the odds that the contestants who advanced to the finals would be decided by a tie-breaker?
It turns out that 18.5% of the possible outcomes would have required using a tie-breaker.
This video explains the math behind it all and the Microsoft Excel model used to make the calculation. To download the workbook: Jeopardy Masters Semi-Finals Tie! What Are The Odds - Episode 2602 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Spoiler Alert
(0:21) Tie game is like kissing your sister
(1:06) Odds of a tie in Jeopardy Masters Semi-Finals
(1:33) Jeopardy Masters Tie-Breaker Rules
(1:50) Mattea Montage was foreshadowing the tie
(2:36) Bookmark MrExcel.com for future Excel questions
(2:53) The Excel model
(3:05) COMBIN(4,3) games
(3:20) Pairings easy way
(3:43) Six ways a 3-player game can finish
(4:05) Full outer Join
(4:28) 6^4 or 1296 outcomes
(4:43) Base-6 count up
(5:19) Solving for 1 outcome
(6:15) HSTACK and TRANSPOSE
(7:05) Go To Special Formulas
(7:19) What-If, Data Table to repeat logic for all scenarios
(8:05) Sorting columns high to low with SORT
(8:37) All possible outcomes with UNIQUE
(8:45) Finding Ties
(9:10) Converting percentage to Fraction in Excel
(10:24) Almost 20% chance of Tie
It turns out that 18.5% of the possible outcomes would have required using a tie-breaker.
This video explains the math behind it all and the Microsoft Excel model used to make the calculation. To download the workbook: Jeopardy Masters Semi-Finals Tie! What Are The Odds - Episode 2602 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Spoiler Alert
(0:21) Tie game is like kissing your sister
(1:06) Odds of a tie in Jeopardy Masters Semi-Finals
(1:33) Jeopardy Masters Tie-Breaker Rules
(1:50) Mattea Montage was foreshadowing the tie
(2:36) Bookmark MrExcel.com for future Excel questions
(2:53) The Excel model
(3:05) COMBIN(4,3) games
(3:20) Pairings easy way
(3:43) Six ways a 3-player game can finish
(4:05) Full outer Join
(4:28) 6^4 or 1296 outcomes
(4:43) Base-6 count up
(5:19) Solving for 1 outcome
(6:15) HSTACK and TRANSPOSE
(7:05) Go To Special Formulas
(7:19) What-If, Data Table to repeat logic for all scenarios
(8:05) Sorting columns high to low with SORT
(8:37) All possible outcomes with UNIQUE
(8:45) Finding Ties
(9:10) Converting percentage to Fraction in Excel
(10:24) Almost 20% chance of Tie
Transcript of the video:
Well, I guess this needs a bit of a spoiler alert in case you didn't watch it.
So last night, the Jeopardy Masters semi-final ends in a tie to advance to the finals.
What are the odds of that?
It came down to Mattea Roach and Andrew He and a tie to advance. In the United States, we despise ties.
My entire life, if something ended in a tie, they always said, "A tie game is like kissing your sister," which goes back to this Navy football coach in 1953 after a 0-0 tie with Duke, Eddie Erdelatz.
I had no idea who originally came up with that saying.
And thanks to my Jamaican friend Junior who comes over and we watch English Premier League soccer, I get it.
Draws are actually useful. You get the points.
But here in the United States growing up, a tie is just terrible, right?
And so when Mattea and Andrew ended up in a tie to advance, it was very unsatisfying to me.
The question today is what are the odds of that happening?
And before I get into the Excel, for those of you who are not Excel people, let's just come out and take a look at the possibilities.
So there's 1,296 possible scenarios of how the points could add up after the games. And the chance of any tie happening is 18.5%.
So better than one in six, almost one in five chance that we were going to have a tie.
And it could have been worse.
2% of the time there would've been a three-way tie and 2% of the time there would've been a four-way tie.
Now the reminder of the tiebreakers, it's the number of wins and then it's the case ...
in the semi-finals, neither had a win. And then it came down to this.
Total correct responses in the semi-finals including final Jeopardy.
And Mattea won with 50 correct answers and Andrew only had 45, which makes you go back to the beginning of the episode where they showed that montage of Mattea quickly answering, what, five, six questions in a row.
That seemed really unusual to me at the time, but thinking about it, that makes a lot of sense that they were foreshadowing that it was that run in her earlier semi-final game that is going to make the difference in the tiebreaker.
If it was still tied after that, then it's cumulative scores, excluding daily double and final Jeopardy wagers, and then cumulative scores excluding final Jeopardy wagers.
So there you are.
For those of you who just want to know what are the chances of a tie happening with the 9-3-2-2 points there, it was almost a 20% chance that we were going to have to see a tiebreaker.
Hey, for those of you who are here for the Jeopardy answer, thanks for watching, please click like down below. Remember the channel.
If you ever have an Excel question, come on back, we will be happy to answer that for you.
For all my regular Excel viewers, let's take a look at the Excel behind this model [ inaudible 00:02:52 ].
All right, so for my Excel friends, let's take a look at this great model that figured it out.
It's reminiscent of back in the World Cup where I was trying to figure out the possible things for the group stage.
We have four players and only three compete at a time.
So how can you do every possible combination of those?
The combined function equals COMBIN of four taken three at a time.
There are four possibilities.
And I was actually trying to get my head wrapped around how you would ever build that but it's really just simple.
In each of the games, one of the people is missing.
So the first time, player A is missing, then B, then C, then D.
Going back and looking at the tape, that would've been A as Mattea Roach, B as Matt Amodio, C as Andrew He and D as James Holzhauer.
But as I built this, I didn't use anyone's names, I just had player A, B, C and D.
And then in any one game, the winner gets three points, the second place person gets one point and the third place person gets zero points.
So what are the possibilities? There's six ways that any game could happen.
So player one wins, player two is in second, player one wins, player three is in second and so on. So we have four games and six possible scenarios.
So that's ...
you basically do a full outer join here of which game and which scenario.
This little key field here, I'm going to use that later in my X lookup. So there's not really a game 11.
It's just game one concatenated with a one.
I should have put a dash in between but I didn't even know I was going to make a video.
I was just doing this for my own interest this morning.
All right, so coming back here, there's four games, six scenarios.
So we take six raised to the fourth power, there's 1,296 possible ways.
The sequence function of 1,296, and then this is just basically like a binary count up except it's not binary.
It's kind of base six, but it's not really base six, because we have the numbers one to six instead of zero to five. But you get it here.
We have the numbers one to six, repeating one to six, one to six, one to six.
And then out here, six one, six two, six three and so on.
Nice little formula here just to check to see if the number to the right of me is a one, the number right and above is a six, and then advancing this.
Unless it was already a six, then resetting it back to one.
And that formula just gets copied to the left and we get all of our possible outcomes for the 1,296 different ways that this could go down.
All right, and then a technique here in columns AB through AG. Let's just solve it for one possible outcome.
So put an outcome in here. I just randomly chose 777.
Do an index to get those four outcomes.
So if we come down here, let's just do one that we can see on the screen.
So if we put in a 10 here, you'll see that we get one, one, two, four. One, one, two, four.
So that means in game one, I had to go look up key field game one-one, game two-one, game three-two, game four-four.
And then looking up that key field, which player was it? So in this case, game one-one would be player B.
And how many points did they get? They would've gotten three points.
And copy that across and down.
So we have for the four games going across, for the three players, how many ... who ...
which player and how many points did they score?
Now I had to get all this back into one little table. Lucky for me that we now have the HSTACK function.
So I HSTACK the players and then did a transpose.
So you see B, A, A, A right there and then C, C, B, B. C, C, B, B.
And then same thing. Get the points, just get everything back together.
The goal for this solving strategy is just to have one input cell in as many fields as you need in as ugly a manner as makes sense to you to come down to one final outcome.
And that outcome here is player A ended with six, player B ended with six, player C ended with three and player D ended with one.
So once we have this model, and it'd be interesting to just know how many formulas there actually are in this model.
So if we go to special and select just the formulas, click okay.
19 formulas to get to just the answer for this one of the 1,296 outcomes.
But then the absolute beautiful thing about Excel is back here on the data tab, under what if analysis, the data table. This is an amazing tool.
So we have a model that's set up to solve one scenario.
We put a list of all 1,296 scenarios down the left-hand side, little formulas that point to our outcomes here, so A, B, C, D in total and then select that whole range.
And then with this whole thing selected, the scenario numbers down the left-hand side, the five output cells across the top, what if analysis data table.
And we say that our column input cell is right there.
That takes each of these 1,296 numbers one at a time and plugs them into the outcome and records the output cells.
All right. Then once we have the ...
So in this scenario one it was someone had nine points, five points, two points, zero points.
Sort those high to low.
I remember that when Microsoft came out with the sort function, they were very proud of the fact that they had this sort by column at the end, something that Google Sheets didn't have at the time.
And it came out handy in this case.
Had it not been there, I would've just had to use the large function.
So this orders the people high to low, concatenates them together.
A unique function here gets me the list of all the possible outcomes and then I sorted that high to low.
So we have nine, five, two, zero.
Of course then count if to figure out how many ...
To figure out the number of ties I could have done a formula, but frankly at this point, while the formula looked for a tie between three and four, you also had to check to see if it was a tie between two, the three-way ties or the four-way tie here.
And it was easier for me just to eyeball this and fill that in. And then here, two-way tie.
A some if to count how many times that showed up. One thing that I do like here.
So these numbers divided by 1,296 to get the percentages, but sometimes percentages is a hard thing to grasp. 14.8%.
I love that Excel can take a percentage and convert it to a fraction.
So equal 81.5%, let's copy that down.
And if we ask to format those on the home tab, number, fraction, up to one digit, it rounds it to the nearest, like four fifths. Ah, that's close to 20%.
One seventh, 14 point something percent. That's close enough.
But then these here didn't work out as well as I would've liked.
So I went back in to format cells and said up to two digits.
So there was a chance, a one in 54 chance there would've been a three-way or four-way tie, which would've been just incredibly unsatisfying for those of us who grew up watching American college football and just hating when there's a tie game.
Nothing good comes out of those tie games in American football. I get it.
With the Premier League and soccer football around the world, draws are important because you get that one point. So there you go.
The Excel behind figuring out that we basically had almost a 20% chance that we were going to end up in a tie, and rather than advancing on the merits of the points on how to go into these tiebreakers. All right, there you go.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from Mr. Excel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
So last night, the Jeopardy Masters semi-final ends in a tie to advance to the finals.
What are the odds of that?
It came down to Mattea Roach and Andrew He and a tie to advance. In the United States, we despise ties.
My entire life, if something ended in a tie, they always said, "A tie game is like kissing your sister," which goes back to this Navy football coach in 1953 after a 0-0 tie with Duke, Eddie Erdelatz.
I had no idea who originally came up with that saying.
And thanks to my Jamaican friend Junior who comes over and we watch English Premier League soccer, I get it.
Draws are actually useful. You get the points.
But here in the United States growing up, a tie is just terrible, right?
And so when Mattea and Andrew ended up in a tie to advance, it was very unsatisfying to me.
The question today is what are the odds of that happening?
And before I get into the Excel, for those of you who are not Excel people, let's just come out and take a look at the possibilities.
So there's 1,296 possible scenarios of how the points could add up after the games. And the chance of any tie happening is 18.5%.
So better than one in six, almost one in five chance that we were going to have a tie.
And it could have been worse.
2% of the time there would've been a three-way tie and 2% of the time there would've been a four-way tie.
Now the reminder of the tiebreakers, it's the number of wins and then it's the case ...
in the semi-finals, neither had a win. And then it came down to this.
Total correct responses in the semi-finals including final Jeopardy.
And Mattea won with 50 correct answers and Andrew only had 45, which makes you go back to the beginning of the episode where they showed that montage of Mattea quickly answering, what, five, six questions in a row.
That seemed really unusual to me at the time, but thinking about it, that makes a lot of sense that they were foreshadowing that it was that run in her earlier semi-final game that is going to make the difference in the tiebreaker.
If it was still tied after that, then it's cumulative scores, excluding daily double and final Jeopardy wagers, and then cumulative scores excluding final Jeopardy wagers.
So there you are.
For those of you who just want to know what are the chances of a tie happening with the 9-3-2-2 points there, it was almost a 20% chance that we were going to have to see a tiebreaker.
Hey, for those of you who are here for the Jeopardy answer, thanks for watching, please click like down below. Remember the channel.
If you ever have an Excel question, come on back, we will be happy to answer that for you.
For all my regular Excel viewers, let's take a look at the Excel behind this model [ inaudible 00:02:52 ].
All right, so for my Excel friends, let's take a look at this great model that figured it out.
It's reminiscent of back in the World Cup where I was trying to figure out the possible things for the group stage.
We have four players and only three compete at a time.
So how can you do every possible combination of those?
The combined function equals COMBIN of four taken three at a time.
There are four possibilities.
And I was actually trying to get my head wrapped around how you would ever build that but it's really just simple.
In each of the games, one of the people is missing.
So the first time, player A is missing, then B, then C, then D.
Going back and looking at the tape, that would've been A as Mattea Roach, B as Matt Amodio, C as Andrew He and D as James Holzhauer.
But as I built this, I didn't use anyone's names, I just had player A, B, C and D.
And then in any one game, the winner gets three points, the second place person gets one point and the third place person gets zero points.
So what are the possibilities? There's six ways that any game could happen.
So player one wins, player two is in second, player one wins, player three is in second and so on. So we have four games and six possible scenarios.
So that's ...
you basically do a full outer join here of which game and which scenario.
This little key field here, I'm going to use that later in my X lookup. So there's not really a game 11.
It's just game one concatenated with a one.
I should have put a dash in between but I didn't even know I was going to make a video.
I was just doing this for my own interest this morning.
All right, so coming back here, there's four games, six scenarios.
So we take six raised to the fourth power, there's 1,296 possible ways.
The sequence function of 1,296, and then this is just basically like a binary count up except it's not binary.
It's kind of base six, but it's not really base six, because we have the numbers one to six instead of zero to five. But you get it here.
We have the numbers one to six, repeating one to six, one to six, one to six.
And then out here, six one, six two, six three and so on.
Nice little formula here just to check to see if the number to the right of me is a one, the number right and above is a six, and then advancing this.
Unless it was already a six, then resetting it back to one.
And that formula just gets copied to the left and we get all of our possible outcomes for the 1,296 different ways that this could go down.
All right, and then a technique here in columns AB through AG. Let's just solve it for one possible outcome.
So put an outcome in here. I just randomly chose 777.
Do an index to get those four outcomes.
So if we come down here, let's just do one that we can see on the screen.
So if we put in a 10 here, you'll see that we get one, one, two, four. One, one, two, four.
So that means in game one, I had to go look up key field game one-one, game two-one, game three-two, game four-four.
And then looking up that key field, which player was it? So in this case, game one-one would be player B.
And how many points did they get? They would've gotten three points.
And copy that across and down.
So we have for the four games going across, for the three players, how many ... who ...
which player and how many points did they score?
Now I had to get all this back into one little table. Lucky for me that we now have the HSTACK function.
So I HSTACK the players and then did a transpose.
So you see B, A, A, A right there and then C, C, B, B. C, C, B, B.
And then same thing. Get the points, just get everything back together.
The goal for this solving strategy is just to have one input cell in as many fields as you need in as ugly a manner as makes sense to you to come down to one final outcome.
And that outcome here is player A ended with six, player B ended with six, player C ended with three and player D ended with one.
So once we have this model, and it'd be interesting to just know how many formulas there actually are in this model.
So if we go to special and select just the formulas, click okay.
19 formulas to get to just the answer for this one of the 1,296 outcomes.
But then the absolute beautiful thing about Excel is back here on the data tab, under what if analysis, the data table. This is an amazing tool.
So we have a model that's set up to solve one scenario.
We put a list of all 1,296 scenarios down the left-hand side, little formulas that point to our outcomes here, so A, B, C, D in total and then select that whole range.
And then with this whole thing selected, the scenario numbers down the left-hand side, the five output cells across the top, what if analysis data table.
And we say that our column input cell is right there.
That takes each of these 1,296 numbers one at a time and plugs them into the outcome and records the output cells.
All right. Then once we have the ...
So in this scenario one it was someone had nine points, five points, two points, zero points.
Sort those high to low.
I remember that when Microsoft came out with the sort function, they were very proud of the fact that they had this sort by column at the end, something that Google Sheets didn't have at the time.
And it came out handy in this case.
Had it not been there, I would've just had to use the large function.
So this orders the people high to low, concatenates them together.
A unique function here gets me the list of all the possible outcomes and then I sorted that high to low.
So we have nine, five, two, zero.
Of course then count if to figure out how many ...
To figure out the number of ties I could have done a formula, but frankly at this point, while the formula looked for a tie between three and four, you also had to check to see if it was a tie between two, the three-way ties or the four-way tie here.
And it was easier for me just to eyeball this and fill that in. And then here, two-way tie.
A some if to count how many times that showed up. One thing that I do like here.
So these numbers divided by 1,296 to get the percentages, but sometimes percentages is a hard thing to grasp. 14.8%.
I love that Excel can take a percentage and convert it to a fraction.
So equal 81.5%, let's copy that down.
And if we ask to format those on the home tab, number, fraction, up to one digit, it rounds it to the nearest, like four fifths. Ah, that's close to 20%.
One seventh, 14 point something percent. That's close enough.
But then these here didn't work out as well as I would've liked.
So I went back in to format cells and said up to two digits.
So there was a chance, a one in 54 chance there would've been a three-way or four-way tie, which would've been just incredibly unsatisfying for those of us who grew up watching American college football and just hating when there's a tie game.
Nothing good comes out of those tie games in American football. I get it.
With the Premier League and soccer football around the world, draws are important because you get that one point. So there you go.
The Excel behind figuring out that we basically had almost a 20% chance that we were going to end up in a tie, and rather than advancing on the merits of the points on how to go into these tiebreakers. All right, there you go.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from Mr. Excel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
