Monte Carlo Simulation Tutorial Excel: Using the PERCENTILE() function to analyze data

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

Monte Carlo Tutorial

power4XL Home

Welcome

Contents

Using the PERCENTILE function

ShareThis

Excel's PERCENTILE() function is useful in many Monte Carlo models, and is particularly useful in answering the main questions behind our Commute Time simulation. Remember, the questions were:

1. How much time do I need to allow in order to have 75% confidence that I will arrive on time?
2. How much time should I allow in order to have 99.5% confidence that I will arrive on time (on days that start with an important meeting)?

As illustrated below, the simple equation

=PERCENTILE(F10:F10009, 0.75)

yields the answer to the first question. Substituting .995 in place of 0.75 as the second argument in the function answers the second question.

Thus we know we can arrive on time about 75% of the time by allowing 8.1 minutes for our commute, but we should allow a full 36.4 minutes in order to have a 99.5% chance of arriving on time.

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.