power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Building a worksheet-based simple coin toss Monte Carlo simulation


Now that we've covered a basic understanding of Monte Carlo concepts, it's time to build the first Monte Carlo simulation worksheet.

This first example will simulate a single coin toss.  In order to determine the probability of the coin landing heads-up or tails-up, we will repeat the simple coin toss many times, then calculate the percentage of those tosses that yield heads.

First we enter the formula simulating a single toss, "=RANDBETWEEN(0,1)", as illustrated in cell A6 below.


Next, we copy cell A6 one thousand times, filling cells A6 through A1006.  To do this, make sure cell A6 is highlighted, then press CTRL+C to copy its contents to the clipboard.  Next, hold down the SHIFT key while pressing PAGE DOWN repeatedly until the selected region reaches cell A1005.  Then press ENTER, and the contents of the clipboard will be pasted into the entire range.

Now we'll calculate the results.  In cell B3, enter the formula "=AVERAGE(A6:A1005)".  Format the cell to show results as a percentage.  The result should be somewhere near 50%. 

This is, in fact, a Monte Carlo simulation in its simplest form.  The "Average" function shows you that approximately 50% of the coin tosses will result in "heads".

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.