15,917,565 members
1.00/5 (1 vote)
See more:
please i intend to run an equivalent of a Monte Carlo simulation on excel. i have a set of random figures linked to some formula cells to give me an answer. i have written the code, and i have looped it 10,000 times. my result gives me the answer as at the 10,000th time, but this is not what i want,

I want an average of the 10,000 answers. (i.e : for every loop, it keep the answer in suspense, and does the loop 10, 000 times, at the end of the 10000 simulations, it does an average of the answers.
please i would be grateful if i my problem is solved.

Here is the code I wrote:

VB
```Sub VaRSimulation()
'
' VaRSimulation Macro
'
' Keyboard Shortcut: Ctrl+a
'
Let x = 0
Do While x < 20
Sheets("RAND (3) exp").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A253")
x = x + 1
Loop
Range("G1:G3").Select
Selection.Copy
Sheets("Var Analysis and result").Select
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Range("G1:G3").Activate
Selection.Font.Bold = True
Range("A2").Select
End Sub```

now, what this does is just to loop it 20 times, what i want is, "after each loop, the answers are stored up and at the end, takes an average to give me an answer (P.S, there will be 3 answers "G1:G3". for G1, after 20 simulations, it takes an average of the 20 simulations, G2 does the same, and G3 does the same)" thank you
Posted
Updated 7-Sep-11 22:36pm
v5
Dr.Walt Fair, PE 7-Sep-11 21:04pm
How are you saving the simulation results? How are you controlling the 10,000 realizations?
olukayode.e 8-Sep-11 4:22am
That is where i am having an issue, i am not sure about how to go with it pertaining that aspect.

here is the code i wrote

Sub VaRSimulation()
'
' VaRSimulation Macro
'
' Keyboard Shortcut: Ctrl+a
'
Let x = 0
Do While x < 20
Sheets("RAND (3) exp").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A253")
x = x + 1
Loop
Range("G1:G3").Select
Selection.Copy
Sheets("Var Analysis and result").Select
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Range("G1:G3").Activate
Selection.Font.Bold = True
Range("A2").Select
End Sub

now, what this does is just to loop it 20 times, what i want is, "after each loop, the answers are stored up and at the end, takes an average to give me an answer (P.S, there will be 3 answers "G1:G3". for G1, after 20 simulations, it takes an average of the 20 simulations, G2 does the same, and G3 does the same)" thank you