How to Create a Bell Curve in Excel
Gaussian distribution curves, commonly known as bell curves, are normal distribution graphs that help in the analysis of variance in datasets. In a bell curve, the highest point (which is also the mean) represents the event that’s most likely to occur, while the rest of the events are distributed in a symmetrical manner with respect to the mean.
From the relative grading of students and creating competitive appraisal systems to predicting returns, bell curves have a wide range of applications. Here, we’ll walk you through the process of creating a bell curve in Excel.
The Basics of Creating a Bell Curve in Excel
To understand how to create a bell curve in Excel, let’s assume you’re a history professor that needs to grade students based on their performance on a test. Suppose the class has 15 students with the following marks:
Now, before you can create the bell curve of any dataset, you need to calculate its:
Finding Mean
You can usebuilt-in functions in Excel to calculate basic statisticssuch as mean, standard deviation, percentage, etc. To find the mean, use the AVERAGE function in Excel:
Type=AVERAGE(B2:B16)to find the mean of the mark sheet given above. You’ll notice that it gives a value of 53.93.
If you want a whole number value, which you’ll usually want, it’s possible to use the ROUND function. To do so, type:
Now the mean becomes 54.
Finding Standard Deviation
Excel shows two formulas for standard deviation:
In statistics, people often pick out samples from a population, soSTEV.Sis normally used. Since you have the complete data i.e. marks of all the students in the class, we’ll useSTDEV.P. To get the standard deviation of the given mark sheet, type:
You’ll get 27.755. If you want your value in whole numbers, simply round it off by typing:
You’ll get 28.
Sorting the Data in Ascending Order
For you to create the bell shape for your normal distribution chart, the data needs to be in ascending order. If your data is not in ascending order (as in our example), simply select all the values (test marks) in your dataset, go to theSort & Filterin the top panel, and selectSort Ascending.
How to Make a Bell Curve in Excel
Now that you’ve got both standard deviation and mean (average), it’s time to calculate the normal distribution of the given values. Once we have that, we’ll have everything we need to create our bell curve using Excel’s scatter plot option. Let’s first find the normal distribution of all the values inside the dataset:
1. Finding Normal Distribution
It’s time to calculate the normal distribution of the data points. In Excel, you can find the normal distribution using theNORM.DISTfunction, which requires the following variables:
To calculate the normal distribution of our test scores:
2. Creating a Scatter Plot
Now that you’ve got the data points and the normal distribution, you have everything required to create your bell curve. For that, you have tomake a scatter plot in Excelby following the steps given below:
You can see that the graph is not perfectly bell-shaped. That’s because the dataset (student’s marks) is not normally distributed (i.e. the mean, median, and mode of the dataset are not the same.)
3. Customizing the Bell Curve
We already have our bell curve, but we can make it a bit better by customizing it. First off, let’s update the title of our curve by double-clicking on the title and entering the desired title. (You can modify the font type, font size, and positioning of the title, among other things.)
You can remove theSeries1written at the bottom of your bell curve by turning off theLegendtoggle. To give your bell curve a better shape, you can define the maximum and minimum values of your bell chart. For that, double-click the x-axis, and you’ll get the Axis Options tab, from where you can make the required changes.
Using Bell Curves in Excel
Now that you have your bell curve, which shows insightful distribution data, you could use it to grade your students. You can apply the same process to create a bell curve for any given data.
While a bell curve provides the probability of a particular data point in your data set, there are several other graphs that you can create in Excel to find other interesting insights about your data set.
When numbers get boring, let these charts and graphs do the talking.
Anyone with more than a passing interest in motorsports must see these films.
It’s not super flashy, but it can help to keep your computer up and running.
Every squeak is your PC’s way of crying for help.
You can’t call this offline, Notion.
Who asked for these upgrades?
