How to create a bell curve - normal distribution Xls Download
CiteHRCiteHR
 
HOME RISE NEW TEAM →|
Home > Human Resource Section > Human Resource Management
fayeg Started The Discussion:

Hi,

I have tried making a note on how to create a bell curve. Please have a look and give me feedback.

Thanks

Faye

A bell curve is a plot of normal distribution of a given data set. This article describes how you can create a chart of a bell curve in Microsoft Excel.


To create a sample bell curve, follow these steps: 1. Start Excel.
2. Enter the following column headings in a new worksheet: A1:Original B1:Average C1:Bin D1:Random E1:Histogram G1:Histogram


3. Enter the following data in the same worksheet: A2: 23 B2:
A3: 25 B3: STDEV
A4: 12 B4:
A5: 24
A6: 27
A7: 57
A8: 45
A9: 19


4. Enter the following formulas in the same worksheet: B2: =AVERAGE(A2:A9)
B3:
B4: =STDEV(A2:A9)

These formulas will generate the average (mean) and standard deviation of the original data, respectively.
5. Enter the following formulas to generate the bin range for the histogram: C2: =$B$2-3*$B4

This generates the lower limit of the bin range. This number represents three standard deviations less than the average. C3: =C2+$B$4

This formula adds one standard deviation to the number calculated in the cell above.
6. Select Cell C3, grab the fill handle, and then fill the formula down from cell C3 to cell C8.
7. To generate the random data that will form the basis for the bell curve, follow these steps: a. On the Tools menu, click Data Analysis.
b. In the Analysis Tools box, click Random Number Generation, and then click OK.
c. In the Number of Variables box, type 1.
d. In the Number of Random Numbers box, type 2000.

NOTE: Varying this number will increase or decrease the accuracy of the bell curve.
e. In the Distribution box, select Normal.
f. In the Parameters pane, enter the number calculated in cell B2 (29 in the example) in the Mean box.
g. In the Standard Deviation box enter the number calculated in cell B4 (14.68722).
h. Leave the Random Seed box blank.
i. In the Output Options pane, click Output Range.
j. Type D2 in the Output Range box.

This will generate 2,000 random numbers that fit in a normal distribution.
k. Click OK.

8. To create a histogram for the random data, follow these steps: a. On the Tools menu, click Data Analysis.
b. In the Analysis Tools box, select Histogram, and then click OK.
c. In the Input Range box, type D2:D2001.
d. In the Bin Range box, type C2:C8.
e. In the Output Options pane, click Output Range.
f. Type E2 in the Output Range box.
g. Click OK.

9. To create a histogram for the original data, follow these steps: a. On the Tools menu, click Data Analysis.
b. Click Histogram, and then click OK.
c. In the Input Range box, type A2:A9.
d. In the Bin Range box, type C2:C8.
e. In the Output Options pane, click Output Range.
f. Type G2 in the Output Range box.
g. Click OK.

10. Create labels for the legend in the chart by entering the following: E14: =G1&"-"&G2
E15: =E1&"-"&F2
E16: =G1&"-"&H2


11. Select the range of cells, E2:H10, on the worksheet.
12. On the Insert menu, click Chart.
13. Under Chart type, click XY (Scatter).
14. Under Chart sub-type, in the middle row, click the chart on the right.

NOTE: Just below these 5 sub-types, the description will say "Scatter with data points connected by smoothed lines without markers."
15. Click Next.
16. Click the Series tab.
17. In the Name box, delete the cell reference, and then select cell E15.
18. In the X Values box, delete the range reference, and then select the range E3:E10.
19. In the Y Values box, delete the range reference, and then select the range F3:F10.
20. Click Add to add another series.
21. Click the Name box, and then select cell E14.
22. Click the X Values box, and then select the range E3:E10.
23. In the Y Values box, delete the value that's there, and then select the range G3:G10.
24. Click Add to add another series.
25. Click the Name box, and then select cell E16.
26. Click the X Values box, and then select the range E3:E10.
27. Click the Y Values box, delete the value that's there, and then select the range H3:H10.
28. Click Finish.

The chart will have two curved series and a flat series along the x-axis.
29. Double-click the second series; it should be labeled "- Bin" in the legend.
30. In the Format Data Series dialog box, click the Axis tab.
31. Click Secondary Axis, and then click OK.
You now have a chart that compares a given data set to a bell curve.

Poonam Mehra - Member Since: Mar 2007
Hi Dear,

Is there any way i canm have a prepared bell curve , to understand the thing more clearly, u have explained it quite nicely but, i m not able to follow coz i am unaware of the concept.

If you could help by giving a ready thing, , with this note it would really help me alot.

Thanks a Ton.

Cheers
Poonam :wink:

Sona Yadav - Member Since: Aug 2006
Hi Faye, Thanks for the detailed step by step version to creating a bell curve. It is indeed helpful n i thoroughly enjoyed making one. Thanks Sona Yadav :)

Sona Yadav - Member Since: Aug 2006
Hi Faye, Thanks for the detailed step by step version to creating a bell curve. It is indeed helpful n i thoroughly enjoyed making one. Thanks Sona Yadav :)

Sona Yadav - Member Since: Aug 2006
Hi Faye, Thanks for the detailed step by step version to creating a bell curve. It is indeed helpful n i thoroughly enjoyed making one. Thanks Sona Yadav :)

shubhag - Member Since: Aug 2006
Dear Pragyasmita, Please check the attachment.This is how bell curve looks like . Regards Shubha.G


Attached FilesProvided by community member shubhag. Join us to learn and grow with your peers.
File Type: xls 1_124.xls (95.5 KB, 1028 views)
jaicharu - Member Since: May 2009
Hi Pragyasmita, In the help option of excel workbook, search for "Data Analysis". This will show you how to enable that option. Rgds Charu

vishwaswatwe - Member Since: Apr 2009
Hi Faye Thank you for sharing very useful information. Can you be kind to share more such ideas in working with xl worksheets? Best Wishes Vishwas H Watwe

Found This Useful? +Vote Up This Via Google.  

Why Vote? User validation is extremely important for good content to prosper.
Disclaimer: This network and the advice provided in good faith by our members only facilitates as a direction towards the actions necessary. The advice should be validated by proper consultation with a certified professional. The network or the members providing advice cannot be held liable for any consequences, under any circumstances.


PLEASE KEEP YOUR CONDUCT PROFESSIONAL AND POLITE



3M Users, 100K+ Documents & 450K+ Discussions

Share »


Cite.Co helps people come together to solve each other's problems on all aspects of professional life.
Community Support & Professional Insights. Login or Register.
Email/Username    
Password  

About Us - Advertise - Contact Us - RSS   On Google+  
All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Privacy Policy | Disclaimer | Terms Of Service
Facebook Page | Follow Us On Twitter | Linkedin Network