This will be the third and final blog post regarding the Monty Hall problem. The aim of the build this time is to make something that is both highly functional and stunningly beautiful. Since all we are concerned with this time is raw data it would be nice to have an exciting way of presenting it. Also, if you are feeling lost at any point it may help to read part 1 and part 2 before tackling this final part.
It would be best to build off of the code that was written for the first blog post because things have changed quite a bit with the introduction of doors as initiators and manipulating images to achieve what we wanted. So let’s go back to the original code. There will only be a few modifications this time around and they will all have the express purpose of running this game automatically. In brief, we will create two different scenarios. In the first scenario, we will have the games AI player to select door 1, 2, or 3 at random and then stay with that original guess throughout the rest of the game. The second scenario will be almost identical except to computer layer, or our AI if you will, will always choose to switch doors when given the option. In both cases the human user will input the number of iterations they would like the computer to run. The only limit to the number of iterations will be your computers processing power.
Since this run is concerned with gathering data we can add in some diagnostics. The three main statistics are of course wins, losses, and total iterations. Make sure to have three cells prepared and labeled to hold that data. I would like to see the percentage of wins versus losses. That is easily obtained by the formula:
Total number of wins/ total number of iterations = wins percentage
And of course we will also include the loss percentage:
Total number of losses/ total number of iterations = losses percentage
Another thing that could be interesting to see is the largest win streak and largest lose streak. If our game’s AI player is in a loop doing 10,000 iterations and just happens to win 50 times in a row at some point, that might be interesting to take note of. I will write into the code a way to tally for that so right now just make sure to keep two cells available and label them appropriately.
There are two ways to go about creating this spreadsheet. The first way is boring, have the code run while nothing visually happens then spit out the final numbers. The other, much more fun way, is to make a really cool dashboard with some flashy gimmicks so that the user can see the code being executed. In addition to making a spreadsheet that works in a fluid, intuitive manner, I think it’s also tremendously important to make it look good as well. So that is the tactic I will choose for this blog post. What I built, and what I’m about to show you how to build, is a cool looking dashboard where you will be able to see each simulation of the game play out and watch as the number tally counts rise, I will even throw in a nifty-looking equalizer-looking thing that will visually show wins and losses as they crop up. Here is what the final product will look like:
And here’s what it will look like in action:
The numbers across the page will all spin as the code continues to run. I also wanted to have cells dedicated to the current winning streak (or losing streak) and created a visual representation of that data, in practice, it looks like an equalizer and adds a certain amount of flare to the sheet. You can set your sheet up any way you like but keep note of which cells contain which data.
Some cells won’t be directly effected by the macro but will have equations in them. I’d like to get those set before moving on to writing any code. Here is what the macro will handle on its own: increase the value of the win or loss column, tally the current winning or losing streak and update the largest winning or losing streak. Total iterations (or simulations, however you choose to label it) is simply a formula that adds the number of wins and losses together. Each percentage is also just a simple formula, wins divided by total iterations and losses divided by total iterations. Because those two equations may sometimes have to divide by zero, such as when you reset the data, they will sometimes show a nasty little error message. In these types of situations like to nest my equations within if statements using the “iserror” function to handle any ugly errors. It’s fairly simple and works like this:
If[THIS FORMULA RETURNS AN ERROR] just put a 0, otherwise [PUT THIS FORMULA]
The “ISERROR” function is a Boolean function, if true we tell excel to put something other than the formula that returns the error. If “ISERROR” is false, tell Excel to use the original formula. Here is what that looks like from my sheet.
=IF(ISERROR(H3/SUM(H3:I3)),0,(H3/SUM(H3:I3)))
Here’s a trick to get this write every time, even for really big equations. First, simply write the equation without the error handling “ISERROR” part. Go into the text bar and copy the actual equation (don’t just click on the cell and hit copy. Then do the following:
=if(ISERROR(PASTE HERE!),0,PASTE HERE AGAIN!) That will get you exactly what you want!
Now I would like to build that cool looking visualizer thing. Choose any color scheme you want but I find green and red over a black background look pretty sweet so I went with that. This is actually pretty easy to do. Now, I would like Excel to paint a bar for each sussecive win (or loss). So if the game wins once, then (in my case) cell C4 shows one green bar. If the next iterations devilvers a win, I want two green bars in cell C4, then three and so on until a loss takes place at which point I want to see a red bar in cell D4. In practice these will fluctuate wildly and create a cool visual effect. This is an easy build if we use one of my favorite equations that come built in with Excel, the Rept function! Since I have already dedicated cells to win and loss streak I already have the data I need. In cell C4 place this equation:
=REPT(“|”,H12). Simple! This equation tells Excel to repeat the character in quotes x number of times and x can be a cell reference. The cell I am referencing there is my current win streak number. Now if the number of consecutive wins is 5, Excel will place 5 |’s into cell C4 and color them green. If the next iteration is a win, then there will be 6 |’s in that cell. Do the same for the cell beneath it and tie the equation to the loss streak but make the font color red. When the code is running it should look something like this:
We have now created a super stylish dashboard and are ready to get into coding the actual simulator!
Start off by defining a new variable, I called mine strategy. If strategy is equal to 1 then our game will automatically switch doors as it runs through the simulation, if strategy is equal to two we will have our little AI player remain with the same door they first chose. Write two subroutines to define just that after adding in the additional variable.
Dim guess As Integer
Dim newguess As Integer
Dim x As Integer
Dim y As Integer
Dim strategy As Integer
Sub startgame1()
strategy = 1
Cells(14, 2).Value = “Current scenario: Change doors”
Call goat
End Sub
Sub startgame2()
strategy = 2
Cells(14, 2).Value = “Current scenario: Do not change doors”
Call goat
End Sub
For each case I also want to update the dashboard to show the user which scenario is currently running. That information I want stored in cell C14 and you can see that the above code does just that. Next we have to alter our initiating subroutine so that it loops for as many times as we like.
In the subroutine “goat” we shall build a loop. I introduce another variable called “cd” for countdown and have the user decide what the value should be using an input box. We are going to write our code so that every time it runs an iteration of the game it subtracts 1 from cd then reruns the game. It will do this until cd = 0. Nothing in this subroutine will have changed since we first wrote it in blog post 1 except the added loop. Lets take a quick look at the loop in detail.
These types of loops are easy enough to add. Set your variable, in this case cd, equal to some number. Then run whatever code you wish. After it has finished executing the code it will subtract 1 from cd and then check the value of cd. If cd = 0 then the code will stop. It will look a little like this:
Sub loop_example
Cd = 5
Do until cd = 0
‘Enter something you want repeated here
‘You could have a message box pop up and count down cd for you
msgbox cd
cd = cd-1
Loop
End sub
That code will make a message box pop up saying “5” then “4” etc. until “cd” reaches 0 and it stops. You should take note that there will be no message box alerting you that “cd” is 0 because once it reaches that number the code will stop executing and therefore no message box will be initiated. You can play around with this loop all you like and get familiar with it. Loops are incredibly important when it comes to writing macros.
So in total, the new and updated subroutine should look as follows:
Sub goat()
cd = Application.InputBox(“Enter the number of iterations you desire”)
Do Until cd = 0
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
cd = cd – 1
Loop
End Sub
Next we shall update the subroutine “choose.” Recall that this is where the player would select a door number but we have a much faster AI player now, eliminate any user input and just have Excel randomly choose a door between 1 and 3. Everything else remains exactly the same:
The next subroutines will also remain largely unchanged except we shall be tweaking the “y” variable because it will be needed later on should we want our AI to switch doors. In sub routine “reveal” simply change “Z” to “y”. It will still paint the correct cell like we had done before but now it will also store a value to “y” and that value will be the door that the host has just revealed. If we want to switch doors later all we have to do now is add “y” to “guess” and subtract from 6. That will give us the only door that can be selected under the change doors scenario. Similarly, in the subroutine “reveal2” and for the specific scenario where “guess” = 2, we can still have the macro paint the cells in the same fashion as before but now we also need “y” to take the value of an integer so that it is easy to calculate which door hasn’t been chosen yet. Simply change it so that if the randomly generated “y” is greater than or equal to 0.5, “y” will become equal to 1. If initially “y” is less than 0.5, change it to be equal to 3. Now, in any case imaginable, we have a variable storing the information about which door the host has revealed. There are now plenty of things the code knows and a variable for each piece of information: which door was initially selected stored in variable “guess”, which door was revealed by the host stored in the variable “y”, and which door hides the goat stored in the variable “x”. The updated code follows:
Sub choose()
guess = Int((3 – 1 + 1) * Rnd + 1)
If guess = x Then Call reveal
If guess <> x Then Call reveal2
End Sub
Sub reveal()
y = 6 – (x + guess)
Cells(1, y).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
y = 1
Cells(1, 1).Interior.ColorIndex = 5
End If
If y < 0.5 Then y = 3 Cells(1, 3).Interior.ColorIndex = 5
End If
End If
Call change
End Sub
Now for the test, this is where we will finally call upon our new variable introduced in this post, “strategy.” If “strategy” is equal to 1 then we know that we need to have the AI switch doors and we know what we need to assign the final variable “newguess” to. That requires a very simple equation Newguess = 6 – (y+guess) Remember, “y” is the door that was revealed by the host so we cannot choose that door as our final selection and “guess” cannot be the final selection because we already determined that we are in the scenario where the AI always switches doors. Therefore there is only one option in this case and it is discovered using the above equation. In scenario 2, where the AI never switches doors, all that needs to be done is to set “newguess” equal to “guess” then let the rest of the code run the checks just as we did in the first blog post. A few additional lines have been added (and some cell references changed) in order to record each iteration onto the dashboard. Whenever there is a win we add a number to the data stored in column 8, our wins column and them same is true for the loss column, number 9, in the event of a loss. Getting the visualizer to properly work: in order to make the visualizer work correctly, we need to reset the current win or loss streak column. This is very simple, in the event of a win just set the cell under “Current loss streak” to 0 and in the event of a loss set the value of the cell labeled “Current win streak” to 0. Our visualizer is running off of those numbers and will work beautifully once we include those lines of code. Shown below in the case of a win:
If Cells(1, newguess).Value = “Goat” Then
Cells(3, 8).Value = Cells(3, 8).Value + 1
Cells(12, 8).Value = Cells(12, 8).Value + 1
Cells(12, 9).Value = 0
Recording the highest win/loss streak: this is also simple. Every time there is a win or a loss conduct a quick test. If the value of “Current win streak” is higher than the value of “Largest win streak” then update the value of “Largest win Streak” to reflect the new high score. You can see that in action here:
If Cells(12, 8) > Cells(9, 8) Then
Cells(9, 8).Value = Cells(12, 8).Value
Here is the full subroutine:
Sub change()
If strategy = 1 Then newguess = 6 – (y + guess)
If strategy = 2 Then newguess = guess
If Cells(1, newguess).Value = “Goat” Then
Cells(3, 8).Value = Cells(3, 8).Value + 1
Cells(12, 8).Value = Cells(12, 8).Value + 1
Cells(12, 9).Value = 0
If Cells(12, 8) > Cells(9, 8) Then Cells(9, 8).Value = Cells(12, 8).Value
Else
Cells(3, 9).Value = Cells(3, 9).Value + 1
Cells(12, 9).Value = Cells(12, 9).Value + 1
Cells(12, 8).Value = 0
If Cells(12, 9) > Cells(9, 9) Then Cells(9, 9).Value = Cells(12, 9).Value
End If
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
End Sub
One full iteration is now complete and the data updated accordingly onto the dashboard. If “cd” is greater then 0, the simulation will run on.
Finally, let’s add in a subroutine that resets all of our data and put a little password protection on it so that we don’t accidentally wipe out potentially hundreds of thousands of simulations’ worth of data.
Create a subroutine called “reset” or whatever you fancy and all it has to do is reset every field that contains data (not equations!) to 0. I also made mine change our current scenario field to say “awaiting input…”. To set a password is easy as well, create a varaible equal to a user input box. Then, make an if statement, if the user input is equal to the password then the code will run, if not then nothing will happen. My password is set to “yes”. You can see all of that below:
Sub reset()
resetvar = Application.InputBox(“Do you really wish to reset data? (This is irrevercable). “)
If resetvar = “Yes” Then
Cells(3, 8).Value = 0
Cells(3, 9).Value = 0
Cells(9, 8).Value = 0
Cells(9, 9).Value = 0
Cells(12, 8).Value = 0
Cells(12, 9).Value = 0
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
Cells(14, 2).Value = “Awaiting input…”
End If
End Sub
That should be all there is to be done! Click on whichever scenario you want to run, either switch doors or don’t and watch the data build up as the visualizer runs wild. Enjoy!
Full code an spreadsheet:
Monty hall problem simulation spreadsheet
Dim guess As Integer
Dim newguess As Integer
Dim x As Integer
Dim y As Integer
Dim strategy As Integer
Sub startgame1()
strategy = 1
Cells(14, 2).Value = “Current scenario: Change doors”
Call goat
End Sub
Sub startgame2()
strategy = 2
Cells(14, 2).Value = “Current scenario: Do not change doors”
Call goat
End Sub
Sub goat()
cd = Application.InputBox(“Enter the number of iterations you desire”)
Do Until cd = 0
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
cd = cd – 1
Loop
End Sub
Sub choose()
guess = Int((3 – 1 + 1) * Rnd + 1)
If guess <> x Then Call reveal
If guess = x Then Call reveal2
End Sub
Sub reveal()
y = 6 – (x + guess)
Cells(1, y).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
y = 1
Cells(1, 1).Interior.ColorIndex = 5
End If
If y < 0.5 Then y = 3 Cells(1, 3).Interior.ColorIndex = 5
End If
End If
Call change
End Sub
Sub change()
If strategy = 1 Then newguess = 6 – (y + guess)
If strategy = 2 Then newguess = guess
If Cells(1, newguess).Value = “Goat” Then
Cells(3, 8).Value = Cells(3, 8).Value + 1
Cells(12, 8).Value = Cells(12, 8).Value + 1
Cells(12, 9).Value = 0 If Cells(12, 8) > Cells(9, 8) Then Cells(9, 8).Value = Cells(12, 8).Value
Else
Cells(3, 9).Value = Cells(3, 9).Value + 1
Cells(12, 9).Value = Cells(12, 9).Value + 1
Cells(12, 8).Value = 0
If Cells(12, 9) > Cells(9, 9) Then Cells(9, 9).Value = Cells(12, 9).Value
End If
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
End Sub
Sub resett()
resetvar = Application.InputBox(“Do you really wish to reset data? (This is irrevercable). “)
If resetvar = “Yes” Then
Cells(3, 8).Value = 0
Cells(3, 9).Value = 0
Cells(9, 8).Value = 0
Cells(9, 9).Value = 0
Cells(12, 8).Value = 0
Cells(12, 9).Value = 0
Range(“A1:C1”).Value = “”
Range(“A1:C1”).Interior.ColorIndex = 0
Cells(14, 2).Value = “Awaiting input…”
End If
End Sub