Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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:

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. Senior Member
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?
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 05:51.

Excel Video Tutorials / Excel Dashboards Reports

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.

4. Senior Member
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 07:02.

Excel Video Tutorials / Excel Dashboards Reports

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

6. ## Re: Normal Distribution Graph

Distributions

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

#### 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