The Monty Hall problem is a famous probability puzzle based on a game show. It shows how counterintuitive probability can be and why switching doors gives a better chance of winning. In this article, we’ll walk through solving the Monty Hall problem using Excel in simple steps. You don’t need advanced knowledge, just basic Excel skills and curiosity.
---
What is the Monty Hall Problem?
Imagine a game show with three doors. Behind one door is a car (the prize), and behind the other two are goats. The player chooses one door. Then, the host, who knows where the car is, opens one of the remaining doors to reveal a goat. The host then gives the player the option to either stick with their original choice or switch to the other unopened door. The question is: Should you switch, or does it not matter?
Intuition might tell you it’s a 50/50 chance, but the math says otherwise. By switching, your chances of winning increase to 2/3, while staying gives you only a 1/3 chance.
---
Why Use Excel?
Excel is perfect for running multiple simulations quickly. By setting up a Monty Hall game in Excel, you can simulate hundreds or thousands of rounds to see the results for yourself. It’s a great way to understand probability through practical application.
---
Step-by-Step: Setting Up the Monty Hall Problem in Excel
1. Create the Columns
Open Excel and create the following columns in row 1:
Column A: Door with the Car
Column B: Player’s Initial Choice
Column C: Host Reveals
Column D: Door if the Player Switches
Column E: Win if Player Switches
Column F: Win if Player Stays
---
2. Randomize the Car’s Location
In Column A (starting from A2), use Excel’s RANDBETWEEN function to randomly place the car behind one of the three doors. For example:
=RANDBETWEEN(1,3)
This means the car can randomly be behind door 1, 2, or 3.
---
3. Randomize the Player’s Initial Choice
In Column B (starting from B2), use the same RANDBETWEEN function to let the player randomly pick a door:
=RANDBETWEEN(1,3)
---
4. Simulate the Host’s Reveal
The host will open a door that:
1. Is not the door with the car (Column A).
2. Is not the door chosen by the player (Column B).
To calculate this, use the following formula in Column C (C2):
=IF(AND(A2<>1,B2<>1),1,IF(AND(A2<>2,B2<>2),2,3))
This formula picks a door that meets the conditions above.
---
5. Simulate Switching
If the player decides to switch, they will choose the remaining unopened door. To calculate this in Column D (D2), use:
=IF(AND(B2<>1,C2<>1),1,IF(AND(B2<>2,C2<>2),2,3))
This formula ensures the player switches to the last unopened door.
---
6. Determine Win or Loss
In Column E (E2), check if switching leads to a win:
=IF(A2=D2,1,0)
Here, 1 means the player wins by switching, and 0 means they lose.
In Column F (F2), check if staying leads to a win:
=IF(A2=B2,1,0)
Here, 1 means the player wins by staying, and 0 means they lose.
---
7. Copy Formulas for Multiple Simulations
Drag these formulas down for hundreds (or thousands) of rows to simulate multiple games. For example, drag the formulas to row 1001 to simulate 1,000 games.
---
8. Calculate Results
To find the win rates for switching and staying, use the AVERAGE function. For example:
In a new cell, calculate the average of Column E:
=AVERAGE(E2:E1001)
This gives the win rate for switching.
Similarly, calculate the average of Column F:
=AVERAGE(F2:F1001)
This gives the win rate for staying.
---
Results and Analysis
After running the simulation, you’ll observe:
Switching wins about 66.7% of the time (2/3).
Staying wins about 33.3% of the time (1/3).
These results confirm the mathematical explanation: Switching is the better strategy because it takes advantage of the initial 2/3 chance that the car was behind one of the other two doors.
---
Why Does Switching Work?
When you first choose a door, there’s a 1/3 chance the car is behind it, and a 2/3 chance it’s behind one of the other doors. When the host opens a goat door, the 2/3 probability doesn’t disappear—it shifts entirely to the other unopened door. By switching, you capitalize on this higher probability.
---
Conclusion
The Monty Hall problem is a great example of how probability works in unexpected ways. Using Excel to simulate the problem helps make the concept clear and shows why switching is the best choice. Try running your own simulations and see the results—it’s a fun and educational way to explore probability!