Tuesday, February 19, 2008

Graphing the Normal Distribution Curve with Excel

I know... You're thinking to yourself: How does he make such awesome normal distribution curves for those illustrations on this blog? He can't be drawing them by hand! Well, I learned the basics from Gary Andrus's web page, and then I modified his method to make it more general and a bit faster.

Here's how I do it using MS Excel 2003 (and I'll even throw in a few nice Excel tricks too):

Step 1: In cell A1 enter -4. In cell A2 enter -3.75. Now, highlight both cells and grab that little box in the lower right hand corner of A2 that turns into a plus sign when you hover your mouse over it (it's called the "fill handle") and drag it down to cell A33. Release your mouse and (wolla!) your first column is filled with numbers from -4 to 4 in increments of 0.25.

Step 2: In cell B1 enter =NORMDIST(A1,0,1,0). This tells Excel to calculate the value of the normal distribution PDF for the value in A1 (which is -4) for a normal distribution with mean of 0 and standard deviation 1. I.e. the standard normal distribution. You can fiddle with those values if you want a different normal distribution. The last 0 tells it to use the probability distribution function, not the cumulative distribution function (you overachievers can try using 1 as your last value for extra credit).

Now, you could use the same handy-dandy trick with the "fill handle" that I showed you in step 1 to propagate the NORMDIST function down column B. But I'll show you another way just for kicks. Highlight cells B1 through B33 and hit Ctrl+D. It fills the values down the column. (Think "D" for "down". Ctrl+R works the same way when you select cells to the right. But Ctrl+L and Ctrl+U don't.)

Step 3: Select all your data points - the region A1 through B33 and hit the Chart Wizard button on the toolbar (or Insert-Chart on the menu bar). In the Chart Wizard step 1, select the XY Scatter chart type and the smooth lines without markers sub-type. Click Next. Click Next for step 2 without changing anything. In step 3, on the Axes tab unselect the y axis, in the Gridlines tab unselect the y axis major gridlines, and in the Legend tab unselect Show legend. Click Next. Click Finish in step 4. Ta-da! Your normal distribution curve magically appears before your very eyes.

Step 4: I usually make it look a little bit nice by right-clicking the x-axis, selecting Format Axis, and in the Scale tab changing the Minimum and Maximum to -4 and 4 respectively. I also right-click the graph line itself, select Format Data Series, and in the Patterns tab I give the line a heavier Weight than the default so it stands out better.

After you're done, you can copy and paste your graph into MS Paint, Paint.NET, Paint Shop Pro or any other graphics program you have to fill it in with colors and/or add explanatory text.

3 comments:

Unknown said...

Great help! You explained this quick and easy! Thanks!!!

sagacious voice said...

Thanks a lot. Im so happy to see my data taking chart form...!!!

cheers
Tanmay

sagacious voice said...

Thanks a lot...I can't be more happy seeing my data taking chart form...!!!