Solve the Monty Hall problem using simulation and Google Sheets 🐐🏎️🐐

The Monty Hall problem is a probability puzzle that became famous in 1990 because its solution is counterintuitive.

If you were faced with this situation, what would you do? Will you stick to your choice or will you switch your choice?

In order to answer this question, you will need to find out which course of action will maximize your chances of winning the car. This, of course, assumes that you prefer winning a car to a goat but we'll leave that aside for now :).

There are multiple ways to solve this puzzle. For example, you could approach it like any other probability question and solve it mathematically. In this tutorial, I will show you how you can solve this puzzle using Google Sheets.

How to solve the Monty Hall problem using Google Sheets?

Many probability puzzles can be solved using simulations. Instead of solving the puzzle mathematically, you create a computer model that you can run repeatedly to understand the outcomes produced by various strategies.

In this case, to solve the Monty Hall problem using simulation, we will first need to build a model that will let us simulate what will happen for the following two strategies: (1) stick with our choice and (2) switch our choice.

Then, we will repeat this process a large number of times to find out how many times we win the car by following each strategy. The probability of winning using a strategy is simply the number of simulations where that strategy resulted in you winning the car divided by the number of simulations run. If you want to maximize your chance of winning the car, you should pick the strategy whose probability of winning is higher.

I will show you how to create a model in Google Sheets for the Monty Hall problem and then run multiple simulations to determine which strategy is better. Should you stick with your initial choice or should you switch?

Prerequisites

This tutorial assumes that you know the basics of Google Sheets (including how to use formulas). You don't need to know probability theory or any other complex concept!

4 steps to simulate the Monty Hall problem in Google Sheets

• Step 1 — Build a model to represent the Monty Hall problem

• Step 2 — Run 1000 simulations in Google Sheets

• Step 3 — Tabulate results

• Step 4 — Determine the winning strategy for the Monty Hall problem

Step 1 — Build a model in Google Sheets to represent the Monty Hall problem

First we need to create a model in Google Sheets to represent the Monty Hall problem. Each row will represent a single simulation. A simulation is a single instance of the problem where you make a choice using each strategy and see if you win.

We'll use the following 5 columns to represent a single simulation:

The above model works in the following way:

• Column A tracks is an ordinal counter that helps us track the number of simulations that have been run. Since the spreadsheet has a header row, the 2nd row is the first simulation. This is why the formula in column A is =ROW() - 1.

• In each simulation, there is exactly one door that the car is behind. Since there are three doors, we will randomly pick the door the car is behind by using the =RANDBETWEEN(1,3) formula. Column B contains this information.

• The next step is for you to make your initial choice, which we'll record in column C. Since you do not have any information at this time, let's assume that you pick the initial door randomly. So again, we'll use the =RANDBETWEEN(1,3) formula to determine the door you'll pick. Remember that you do not know which door the car is behind (you sort of do in the model because you'll see it in column B but the point is your choice is independently made since you won't know the correct door in real life).

• Now that you know the correct door and your initial choice, we need to test each strategy and see if it will result in you winning the car.

• Suppose columns B and C both have the same door number. This means that you picked the door correctly and sticking with your choice will result in you winning the car.

• On the other hand, if columns B and C were not the same, it means that you picked the wrong door and switching your choice is what will win you the car.

• In each simulation (i.e., row), we will record a 1 against each strategy if that strategy wins and a 0 if that strategy loses. We will use column D for the strategy where you stick with your initial choice and column E for the strategy where you switch your initial choice.

• In column D, we will use the formula =IF(B2=C2,1,0) to record a 1 if the values in columns B and C are the same and a 0 otherwise.

• In column D, we will use the formula =IF(B2<>C2,1,0) to record a 1 if the values in columns B and C are NOT the same and a 0 otherwise.

When you're done creating the above model, you should see a sheet like the one below.

The formulas corresponding to the above cells are as follows:

Row 2 represents a single simulation of the Monty Hall problem and columns D and E show you whether a specific strategy wins for this specific simulation. In the above illustration, switching your choice is the strategy that wins.

Step 2 — Run 1000 simulations in Google Sheets

The next step after you create the model is to run the simulation a large number of times. The more simulations you run the better you'll be able to correctly estimate the probability of winning for each strategy. However, you don't have to run infinitely many simulations since you'll be able to estimate the probabilities after even a few hundred simulations.

Since each row represents a simulation, all you need to do to run say 1000 simulations is fill the formulas to 1000 rows by using the fill handle.

Step 3 — Tabulate results

Once you have results from the 1000 simulations, the next step is to see how good each of the two strategies are. To do this, simply sum up the values in each of the two columns D and E. The sum of values in column D will tell you the number of simulations in which sticking with your choice ended up winning you the car. The sum of values in column E will tell you the number of simulations where switching your choice would have won you the car.

In my spreadsheet, these values were as follows:

• Sticking with your choice:

• Sum of values in column D (=SUM(D:D)): 336

• Switching your choice:

• Sum of values in column E (=SUM(E:E)): 664

Therefore, based on the 1000 simulations that I ran, switching your choice would have resulted in you winning the car 66.4% of the time and sticking with your initial choice would have resulted in you winning the car only 33.6% of the time.

I also plotted these percentages as a function of the number of simulations and as you can see they converge over time. One thing that immediately stands out from the graph is that switching your choice is clearly the better strategy and will result in you winning the car roughly two thirds of the time.

Step 4 — Determine the winning strategy for the Monty Hall problem

According to the results from the simulation, sticking with your initial choice will result in winning the car only one thirds of the time whereas switching your choice will win you the car two thirds of the time.

Therefore, to maximize your odds of winning the car, you should switch your choice.

Conclusion

In this tutorial, I showed you how to use Google Sheets to run simulations and solve the famous Monty Hall problem without using any probability theory!

Whenever you're faced with a business problem involving chance, consider using simulations to determine the optimal strategy to pursue. Using Google Sheets, you can build a model to represent the problem at hand and then quickly run several simulations to figure out which strategy to pursue. The best part is you don't have to know detailed probability theory to determine the right course of action.

Thanks for reading!