Excel Challenge #2: The Monty Hall Problem
Level: Essential Skills, Estimated Completion Time: Under 2 minutes
Sample File
To save you time typing in the data you can download this sample file that has the starting-point for the challenge:
PDF Version
This challenge is also available as a printable PDF file.
If you'd rather watch a video...
Table of Contents
Challenge Overview
Challenge 1: Download the sample files
Challenge 2: Add formulas to randomly generate the Correct Door and First Choice values
Challenge 3: Add a formula to check whether the First Choice was correct
Challenge 4: Add formulas to show if Player 1 or Player 2 chose the correct door
Challenge 5: Fill down formulas to simulate 50 games
Challenge 6: Add formulas to calculate the total scores of each player
Solution
Challenge Overview
The Monty Hall problem is one that has caused a great deal of controversy over the years. In 1991 it was so hotly debated that the New York Times ran a front-page feature on the subject. The debate began when a letter was written to Parade magazine with the following puzzle:
Suppose you’re on a game show, and you’re given the choice of three doors. Behind one door is a car, behind the others, goats. You pick a door, say #1, and the host, who knows what’s behind the doors, opens another door, say #3, which has a goat. He says to you, “Do you want to pick door #2?” Is it to your advantage to switch your choice of doors?
This is called the “Monty Hall” problem because the scenario is quite similar to the game show “Let’s Make a Deal”, which was hosted by Monty Hall until 1991.
Marilyn vos Savant, who wrote Parade’s “Ask Marilyn” column, asserted that picking door #2 is the correct answer, but the magazine received over 10,000 letters from people who thought she was wrong!
In this challenge you’ll use Excel to prove who had the correct answer by running 50 simulated games and comparing Player 1, who always sticks with their original choice with Player 2, who always changes their answer.
You’ll begin with an almost completely empty worksheet and end with a worksheet that shows all 50 simulated games and each player’s scores.
You can complete this challenge using nothing but Excel’s built in formulas and the functions in Excel’s function library. Writing formulas and using the function library are both covered in our free Basic Skills course, although the follow-up Essential Skills and Expert Skills courses go into more detail about certain functions.
Estimated time to complete this challenge: 2 minutes
The solution video shows this task being completed in the estimated time shown above.
It is interesting to compare the time a trained Excel user (trained to Essential Skills level only) can complete a simple task when compared to an untrained user. An untrained user could spend many hours figuring out how to complete this task (if they were able to complete it at all) and would probably attempt it in an unnecessarily complex way.
Challenge 1: Download the sample file and open it in Excel
To save you time typing in the data you can download this sample file that has the starting point for the challenge.
This workbook contains sample data to illustrate how the workbook is going to work when it is finished. None of the cells contain any formulas at present; this is entirely sample data.
In the example you can see the following things:
- The Correct Door was door #2.
- The player’s First Choice was door #2.
- The player’s first choice was not correct (FALSE).
- Player 1, who never changes their first choice would have been incorrect in this case (FALSE).
- Player 2, who always changes their first choice, would have been correct (TRUE).
- Because player 1 was incorrect, the Player 1 Score is currently 0.
- Because player 2 was correct, the Player 2 Score is currently 1.
In the final version you will add formulas to enable all of these values to be calculated automatically and simulate 50 individual tests to see whether Player 1 or Player 2 gets a higher score.
Estimated time: 2 seconds
The solution video shows this task being completed in the estimated time shown above.
Challenge 2: Add formulas to randomly generate the Correct Door and First Choice values
Remove the test data from the worksheet and add formulas to cells A4 and B4 that generate random numbers between 1 and 3.
Your worksheet should now look like this (of course, your random numbers will probably be different):
After you’ve added the formulas, you can recalculate the random numbers by clicking Formulas>Calculation>Calculate Now or by pressing the <F9> key.
Tasks
- Clear the contents of cells A4:E4 and H3:H4.
- Add formulas to cells A4 and B4 that generate random numbers between 1 and 3.
Estimated time: 25 seconds
There is a way to add average formulas to cells B23:E23 with just three clicks of the mouse.
The solution video shows this task being completed in the estimated time shown above.
Challenge 3: Add a formula to check whether the First Choice was correct
Add a formula to cell C4 that will return TRUE if the Correct Door and First Choice are the same and FALSE if they are not.
Your worksheet should now look like this:
As with the previous step, you can recalculate the random numbers by pressing the <F9> key. You can test your formula by pressing <F9> until the two random numbers match.
Hint
The fastest way to do this doesn’t require the use of any functions at all.
Estimated time: 7 seconds
The solution video shows this task being completed in the estimated time shown above.
Challenge 4: Add formulas to show if Player 1 or Player 2 chose the correct door
Add formulas to cells D4 and E4 that return TRUE if the player chose correctly and FALSE if they chose incorrectly.
Your workbook should now look like this:
Tasks
- Add a formula to cell D4 that will show TRUE if Player 1 chose the correct door and FALSE if they did not.
- Add a formula to cell E4 that will show TRUE if Player 2 chose the correct door and FALSE if they did not.
Hint
Remember that Player 1 never changes their first choice and Player 2 always changes their choice.
Estimated time: 16 seconds
The solution video shows this task being completed in the estimated time shown above.
Challenge 5: Fill down formulas to simulate 50 games
Fill the formulas in cells A4:E4 down to cells A53:E53 to simulate 50 games.
Your worksheet should now look like this:
If all of your formulas are correct, each row will have different randomly-generated numbers, resulting in different outcomes each time.
Estimated time: 13 seconds
The solution video shows this task being completed in the estimated time shown above.
Challenge 6: Add formulas to calculate the total scores of each player
You could manually read through each of the 50 tests and calculate the total scores, but of course Excel can do this automatically.
Add formulas to cells H3 and H4 that will return the total number of times each player chose the correct door (each TRUE value).
Your final worksheet should look like this (your totals will be different):
You can press the <F9> key to regenerate the 50 tests as many times as you wish. You will find that Player 2 consistently does better than Player 1, proving that Marilyn was correct and 10,000 of her readers were wrong!
Tasks
- Add a formula to cell H3 that returns the total number of TRUE values in cells D4:D53.
- Add a formula to cell H4 that returns the total number of TRUE values in cells E4:E53.
Hints
You will need to use a function for this calculation.
Estimated time: 34 seconds
The solution video shows this task being completed in the estimated time shown above.
Challenge Solution Video Tutorial
Video Solution Tutorial
Attempt the challenge before watching the solution video
If you have difficulties completing the challenge (or if you want to use our challenges as a learning resource in themselves) we’ve create a professional narrated 26-minute challenge solution video tutorial that fully explains each step needed to complete this challenge. You should only watch the solution video if you have difficulty completing the challenge.
If you do need to work through the challenge solution video tutorial try the challenge again afterwards and keep trying until you can complete it without referring to the video.
