Hi! i have done one thousand simulations (monte carlo) and i want to make a normal distribution graph with percentiles of probability, how can i do this?
thanks!
Hi! i have done one thousand simulations (monte carlo) and i want to make a normal distribution graph with percentiles of probability, how can i do this?
thanks!
How much do you know about statistics? Essentially, you want to find the average and the standard deviation of your data. From there, a distribution can be derived. I recommend looking through the many web pages that describe the normal distribution since the pictures will really help explain what you are calculating. Maybe this one: http://support.microsoft.com/kb/213930
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
I presume you want to compare the simulation distribution with a normal distribution visually in a chart.
I don't know what you mean by "with percentiles of probability". I assume you want the x-axis to be the cumulative probability. But that seems odd to me.
Download "louise norm dist.xls" from https://app.box.com/s/jsr7x04n753c3f8i82je5d5crenpphs4 (click here).
Click on each of the following links to see the charts created by the procedures below. Unfortunately, this forum does not display images, AFIK.
Normal simulation v. normal distribution:
\1
Uniform simulation v. normal distribution:
\1
Explanation....
To begin, do not use the procedure in http://support.microsoft.com/kb/213930. That procedure relies a random sampling from a normal distribution, which may or may not be normally distributed (often not!). And that is just one of several issues I have with the procedure.
Note: That is not a criticism of Pauleyb. I have been writing this posting over time with many interruptions. The line above was written many hours before Pauleyb posted a response. It is, however, a criticism of Microsoft.
[EDIT] I added the worksheet "MS norm sim" to the example Excel file in order to demonstrate the weakness of the MS approach.
Suppose the results of your simulation are in A2:A1001, which I name "simdata".
For demonstration purposes, I generate two sets of "simulation" results in the example Excel file. See the "uniform sim" and "norm sim" worksheets. They are actually random samples from a uniform and a normal distribution, which are intended to have similar mean and standard deviation.
See the comments in A2 of the "uniform sim" and "norm sim" worksheets for an explanation of how I created uniform and normal random samples.
Then set up the following formulas (the numbers in columns D, H, I and J will vary):
C
D
E
F
G
H
I
J
1
z cumul% bin sim dist norm dist 2
n 1000
-4.00 0.0032% -1710.2287 0 0.0317 3
mean 2998.6690
-3.56 0.0189% -1187.0179 0 0.1569 4
sd 1177.2244
-3.11 0.0932% -663.8070 0 0.7433 5
-2.67 0.3830% -140.5961 4 2.8985 6
-2.22 1.3134% 382.6147 14 9.3038 7
-1.78 3.7720% 905.8256 27 24.5860 8
-1.33 9.1211% 1429.0364 54 53.4910 9
-0.89 18.7031% 1952.2473 96 95.8202 10
-0.44 32.8361% 2475.4581 129 141.3292 11
0.00 50.0000% 2998.6690 156 171.6394 12
0.44 67.1639% 3521.8799 165 171.6394 13
0.89 81.2969% 4045.0907 161 141.3292 14
1.33 90.8789% 4568.3016 104 95.8202 15
1.78 96.2280% 5091.5124 57 53.4910 16
2.22 98.6866% 5614.7233 27 24.5860 17
2.67 99.6170% 6137.9341 4 9.3038 18
3.11 99.9068% 6661.1450 2 2.8985 19
3.56 99.9811% 7184.3559 0 0.7433 20
4.00 99.9968% 7707.5667 0 0.1569 21
> 4.00 100.0000%
0 0.0317 The chart is created from two "series". The "sim" series uses I2:I21 for Y-values. The "norm" series uses J2:J21 for Y-values. Both series use G2:G21 for X-values.Please Login or Register to view this content.
I am not a chart expert. I fumble my way around to get what I want; but there might be better way. The following are the steps I took. It works in Excel 2007 and 2010; and it might work in later versions. Excel 2003 is very different.
- Enter G2:G21,I2:I21 into the Name Box to select both ranges.
- Click on Insert, Scatter Charters, Scatter With Only Markers.
- Right-click on a Series1 (sim) marker, and click on Change Series Chart, Column, Clustered Column, OK.
- Right-click on the chart area, and click on Select Data.
- Click on Add, select J2:J21 Y values, and click on OK.
- Click on OK to exit Select Data.
- Right-click on a Series2 ("norm") bar, click on Change Series Chart, XY, Scatter With Smooth Lines, OK.
Last edited by joeu2004; 02-25-2015 at 06:19 PM. Reason: cosmetic; errata: reversed links to JPGs; "MS norm sim"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks