Monty Hall Problem 1
This first blog post will be part one of three. The purpose here will be to start building a basic program in Excel to show you how you can actualize quite complex ideas using only a very basic understanding of VBA. As will be the case with most of my posts, I assume the reader has a very basic knowledge of Excel and VBA, this link can help you get started if not (and I am always willing to help as well). For this example, I will focus on creating a Monty Hall simulator and game. For those unfamiliar, here is a very brief overview of the problem:
You are on a game show, on stage there are three doors and you are told that behind one of those doors is a brand new car while the other two doors contain goats. You are asked to choose a door, which you do, say door number 3. The host, who knows which door hides the car, then opens one of the two doors that haven’t been chosen. He must open one with a goat. You now are looking at two doors, one definitely has a car behind it and the other definitely has a goat. You are asked if you would like to stick with your original guess (in this case door number 3) or switch to the other unopened door. What will you do?
Mathematicians and statisticians will tell us that switching doors will lead to a greater probability of winning the game but some people find this to be counter intuitive, after all, there are only two doors left at the end, shouldn’t the odds be 50/50? For a more detailed explanation, see this link which I think explains the problem rather nicely.
This first post will focus on getting the mechanics down. The follow up post will add in some images and a few other tricks to get it looking like a polished game. In the last post I will be adding some automation tools and introduce a few diagnostics so that we can run a simulation of our game countless times in order to get some data and test the veracity of the assumption that switching doors does indeed lead to a higher probability of winning the game. On a final note, I prefer goats to cars so in my version we will be trying to win a goat, the other two doors will simply be empty.
So let’s get started!
Before doing any coding lets set up the spreadsheet. In cell F1 I have written “Win” and in cell G1 I have written “Lose”. We will have our code keep track of the number of wins and losses and place those numbers under each respective heading. Also, change the font color in cells A1, B1, and C1 to white (this is so that we can hide text saying “goat” later). That’s all the prep work I will do for this example. Lets get down to the nitty gritty.
There are three variables we need to define. “x” will be our randomized location for the goat (if x = 1 then the goat resides behind door #1 and so on). Variable “guess” will be the players first door choice (if player selects door #2 then guess = 2) and the final variable is “newguess” for the final door selection. (If the player switches from door #2 to door #3 then newguess = 3.) Simply define them as follows:
Dim x As Integer
Dim guess As Integer
Dim newguess As Integer
Our first subroutine will clear any old data in our work area, randomize our x variable, and place our goat. Cells A1, B1, and C1 will be our doors 1, 2, and 3 respectively. So we will write a line to clear the data as well as turn the cell interior color to white, this way, we can highlight any door later using a different color. And we are also going to have our macro type “Goat” into the cell that holds our goat.
Here is the first “cheat” I’m going to utilize. I want my goat to be easy to find, in case I need to do some debugging, but I also need it to be hidden from the player. You could store it in a variable and call on it in the later, and we will do that in the future, but for debugging purposes and to make sure the game is running smoothly at this point I wanted everything to be a bit more visible. To that end, there should be an easy way to see where the goat is hiding. Simple solution: have the cell with the goat say “Goat” and simply make the font color match the cell background color. Now it is hidden from the player and if we want to run diagnostics later, all we have to do is change the cell’s font color and we can follow our goat as it tries to hide.
Sub goat()
Randomize
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
x = Int((3 – 1 + 1) * Rnd + 1)
Cells(1, x).Value = “Goat”
Call choose
End Sub
The previous code achieves everything we set out to achieve so far and also calls our next subroutine.
The next task is to obtain the player’s choice and assign our variable “guess” to that choice. We will do this by using an input box, in VBA, the syntax is simply application.inputbox(“Enter prompt here”) and the player will enter either 1, 2, or 3. We now have to write an algorithm. Excel will have to reveal the door that the player did not select which also does not have the goat behind it. I wrote an algorithm for two scenarios, one where the player’s first guess just so happens to be where the goat is hiding and another where that is not the case. You will see why in a moment. This will lead the code down two avenues and we can easily see that happening here:
Sub choose()
guess = Application.InputBox(“Pick a door from 1 to 3.”)
If guess <> x Then Call reveal
If guess = x Then Call reveal2
End Sub
If the door number the player guessed is not equal to the door number randomly assigned to “x” then we send our code down to subroutine “reveal”. If they are equal, the only other option is to head down to “reveal2”.
Here is where we build a very simple AI version of the game show host. He needs to know which door hides the goat and which door the player selected and open the appropriate door based on that knowledge. In the easiest scenario, (for our AI game show host to calculate), our player has selected a door that does not hide a goat. This means that Excel can only reveal one of the doors, it cannot be door “guess” because the game rules won’t allow it, and it cannot be the door with the goat because that also violates our game’s rules. To determine which door has to be opened requires a very simple equation:
Z = 6 – (x + guess)
Z is to be the door number revealed. “x” is the door number with the goat, and “guess” is the door selected by the player. The logic follows thusly: there are 3 doors therefore six possible combinations of doors given by 3!, if the player selects door 2 and the goat is behind door 1 then: x + guess = 3 and 6 – 3 = 3. Door 3 will be revealed. Another scenario: x = 1 and guess = 3, 3 + 1 = 4 and 6 – 4 = 2. Door 2 will be opened. Here is a handy table:
To “reveal” a door, I simply have Excel paint the interior of the cell to blue using the interior.colorindexsyntax. Here is that code:
Sub reveal()
Z = 6 – (x + guess)
Cells(1, Z).Interior.ColorIndex = 5
Call change
End Sub
Deciding which door to reveal if x = guess requires a little more work in order to make sure the system is random but it’s fairly straightforward. Recall that x is where our goat is, if x = guess then our player guessed correctly and Excel must reveal one of the other two doors, it doesn’t matter which because neither have been selected by the player and neither have the goat. This will require a little bit of work in order to truly make sure that the door revealed is random.
I will explain the algorithm in writing here and the logic should follow easily:
Let y be the number of the door to be opened.
If guess = 3 (and therefore x = 3) we must reveal either door 1 or door 2. We will use Excel’s Rnd function to generate a random integer of either 1 or 2 and use that for our y variable, that door will open.
If guess = 1 (and therefore x = 1) we must open either door 2 or door 3. We will use Excel’s Rnd function to generate a random integer of 2 or 3 and use that for our y variable, that door will open.
If guess = 2 then either door 1 or door 3 needs to be revealed. An easy workaround here is to have Excel generate a random number between 0 and 1 and if that number is greater than or equal to 0.5, reveal door number 1, otherwise reveal door number 3. From this we can write the code for the scenario where the player’s choice and the goat’s door are equal:
Sub reveal2()
If guess = 3 Then
y = Int((2 – 1 + 1) * Rnd + 1)
Cells(1, y).Interior.ColorIndex = 5
End If
If guess = 1 Then
y = Int((3 – 2 + 1) * Rnd + 2)
Cells(1, y).Interior.ColorIndex = 5
End If
If guess = 2 Then
y = Rnd
If >= 0.5 Then Cells(1, 1).Interior.ColorIndex = 5
If y < 0.5 Then Cells(1, 3).Interior.ColorIndex = 5
End If
Call change
End Sub
Now we are at the home stretch! Notice that both previous subroutines ended by calling something called “change”. That’s what we will build right now. At this point, the player will see two white cells and a blue cell. They know that the blue cell does not have the goat and therefore can choose to either reenter their original choice, not switching doors, or enter the number of the only other white door (cell), thereby changing their guess. Here is where we utilize our final variable, “newguess”. We write another input box and ask the player to either enter the number of the original door they chose or choose the other door. The final task for us, the coders, is to simply check to see if the number that the player has entered, the value of “newguess”, is equal to that of “x”, the door with the goat. If they are equal, we have a message box pop up saying “you win a goat!” or what have you. If those two variables are not equal then have a message box say “you lost” or something similar. I wrote my code this way:
Sub change()
newguess = Application.InputBox(“Will you change doors? Enter a door number.”)
If Cells(1, newguess).Value = “Goat” Then
MsgBox (“You win a goat!”)
Cells(2, 6).Value = Cells(2, 6).Value + 1
Else
MsgBox (“No goat for you!”)
Cells(2, 7).Value = Cells(2, 7).Value + 1
End If
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
End Sub
And, as you can see, after the final verdict is in the cells are relieved of their contents and the color set back to the default. Should the player win, then the cell containing the number of wins is increased by 1. Recall that “Win” is written in cell F1 (cell 1,6) so we want to store the win in the cell beneath that, cell 2,6. In the case of a win, just add 1 to the value already in cell 2,6 which can be seen in the above code block. In the case of a loss, do the exact same thing except in cell 2,7, underneath where we have written “Lose”.
Finally, insert a shape or image and right click on it. Go to “assign macro” and choose the initial subroutine that starts it all, I called mine “goat” so I assigned macro “goat” to my shape. Now all you have to do to start the game is click on the shape and off you go. Here is a screen shot of mine for a reference to how this could look in action (there’s not too much going on visually yet, we can get on that in the second blog post).
We have built an actual working Monty Hall game but there are many more things that can be done. For instance, there is nothing stopping the player from entering any arbitrary number when asked to choose a door. We can fix that by checking to see whether they entered an integer between 1 and 3 and recalling the subroutine if that isn’t the case but I’m going to finish this post as is. In the future, we can add images of doors and have the game initiate when the player clicks on a door, eliminating the need to check the players’ input, it will be guaranteed to be either door 1, 2 or 3 because we simply wont give them any other options. And what about the idea that switching doors will lead to a higher chance of winning? Can we test that? Absolutely! If you repeatedly play the game over and over again the number of wins will increase (as well as the number of losses) and we should be able to see a pattern emerge, simply play the game 100 times and always switch doors, then play it 100 times and never switch and see which strategy leads to the most wins. But who has time for that? No one, of course, so we will build a simulation that does that for us, one with a button corresponding to each strategy. In the third blog post, we will build a very, very basic AI and tell it to switch doors every time and another that never switches doors. Then we will have Excel run the game, 100’s, 1000’s or even 10,000’s of times under each strategy and compare the results.
It is my hope that this post has shown you one way of building an interesting game based on an interesting topic using minimal work and nothing more than just a basic knowledge of VBA. If you would like to know more or are confused about anything here, please feel free to comment or message me using the contact us fields on the home page.
You can find the spreadsheet and full code below.
Dim guess As Integer
Dim newguess As Integer
Dim x As Integer
Sub goat()
Randomize
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
x = Int((3 – 1 + 1) * Rnd + 1)
Cells(1, x).Value = “Goat”
Call choose
End Sub
Sub choose()
guess = Application.InputBox(“Pick a door from 1 to 3.”)
If guess <> x Then Call reveal
If guess = x Then Call reveal2
End Sub
Sub reveal()
Z = 6 – (x + guess)
Cells(1, Z).Interior.ColorIndex = 5
Call change
End Sub
Sub reveal2()
If guess = 3 Then
y = Int((2 – 1 + 1) * Rnd + 1)
Cells(1, y).Interior.ColorIndex = 5
End If
If guess = 1 Then
y = Int((3 – 2 + 1) * Rnd + 2)
Cells(1, y).Interior.ColorIndex = 5
End If
If guess = 2 Then
y = Rnd
If y >= 0.5 Then Cells(1, 1).Interior.ColorIndex = 5
If y < 0.5 Then Cells(1, 3).Interior.ColorIndex = 5
End If
Call change
End Sub
Sub change()
newguess = Application.InputBox(“Will you change doors? Enter a door number”)
If Cells(1, newguess).Value = “Goat” Then
MsgBox (“You win a goat!”)
Cells(2, 6).Value = Cells(2, 6).Value + 1
Else
MsgBox (“No goat for you!”)
Cells(2, 7).Value = Cells(2, 7).Value + 1
End If
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
End Sub