how to loop and calculate averages.

Question

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 7-Sep-11 13:31pm

olukayode.e

Updated 7-Sep-11 22:36pm

Suresh Suthar

v5

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mehdi Gholam · Answer 1 · 2011-09-08T03:55:00

Solution 1

Check the following links :
Microsoft Monte Carlo introduction

http://excelmontecarlo.com/

Posted 8-Sep-11 3:55am

Mehdi Gholam