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

How many iterations are enough?

ShareThis

In the simple coin toss example, you probably noticed the answer didn't come out to exactly 50%, even though 50% is the statistically correct answer.

If you recalculate the spreadsheet several times by pressing the F9 key, you will see the average change with each recalculation.  You may see instances where the average is as low as 47% or as high as 53%.

By increasing the number of iterations in your model, you will increase the accuracy of your results.

To illustrate, return to your spreadsheet from the simple coin toss example, and make a new column of simulated coin tosses, this time pasting & copying into 50,000 cells.  As shown below, the column with 50,000 iterations returns an answer much closer to 50% than the column with only 1,000 iterations.

 

One way to spot-check the accuracy of your results in order to determine whether you should increase the number of iterations is to recalculate the spreadsheet several times, and observe how much variance there is in the "Average" (or other key outputs of your model) each time you "run" the model by resetting all the RAND() variables.  To recalculate the spreadsheet (which it what triggers new values for RAND()), simply press the F9 key.  If you see more variance in the results than you are satisfied with, increase the number of iterations until this spot-check method gives answers within an acceptably narrow range.

So how many is enough?

It depends on the complexity of the variables and the required accuracy of the result. 

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.