+ Reply to Thread
Results 1 to 3 of 3

Normal distribution graph - how to?

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    GothenburgGothenburg
    MS-Off Ver
    2011
    Posts
    7

    Normal distribution graph - how to?

    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!

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Normal distribution graph - how to?

    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).

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Normal distribution graph - how to?

    Quote Originally Posted by Louise8905 View Post
    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?
    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
    Please Login or Register  to view this content.
    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.

    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.

    1. Enter G2:G21,I2:I21 into the Name Box to select both ranges.
    2. Click on Insert, Scatter Charters, Scatter With Only Markers.
    3. Right-click on a Series1 (sim) marker, and click on Change Series Chart, Column, Clustered Column, OK.
    4. Right-click on the chart area, and click on Select Data.
    5. Click on Add, select J2:J21 Y values, and click on OK.
    6. Click on OK to exit Select Data.
    7. 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"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. A graph showing normal distribution over Time variable
    By resvic in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 02-11-2015, 05:09 PM
  2. Normal distribution shaped graph
    By pulsewidth in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 05-25-2013, 10:18 AM
  3. normal distribution graph
    By pulsewidth in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-24-2013, 12:25 AM
  4. How to create a bell curve / normal distribution graph
    By anstasija in forum Excel General
    Replies: 5
    Last Post: 04-17-2011, 10:21 PM
  5. [SOLVED] Graph a normal distribution chart?
    By Barbara in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-02-2006, 03:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1