The Bell Curve is one of the most useful tools used in statistics and financial data analysis. This helps us to visualize the normal probability distribution of a range of data. In this article, we are going to see how we can make a bell curve in Excel for performance appraisal.
Download Practice Workbook
You can download the workbook with the examples used for demonstration in this example from the link below. Try downloading and practicing it while you go through the guide.
What Is Bell Curve?
As the name suggests, the bell curve is a curve that resembles the shape of a bell which depicts the normal distribution. The highest point of the curve indicates the most probable event in the range of data, which can be either the mean, mode, or median of the range. Other data are evenly distributed around this point and so have an evenly distributed probability of occurring. While the standard deviation of the dataset is depicted by the relative width of the curve.
The curve looks something like this.
This bell curve feature indicates that 68.2% of the distribution is within one standard deviation of the mean. And 95.5% of the distribution is within two standard deviations of the average. While 99.7% are within three standard deviations of the mean.
The practical application of this curve is huge. It helps us o visualize how the mean, mode, or median is above the rest. The curve also shows how other values are clustered around them.
2 Suitable Examples to Make Bell Curve in Excel for Performance Appraisal
In this tutorial, we are going for two different approaches to making bell curves in Excel for performance appraisal. The first example will focus on calculating the normal distributions and then making the bell curve out of it. The second example will focus on determining the total percentiles of employees based on their performance and then make a bell curve from it. Each scenario is described in detail in its own sub-section. Follow along to see how you can determine the bell curve in each scenario or find the one you prefer from the table above.
1. Bell Curve of Sales Increase
In this example, we will work with the following dataset.
This dataset consists of sales of each employee in two consecutive months and the increase in their sales. We are going to make a bell curve for this increase in sales. In order to do that, we are going to first determine the average, then the standard deviation for all the increases in sales. And then find out the normal distribution for each of them. We can then finally proceed to plot the curves and thus have the bell curve for performance appraisal- all done in Excel.
In pursuit of these, we are going to use the AVERAGE, STDEV.P, and NORM.DIST functions. The AVERAGE function returns the average of the numbers it takes as arguments. On the other hand, the STDEV.P function also takes a series of numbers as arguments and returns the standard deviations. While the NORM.DIST function takes a value from a range of data, mean and standard deviation for the dataset, and a boolean value as its arguments and returns the normal distribution of the number in the number.
Follow these steps for a more detailed guide.
Steps:
- First, select a cell to put in the average value of the range of data. We have selected cell D16 for this.
- Now, select cell D16 and write down the following formula.
=AVERAGE(E5:E14)
- Once done, press Enter on your keyboard. Thus you will have the average for all the data.
- Then select a cell for standard deviation (we have selected cell D18 for this) and write down the following formula.
=STDEV.P(E5:E14)
- Now press Enter on your keyboard.
- After that, make a column for the normal distribution.
- Now select cell F5 and write down the following formula.
=NORM.DIST(E5,$D$16,$D$17,FALSE)
- Then press Enter on your keyboard.
- Now select the cell again. Then click and drag the fill handle icon to the end of the column to fill up the rest of the cells with this formula.
- While the column is selected go to the Insert tab on your ribbon and select Recommended Charts from the Charts
- Now in the Insert Chart box, select the All Charts
- From the left side of the box, select X Y (Scatter) and select Scatter with Smooth Lines from the right side of the box.
- Finally, click on OK.
As a result, the bell curve will now be plotted on the spreadsheet.
After some modifications, the bell curve for performance appraisal will look something like this.
Read More: How to Create a Bell Curve with Mean and Standard Deviation in Excel
2. Bell Curve for Project Completion Remarks
In our second example, we will this dataset for demonstration.
It is another list of employees with how many days they needed to complete a project and a remark based on how they performed. We are going to use this dataset to plot the bell curve in this example.
To do that, we are going to make a different dataset from this one with the help of the COUNTIF and SUM functions. The COUNTIF function takes a range and a condition as arguments and returns how many times the condition has been met in the range. While the SUM function takes a range as an argument and returns the total of those numbers.
Follow these detailed steps for a clear guide on how we can plot the bell curve for performance appraisal for this dataset.
Steps:
- First, make a chart for the performance appraisal list as shown in the figure below.
- Then go to cell H6 and write down the following formula in it.
=COUNTIF($E$5:$E$24,G6)
- After that, press Enter.
- Now select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.
- Now, select cell I6 and write down the following formula.
=H6/SUM($H$6:$H$10)
- Then press Enter on your keyboard.
- After that, select the cell again and click and drag the fill handle icon to fill the rest of the column with the formula.
- After that, select the Remarks and % of total
- Then go to the Insert tab on your ribbon and select Recommended Charts from the Charts
- Next, select the All Charts tab in the Insert Chart
- Now select the X Y (Scatter) option from the left of the box and select the Scatter with Smooth Lines from the right side.
- Finally, click on OK.
As a result, a bell curve for performance appraisal based on the dataset will be inserted into the spreadsheet.
After some modifications, it will look something like this.
Conclusion
These were two different examples of making a bell curve in Excel for performance appraisal. Hope you will be able to make a bell curve for your dataset now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
For more guides like these, visit Exceldemy.com.
