Monte Carlo Simulation

**Phedwards** · 07-19-2007, 08:49 PM

I have a series of formulas that calculate data using the RAND function, so I get a different result every time I do a re-calc. I would like to re-calc a thousand times and capture the result in a thousand different cells, so I can look for long-term trends. Any ideas?

Or if not possible, can I get Excel to do a re-calc a thousand times and record the average result of all thousand runs.

Thanks
Phil

**mikerickson** · 07-19-2007, 09:05 PM

This might help you.

Please Login or Register  to view this content.

**Myles** · 07-19-2007, 11:34 PM

Another idea. Assuming your formula is in the range A1:A25 covering only 25 cells and you wish to have the range recalculate 1000x. Assuming again that the results needed are SUM, AVERAGE and STANDARD DEVIATION, you can collect these results by code and transfer them onto the worksheet, in my example, in Columns C,D and E. Here is the code:

Sub MonSimu()
Dim i%, n%
Range("C1") = "SUM"
Range("D1") = "AVERAGE"
Range("E1") = "STDEV"
n = 2
For i = 1 To 1000
Calculate
Cells(n, 3) = Application.Sum(Range("a1:a25"))
Cells(n, 4) = Application.Average(Range("a1:a25"))
Cells(n, 5) = Application.StDev(Range("a1:a25"))
n = n + 1
Next
End Sub

This should get you started.