How do I make a Monte Carlo simulation of the election in Excel? I found this plug-in http://dawgroup.com/mc/ that has a few day trial periods, but it doesn’t seem to to work. So, I’m not sure if there’s a nother way to do it. Here’s what I want:
Kerry and Bush each have a pretty sure look on 200 some Electoral Votes. I want to have people able to put in their opinion on the chance of Kerry taking each of the swing states individually (assuming they are all independent events) and then have Excel spit out they odds of Kerry getting over 269 EV, or something like that.
Make sense? Can I do this crudely in Excel 2000 as is?
While its not a Monte Carlo simulation, If you mean you want to specify individual probabilities for the states, I suppose you could do something simple like this for fun. (Caveat: I have no idea how good the random number generation abilities of excel are or whether this’d be meaningful statistically.)
1.) Every state gets three columns. In the first cell of each row you enter the probability of the candidate taking the state, in the second cell you enter RAND() to generate a random number between 0 and 1, the third cell in the row put something like =IF(B1<=A1,X,0) in which X = the number of electoral votes.
2.) Continue as above for the remaining contested states, starting with cell #4, three cells for each state.
3.) After entering the data for all the states, add a win/loss column in which the value in your third cells of each state column are tallied and compare to the total needed to win, e.g., if there were three states and 69 electoral votes needed to win, you might have =IF((C1+F1+I1)>=69,1,0) in cell G1.
4.) Now you can copy the formulas down into as many cells as you wish for each combination of probabilities and estimate the probability that the candidate wins, e.g., if you copied the above formulas into 1000 rows, you’d have a single result cell =(COUNTIF(G1:G1000,"=1")/1000).
I think the spreeadsheet will re-calculate every time the sheet updates under the default settings. if auto calculate is turned off, or you want to force a re-calculation, Hit F9.
Thanks, Stan. Sounds like what I was trying. If you want to see my excel spreadsheet and comment on it, email me at <washingtondcusa at a o l dot c o m>. Thanks.