michaelsmith559
Well-known Member
- Joined
- Oct 6, 2013
- Messages
- 881
- Office Version
- 2013
- 2007
Here is the article to reference:
https://www.projectsmart.co.uk/docs/monte-carlo-simulation.pdf
Here is some stuff put into excel:
My question concerns interpreting the error. How would I interpret an error of 73.75? Is this the total error of the predicted average? Is it the error between the avg and median?
Another question, I notice the error decreases if you increase the trials. My next question is if you know the number of trials to run per simulation, how many simulations do you need to run?
Thanks for any help. Other questions may follow.
Mike
https://www.projectsmart.co.uk/docs/monte-carlo-simulation.pdf
Here is some stuff put into excel:
Excel 2003 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
11 | Activity | A | B | C | D | E | F | Total | Average | 73750 | Percent | Error | Trials | |||
12 | Minimum | 10000 | 15000 | 7500 | 4800 | 20000 | 5000 | 62300 | Std Dev | 9349 | s | 10% | 7375 | 14 | ||
13 | Maximum | 20000 | 15000 | 12000 | 6200 | 25000 | 7000 | 85200 | Error | 73.75 | e | 9% | 6637.5 | 18 | ||
14 | Trials | 144627 | N | 8% | 5900 | 23 | ||||||||||
15 | 7% | 5162.5 | 30 | |||||||||||||
16 | 6% | 4425 | 40 | |||||||||||||
17 | 5% | 3687.5 | 58 | |||||||||||||
18 | 4% | 2950 | 90 | |||||||||||||
19 | 3% | 2212.5 | 161 | |||||||||||||
20 | 2% | 1475 | 362 | |||||||||||||
21 | 1% | 737.5 | 1446 | |||||||||||||
22 | 0.1% | 73.75 | 144627 | |||||||||||||
23 | 0.01% | 7.375 | 14462680 | |||||||||||||
24 | 0.001% | 0.7375 | 1.45E+09 | |||||||||||||
25 | 0.0001% | 0.07375 | 1.45E+11 | |||||||||||||
26 | 0.00001% | 0.007375 | 1.45E+13 | |||||||||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M12 | =L12*$J$11 | |
M13 | =L13*$J$11 | |
M14 | =L14*$J$11 | |
M15 | =L15*$J$11 | |
M16 | =L16*$J$11 | |
M17 | =L17*$J$11 | |
M18 | =L18*$J$11 | |
M19 | =L19*$J$11 | |
M20 | =L20*$J$11 | |
M21 | =L21*$J$11 | |
M22 | =L22*$J$11 | |
M23 | =L23*$J$11 | |
M24 | =L24*$J$11 | |
M25 | =L25*$J$11 | |
M26 | =L26*$J$11 | |
N12 | =ROUND(((3*$J$12)/M12)^2,0) | |
N13 | =ROUND(((3*$J$12)/M13)^2,0) | |
N14 | =ROUND(((3*$J$12)/M14)^2,0) | |
N15 | =ROUND(((3*$J$12)/M15)^2,0) | |
N16 | =ROUND(((3*$J$12)/M16)^2,0) | |
N17 | =ROUND(((3*$J$12)/M17)^2,0) | |
N18 | =ROUND(((3*$J$12)/M18)^2,0) | |
N19 | =ROUND(((3*$J$12)/M19)^2,0) | |
N20 | =ROUND(((3*$J$12)/M20)^2,0) | |
N21 | =ROUND(((3*$J$12)/M21)^2,0) | |
N22 | =ROUND(((3*$J$12)/M22)^2,0) | |
N23 | =ROUND(((3*$J$12)/M23)^2,0) | |
N24 | =ROUND(((3*$J$12)/M24)^2,0) | |
N25 | =ROUND(((3*$J$12)/M25)^2,0) | |
N26 | =ROUND(((3*$J$12)/M26)^2,0) | |
H12 | =SUM(B12:G12) | |
H13 | =SUM(B13:G13) | |
J11 | =AVERAGE(H12:H13) | |
J12 | =ROUND(STDEVP(H12:H13,AVERAGE(H12:H13)),0) | |
J13 | =L22*$J$11 | |
J14 | =ROUND(((3*J12)/J13)^2,0) | |
L22 | =0.001 | |
L23 | =0.0001 | |
L24 | =0.00001 | |
L25 | =0.000001 | |
L26 | =0.0000001 |
My question concerns interpreting the error. How would I interpret an error of 73.75? Is this the total error of the predicted average? Is it the error between the avg and median?
Another question, I notice the error decreases if you increase the trials. My next question is if you know the number of trials to run per simulation, how many simulations do you need to run?
Thanks for any help. Other questions may follow.
Mike