How likely will you make the fantasy football playoffs? It’s week 11 of the Fantasy Football season, which means there’s only three more weeks until the Fantasy Football playoffs. All your hard work up to this point – drafting your team, closely following daily fantasy football podcasts and injury reports, and agonizing over who to play in your Flex spot – rests upon what happens in these next few weeks.

One situation we’ve been in is being in 6th place with a few weeks to go. We’ve wondered – what’s the likelihood that we’ll wind up in the top four places and make the playoffs?

Here’s an example of a league in the 11th week of action. What’s the probability of the tough-luck team “Drinkin Forte,” currently in sixth place but with the second most points scored, to finish in the top four?

It looks like a relatively difficult problem. Drinkin Forte could make the playoffs if in the last three games if it wins one more game than both the 4th and 5th place teams and maintains its point lead. Also, if Drinkin Forte wins two more games than the #2 and #3 teams, he could surpass either of them as well. However, the #7 and #8 teams could surpass Drinkin Forte if they win one more game out of the last three.

This is a situation that lends itself well to Monte Carlo Simulation. Monte Carlo Simulation is better explained here, but basically you set up your spreadsheet to simulate the last three games of the season, and use random variables to determine the outcomes of the games. Each simulation gives you one data point of one outcome that might happen. When you run the simulation many times, it gives you an idea of the overall likelihood of all the different outcomes of the experiment.

Monte Carlo Simulation: Spreadsheet Inputs and Outputs

Spreadsheet Inputs – Current standings, average points scored so far for each team, the schedule of who plays who for the next three weeks.

Spreadsheet Output – We’ll run the simulation of the last three weeks of the season many times, and see what the likelihood of each team making the playoffs is.

First, we paste the standings data on the table into our spreadsheet:

Then we look at the rest of the schedule and add in who is playing who in weeks 12-14:

Okay, now for the somewhat tricky part. We want to generate a random number for each team’s score for each week. At this point, we have 11 weeks of data on each team, so we can reflect each team’s strength by generating a random number with a mean of the average points per week scored by each team. For the standard deviation of the random variable, we’ll use the standard deviation of all of the team’s scores for the past four week (yes, it’s a bit of a shortcut), which is 19.6.

This website shows that to generate a random variable in excel with a specific mean x and standard deviation y, we use the formula =NORMINV(rand(),x,y). So we add that in for each game with each player’s mean score for the first 11 weeks and the standard deviation of 19.6 points:

Now we add in an IF() function to put a “1” in column F next to the team that won the game, and a “0” for the team that lost the game.

Then we want to calculate the final total wins and losses for each team, along with each team’s total points scored to break ties. Here we use a sumif() function to add scores and wins to the existing point total only if it is for the team in question:

Now we can add in some logic to get a final ranking of the teams. This gets a little tricky because the ranking is first based on the win/loss record, and then uses points scored as a tiebreaker. There are many ways around this – we’ll just create another column with Wins + Pts scored/10000 and rank by the new column. We’ll add in another column with a “1” for the top four teams that make the playoffs, and a “0” for the bottom six teams that miss the playoffs:

This is basically one simulated outcome – we see that teams #1, 2, 4 and 6 made it to the playoffs this time. When we hit the recalculate button (F9), we will be running another instance of our simulation. If we run this 100 times, and store the outcomes of each run, we can get an overall sense of the likelihood of each team to make the playoffs. There is software out there that can do this, and perhaps you could write some excel VBA code that would do it, but we’ll just do a brute force F9, copy, paste, F9, copy, paste, F9, copy, paste…

Okay, I nearly cried after doing that 100 times, but here is the output:

Since we ran it exactly 100 times, we can interpret the Sum column as the chance that each team will make the playoffs. If we ran more than 100, we would divide the number of times made the playoffs by the total trials to get the likelihood. Lt Dan still has a 1% chance!

Here’s the spreadsheet: Spreadsheetsolving’s Fantasy Football Playoff Monte Carlo Simulation