A couple years ago, my Fantasy Football league passed a rule that said our draft order would be determined by lottery each year. As commissioner, it was my job to implement this rule. My initial idea was to buy an actual full-blown lottery ball machine. How cool would that be?! But due a combination of frugality and laziness, I never actually ended up buying one. I also debated using the tried-and-true “numbers out of a hat” method. It was simple and would get the job done, but I decided that I wanted to create something with a little flair.

Enter the Lotto Simulator. This spreadsheet has the ability to run a couple different types of lotteries, and the fun part, as always, is in the math! Now, there’s a whole lot of interesting math we can do for lotteries like this, and maybe I’ll do a super deep dive in a future post. But today, I’ll focus only on what I automated in the Lotto Simulator spreadsheet, just please know that this is only scratching the surface!

Draft lotteries can be set up in a couple different ways. For our Fantasy Football league, we set it up as follows:

• Each team is assigned between 1 and 10 lottery balls based on their finish in the previous regular season
  • 5^th place – 10 balls
  • 6^th place – 9 balls
  • 7^th – 8 balls
  • 8^th – 7 balls
  • 9^th – 6 balls
  • 10^th – 5 balls
  • 4^th – 4
  • 3^rd – 3
  • 2^nd – 2
  • 1^st – 1
• When someone’s lottery ball is selected, they get to pick their draft position

What is the most likely outcome of our league’s draft lottery, and what is the probability of that happening?

• The most likely outcome is that for each draw, the event with the highest probability occurs. Based on the Plackett-Luce model, doing this ten times gives us:

Given our league’s lottery setup:

After identifying the probability of the max likelihood outcome above, suppose we run a lottery that yields the below selection order:

1. 7^th place – 8 balls
2. 8^th place – 7 balls
3. 6^th place – 9 balls
4. 5^th place – 10 balls
5. 2^nd place – 2 balls
6. 9^th place – 6 balls
7. 10^th place – 5 balls
8. 1^st place – 1 ball
9. 4^th place – 4 balls
10. 3^rd place – 3 balls

It’s reasonable to ask “What was the probability of the observed result?” The lotto spreadsheet gives us the answers to this question!

The probability of observing our result is:

Unfortunately, this number doesn’t tell us much on its own. It’s a tiny number, but so too is our max likelihood number we derived earlier. How do we make sense of it all? The p(Max)/p(Observed) ratio is our answer! This number tells us how many times more likely we are to see the max likelihood outcome than the observed outcome for a given lottery. In the example of my league, this ratio is equal to:

This means that we are 19.7 times more likely to observe the max likelihood result than the result we observed.

Let’s do the same lottery again. Suppose that our second lottery gives these results:

1. 7^th place – 8 balls
2. 5^th place – 10 balls
3. 9^th place – 6 balls
4. 4^th place – 4 balls
5. 10^th place – 5 balls
6. 6^th place – 9 balls
7. 3^rd place – 3 balls
8. 8^th place – 7 balls
9. 2^nd place – 2 balls
10. 1^st place – 1 ball

From this lottery, our p(max)/p(observed) ratio is 4.9…. so we can say that the result of the second lottery was a far more likely outcome than the result of the first!

And there we have it. The Lotto Simulator spreadsheet not only runs a lottery, but it also gives us a surface level understanding of the results we observe. Now, when your league-mates complain that a lottery is “rigged,” you can use the Lotto spreadsheet to see just how unusual the results of your lottery are.

Here’s what to do:

• Download the Lotto Simulator spreadsheet
  • You many need to click “Enable Content” to enable macros
• Choose the type of lottery you want to run
  • In addition to the Draft Lottery discussed in this article, the spreadsheet also has a built in Traditional Lottery option. Try it out!
• Run the Lotto Simulator spreadsheet for any and all of your lottery needs!