Dear all, could you please let me understand how to derive a bell chart to restructure salary for our employees based on the performance appraisal Regards, Rekha
From India, Madras
Acknowledge(0)
Amend(0)
From India, Madras
Acknowledge(0)Amend(0)
Dear Rekha,
In the following example, you can create a bell curve of data generated by Excel using the Random Number Generation tool in the Analysis ToolPak. After Microsoft Excel generates a set of random numbers, you can create a histogram using those random numbers and the Histogram tool from the Analysis ToolPak. From the histogram, you can create a chart to represent a bell curve.
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:
1. On the Tools menu, click Data Analysis.
2. In the Analysis Tools box, click Random Number Generation, and then click OK.
3. In the Number of Variables box, type 1.
4. In the Number of Random Numbers box, type 2000.
NOTE: Varying this number will increase or decrease the accuracy of the bell curve.
5. In the Distribution box, select Normal.
6. In the Parameters pane, enter the number calculated in cell B2 (29 in the example) in the Mean box.
7. In the Standard Deviation box, enter the number calculated in cell B4 (14.68722).
8. Leave the Random Seed box blank.
9. In the Output Options pane, click Output Range.
10. Type D2 in the Output Range box.
This will generate 2,000 random numbers that fit in a normal distribution.
11. Click OK.
8. To create a histogram for the random data, follow these steps:
1. On the Tools menu, click Data Analysis.
2. In the Analysis Tools box, select Histogram, and then click OK.
3. In the Input Range box, type D2:D2001.
4. In the Bin Range box, type C2:C8.
5. In the Output Options pane, click Output Range.
6. Type E2 in the Output Range box.
7. Click OK.
9. To create a histogram for the original data, follow these steps:
1. On the Tools menu, click Data Analysis.
2. Click Histogram, and then click OK.
3. In the Input Range box, type A2:A9.
4. In the Bin Range box, type C2:C8.
5. In the Output Options pane, click Output Range.
6. Type G2 in the Output Range box.
7. 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.
(Continued in the next message)
From India, Kochi
Acknowledge(0)Amend(0)
1In the following example, you can create a bell curve of data generated by Excel using the Random Number Generation tool in the Analysis ToolPak. After Microsoft Excel generates a set of random numbers, you can create a histogram using those random numbers and the Histogram tool from the Analysis ToolPak. From the histogram, you can create a chart to represent a bell curve.
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:
1. On the Tools menu, click Data Analysis.
2. In the Analysis Tools box, click Random Number Generation, and then click OK.
3. In the Number of Variables box, type 1.
4. In the Number of Random Numbers box, type 2000.
NOTE: Varying this number will increase or decrease the accuracy of the bell curve.
5. In the Distribution box, select Normal.
6. In the Parameters pane, enter the number calculated in cell B2 (29 in the example) in the Mean box.
7. In the Standard Deviation box, enter the number calculated in cell B4 (14.68722).
8. Leave the Random Seed box blank.
9. In the Output Options pane, click Output Range.
10. Type D2 in the Output Range box.
This will generate 2,000 random numbers that fit in a normal distribution.
11. Click OK.
8. To create a histogram for the random data, follow these steps:
1. On the Tools menu, click Data Analysis.
2. In the Analysis Tools box, select Histogram, and then click OK.
3. In the Input Range box, type D2:D2001.
4. In the Bin Range box, type C2:C8.
5. In the Output Options pane, click Output Range.
6. Type E2 in the Output Range box.
7. Click OK.
9. To create a histogram for the original data, follow these steps:
1. On the Tools menu, click Data Analysis.
2. Click Histogram, and then click OK.
3. In the Input Range box, type A2:A9.
4. In the Bin Range box, type C2:C8.
5. In the Output Options pane, click Output Range.
6. Type G2 in the Output Range box.
7. 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.
(Continued in the next message)
From India, Kochi
Acknowledge(0)Amend(0)Looking for something specific? - Join & Be Part Of Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
Bell Curve Appraisal Tool.doc
Bell Curve Appraisal.pdf