Announcement

Collapse
No announcement yet.

Normal Distribution Graph

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Normal Distribution Graph

    Say I measure the height of 1000 people and I have those in a column in excel. It should be a normal distribution with mean x and standard deviation y. How do I actually graph it so I can see the normal distrubution curve that looks something like this:
    http://images.google.com/images?hl=e...-8&sa=N&tab=wi

    I tried making a scatter graph but it graphs them point by point. I want the height to be on the x axis and something like frequency on the y axis. Any ideas?

  • #2
    Re: Normal Distribution Graph

    Have you tried using the "Analysis Toolpak" available under Tools - Add Ins?
    I found this link to more information about this Excel feature under one of the images on the link provided.
    http://www-micro.msb.le.ac.uk/1010/toolpak.html

    Otherwise I suggest generate three columns with column A = 1 to 1000, column B filled with the height data, and column C filled with the Gaussian (normal) distribution function http://hyperphysics.phy-astr.gsu.edu...th/gaufcn.html.
    Figure out the mean and standard deviation of the Excel data using the Excel functions = AVERAGE("B1:B1000") and = STDEV("B1:B1000"). Use an absolute reference to these two cells when writing the Gaussian function in column C.
    Last edited by macromike; November 16th, 2006, 06:51.

    Comment


    • #3
      Re: Normal Distribution Graph

      It's a histogram... You have to create bins to hold counts of data that falls within ranges in order to get a visual on whether or not the data approximates a normal distribution.

      Search for the "distributions" example in the download section of my web site.

      The histogram feature of the analysis toolpack (don't get me wrong I use xNPV & xIRR all the time) does us a disservice by not generating the formulas necessary to evaluate the data range to create the bins. I don't recommend it.
      Sub All_Macros(Optional control As Variant)

      Comment


      • #4
        Re: Normal Distribution Graph

        Excellent reference here (complete with pictures and step-by-step method) to draw a normal distribution on your Excel data.
        http://www.tushar-mehta.com/excel/ch...n/#enumeration

        An important thing that I left out of my previous post is you must generate a new column filled with the range of approximately -3*standard deviation to + 3*standard deviation. You will need at least this range to fully show the bell-shaped curve. You can use the Fill function to generate any number of points between these two numbers. Ex: Within the Fill function, make the stop value = +3*standard deviation. Make the step value something like 0.01 if you want ALOT of points or 0.25 if you want just a few points - though with two few points, your curve might not look good. It all depends on how big your values are for the Standard Deviation and Mean. These numbers generated by FILL command will be your X-values. In another column write the Guassian distribution function to reference each of these X-values and the Standard Deviation and Mean. These will be your Y-values. Plot them against each other on an X-Y Scatter plot.
        Last edited by macromike; November 16th, 2006, 08:02.

        Comment


        • #5
          Re: Normal Distribution Graph

          Please... just look at my example.

          Hey and if you guys have improvement suggestions, I'm all ears.
          Last edited by Aaron Blood; November 16th, 2006, 08:02.
          Sub All_Macros(Optional control As Variant)

          Comment


          • #6
            Re: Normal Distribution Graph

            OK... here's the link.

            Distributions
            Sub All_Macros(Optional control As Variant)

            Comment

            Working...
            X