Check the following links :

Microsoft Monte Carlo introduction

http://excelmontecarlo.com/

15,917,565 members

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:

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

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

Comments

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

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