Originally posted on http://www.howtomeasureanything.com/forums/ on Thursday, February 19, 2009 1:41:48 PM, by Thakur.
“I enjoyed reading Chapter 6 (Measuring Risk: Introduction to the Monte Carlo Simulation). It was very informative. After reading it I tried to do the following using Excel. But I failed.
1). Simulating the Monty Hall Problem.
2). Simulating Birthdays
3). Genetics: Simulating Population Control
Can You please help me and guide me.
Thanks
Thakur”
You are asking for a lot! But how about I answer a bit at a time? First, lets do Monty Hall.
For those of you who might not have heard of this problem, its based on a classic probability theory example. Imagine that you are on the 70’s game show “Let’s Make a Deal” hosted by Monty Hall. You are a contestant and you are given three doors to choose from. Behind one of the doors is a brand new car! If you choose the door with the car behind it, you get to drive it away.
You choose a door. But then Monty Hall shows you what is behind one of the other doors to reveal one of the “joke prizes” (e.g. a donkey). Then he asks you if you would like to keep the door you first chose or switch to the other remaining door. People often think that the odds of winning would be the same whether you switched or not. But they would be wrong.
To demonstrate why switching doors would be better, let’s set up a spreadsheet simulation where we define columns for the prize door, the chosen door, and the revealed door. One more column will be used as a flag to indicate whether we would have won if we stayed with the first door we chose or if we should have switched doors. Then we will copy down the first row of these columns to a few thousand rows to see the outcome.
Column 1, The Prize Door: This is the door the prize is really behind. As a contestant, you wouldn’t know this information, but we need it for the simulation. Write “The Prize Door” in cell A1. In cell A2 write =int(rand()*3+1). This will randomly generate the value of 1, 2 or 3.
Column 2, The Chosen Door: This is the door the contestant chose. In B1, write “The Chosen Door” and in B2 write the same formula you wrote in A2; =int(rand()*3+1). Again, this will randomly generate the value of 1, 2 or 3.
Column 3, The Revealed Door: This is the door Monty Hall reveals. Monty will always reveal a door you didn’t choose and it will always be a door that doesn’t have a prize behind it. In cell C1 write “The Revealed Door” and in C2 write =if(and(a2=1,b2=1),int(rand()*2+2),if(and(a2=1,b2=2),3,if(and(a2=1,b2=3),2,if(and(a2=2,b2=1),3,if(and(a2=2,b2=2),int(rand()+.5)*2+1,if(and(a2=2,b2=3),1,if(and(a2=3,b2=1),2,if(and(a2=3,b2=2),1,int(rand()*2+1))))))))) This seems clumsy, but its visually easier to decompose and understand than some approaches I might have taken. This will generate values according to the following table:
Prize Door……Chosen Door……Revealed Door
1…………………..1………………….2 or 3
1…………………..2………………….3
1…………………..3………………….2
2…………………..1………………….3
2…………………..2………………….1 or 3
2…………………..3………………….1
3…………………..1………………….2
3…………………..2………………….1
3…………………..3………………….1 or 2
Column 4, Winning Strategy; This cell tells you what the winning strategy would have been. Either you stick with the door you first chose or you switch doors. In D1 write “Winning Strategy” and in D2 write =if(A2=b2,0,1). This will generate a 0 if the winning strategy would have been to stick with the door you have and a 1 if you were better off switching.
Now copy down row 2 a thousand rows and take the average of the values in column 4 (remember not to average in the text in D1). One way to do this is write =average(D2:D1001) in cell E1. If you were just as well off sticking with the first chosen door as switching, then this average would be .5. But you will find that the average will be about .667. In other words, two thirds of the time the winning strategy was switching doors. The reason this works is that when Monty Hall reveals one of the other doors, he gives you additional information you didn’t have before. He reveals ONLY a door that doesn’t have a prize and ONLY a door you didn’t choose. When you first choose a door, you have a 2/3 chance of not winning (the prize is behind one of the other two doors). Once he reveals which of the other 2 doors is not a winner, then the remaining door has a 2/3 chance of winning.
Check back for my responses to your other questions. For clarification, when you talk about birthdays do you mean simulating the problem where you find minimum number of people before there is equal odds that at least 2 people have the same birthday?
Thanks for your question
Doug Hubbard
