Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Normal Distribution Graph

  1. #1
    Join Date
    26th March 2006
    Posts
    19

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th May 2005
    Location
    St. Clair Shores, MI, USA
    Posts
    145

    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 at 06:51.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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)

  4. #4
    Join Date
    25th May 2005
    Location
    St. Clair Shores, MI, USA
    Posts
    145

    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 at 08:02.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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 at 08:02.
    Sub All_Macros(Optional control As Variant)

  6. #6
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Normal Distribution Graph

    OK... here's the link.

    Distributions
    Sub All_Macros(Optional control As Variant)

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Random Number Within Normal Distribution
    By J88L in forum EXCEL HELP
    Replies: 3
    Last Post: November 28th, 2007, 08:21
  2. Is Random Normal Distribution Possible
    By ferreira in forum EXCEL HELP
    Replies: 3
    Last Post: July 18th, 2007, 15:58
  3. Test for Normal Distribution
    By tja26 in forum EXCEL HELP
    Replies: 5
    Last Post: January 19th, 2006, 02:07
  4. normal law/distribution in VBA
    By baladame in forum EXCEL HELP
    Replies: 1
    Last Post: November 9th, 2004, 19:30
  5. Normal distribution random number
    By ljoseph in forum EXCEL HELP
    Replies: 2
    Last Post: August 4th, 2004, 02:54

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
  •  
porno