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

Building the "Commute Time" Monte Carlo simulation worksheet


Now that we've developed an Excel equation for each of the variables in our Commute Time problem, we can construct the worksheet to calculate total commute time.

In the illustration below, we have entered the following values in the corresponding cells:

A5:  =IF(RAND() < 0.9, 111, 360)
B5:  =MAX(0, (RAND() * 120) - 30)
C5:  =CHOOSE(INT(RAND()*10)+1, 240, 240, 240, 240, 240, 240, 240, 360, 180, 1800)
E5:  =SUM(A5:C5)
F5:  =E5 / 60

Next make 10,000 copies of ROW 5, and the simulation model is complete.  We're ready to analyze the data.

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.