Monte Carlo Simulation

[AdiVohra]

I have created a table of 260 values that are a random normal distribution with mean 0 and standard deviation 1 using =NORMINV(RAND(), "mean", "std dev").

The average and variance of this table has been output and when you press F9 this number changes as the random number generator gets to work.

Any ideas of how to create a vba that when pressing F9 it keeps outputting the new average values and variance values of the data set on to a new sheet? Say for example 1000 outputs with the one click of a macro?

 

[joeu2004]

I have created a table of 260 values that are a random normal distribution with mean 0 and standard deviation 1 using =NORMINV(RAND(), "mean", "std dev").

Aside.... If the mean is 0 and the std dev is 1, you can use =NORMSINV(RAND()).

The average and variance of this table has been output and when you press F9 this number changes as the random number generator gets to work. Any ideas of how to create a vba that when pressing F9 it keeps outputting the new average values and variance values of the data set on to a new sheet? Say for example 1000 outputs with the one click of a macro?

Something like the following.

Sub doit()
    ' **** change **** location of random results to copy (average and variance)
    Const data As String = "sheet1!b1:c1"
    Const nloop As Long = 1000
    Dim i As Long
    ' assume Application.Calculation = xlCalculationAutomatic
    ' also assume Sheets("sheet1").EnableCalculation = True
    Application.ScreenUpdating = False
    ' create new worksheet for simulation results
    Sheets.Add after:=Sheets(Sheets.Count)
    ' recalculate first random results
    Range(data).Dirty
    For i = 1 To nloop
        ' copy random results into new range starting with A2:B2.
        ' each assignment recalculates new random results
        Range("a1:b1").Offset(i) = Range(data).Value
    Next
    Application.ScreenUpdating = True
    MsgBox "done"
End Sub