Click here to Skip to main content
15,917,565 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Comments
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

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900