Bell curve is a kind of graph that displays the normal distribution of a variable. It is mostly used to analyze the performance of employees or students etc. Bell curve helps you in identifying the poor, average and good performers in the team.
The individuals whose performance is marked at the left side of the bell curve can be identified as poor performers and the individuals whose performance lies at the right side of the bell curve are identified as excellent performers and the individuals that are marked in the middle of the curve are the average performers.
Steps to Create a Bell Curve in Excel:
Step 1: Consider that we have the scores of twenty students in ascending order. As to get a smooth bell curve we need the data sorted in ascending order.
Step 2: First we need to calculate the mean of scores that the twenty students have secured. To compute the mean we can use an AVERAGE formula in excel as
=AVERAGE(number 1: number n)
Step 3: Next after calculating the average we have to calculate the standard deviation. For that, we have the formula in excel as:
=STDEV.P(number 1: number n)
Step 4: Now you have to calculate the normal distribution value for each of the score values. For this you have an inbuilt formula as given below:
=NORM.DIST(x, mean, standard_dev, cumulative)
- x is the value for which the normal distribution value has to be calculated.
- mean – is the average of the data
- standard_dev – is the standard deviation of the distribution.
- cumulative – it determines the logical value of the function. If it is TRUE, NORM.DIST returns the cumulative distribution function. If it is FALSE, return probability density function.
To get the normal distribution value for each score value just drag the NORM.DIST formula down up to the last score values.
Step 5: Now select the x and y-axis for the bell chart. In this example our x-axis will be our score column and the y-axis will be our normal distribution column. Select all the values of the score and normal distribution column and go to the Insert tab and search for the Charts group.
In the Charts group, you have two options either you can go for ‘Recommended Charts’ or you can pick one from the ‘Scatter Charts’.
From the Scatter the charts in the above row show variants of a smooth bell curve.
The Recommended Charts also show you variants of the smooth bell curve.
In the graph below, we can notice that the maximum number of students lies between 74.8 to 86.2. In our example, we have 13 students who are between this range and we have three students who are below this range and four students who are above this range.
So, we can say that we have three students who have not performed well. There are 13 students are who are average performers and four students who have excelled in the exam.
Note: You must have data sorted in ascending order to get a smooth bell curve.
If you have unsorted data then your bell curve will look like this:
So this is how you can create a bell curve to analyze the performance of employees or students etc.
Leave a Reply