Saquib Ahmad Shuvo
If you are looking for some special tricks to create a bell curve with mean and standard deviation in Excel, you’ve come to the right place. There is one way to create a bell curve with mean and standard deviation in Excel. This article will discuss every step of this method to create a bell curve with mean and standard deviation in Excel. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets and graphs in different spreadsheets for a clear understanding.
What Is Bell Curve?
There is a graph that shows the normal distribution of a variable called the Bell Curve. This is also known as the Normal Distribution Curve. We See this distribution all around us. When we review scores from an exam, we typically find that the majority of the numbers are in the middle. The peak on this curve represents the mean score. Both sides of this curve are lower. Additionally, it indicates that the probability is much lower for extreme values (i.e highest or lowest)
Bell Curve features include:
- According to the data, 68.2% of the distribution is within one standard deviation of the mean.
- Moreover, 95.5% of the distribution falls within two standard deviations of the average.
- Finally, 99.7% of the distribution lies within three standard deviations of the mean.
What Is Mean and Standard Deviation?
Mean
We define the mean as the average of a set of values. An equal distribution of values across a data set is meant by the mean. Generally, the mean means the central tendency of a probability distribution in statistics.
Standard Deviation
Generally, the standard deviation in statistics measures the amount of variation or distribution in a set of numbers. If the value of standard deviation is low it means that the values are close to the mean value. On the other hand, if the value of standard deviation is high, it means the values are distributed in a larger range.
Step-by-Step Procedure to Create a Bell Curve with Mean and Standard Deviation in Excel
In the following section, we will use one effective and tricky method to create a bell curve with mean and standard deviation in Excel. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
Step 1: Create Dataset
Here, we have created the basic outlines of creating a bell curve with mean and standard deviation in Excel
- In the following image, we can see the basic outlines of the bell curve and its related dataset.
- Here, we have Student Name and Score in the following dataset.
- For further calculations, we have inserted the columns Values and Normal Values.
Step 2: Calculate Mean
Now we are going to determine the mean for creating a bell curve. We want to use the AVERAGE function to determine the mean value.
- First of all, to determine the mean, we will use the following formula in the cell H5:
=AVERAGE(C5:C12)
This AVERAGE function will return the mean value for the range of the cells C5:C12.
- Then, press Enter.
Read More: How to Create a Histogram with Bell Curve in Excel (2 Suitable Examples)Â
Step 3: Evaluate Standard Deviation
Here, we will determine the standard deviation for creating a bell curve. To do this, we will use the STDEV.P function.
- Next, to determine standard deviation, we will use the following formula in the cell H6:
=STDEV.P(C5:C12)
The above function will return the standard deviations of the range of the cells C5:C12.
- Then, press Enter.
Read More: How to Create a Skewed Bell Curve in Excel (With Easy Steps)
Step 4: Calculate Normal Values
Finally, we will normal values for creating a bell curve. To do this you have to follow the following process. Here, we will use the NORM.DIST function to determine the normal distribution values.
- According to our previous discussion, 7% of maximum and lowest values are within three standard deviations.
- Next, to determine the value of 99.7% Low, we will use the following formula in the cell H7:
=H5-3*H6
Here, cell H6 is the standard deviation of the dataset.
- Then, press Enter.
- Next, to determine the value of 99.7% High, we will use the following formula in the cell H8:
=H5+3*H6
Here, cell H6 is the standard deviation of the dataset.
- Then, press Enter.
- Then, we are putting 7 in cell H9. We want 8 values that are the reason why we are putting 1 less than our desired value.
- Next, to determine the value of Gap, we will use the following formula in the cell H10:
=(H8-H7)/H9
- Then, press Enter.
- Now, we are going to add values to column D in the dataset.
- Firstly, the first value will be from cell H7.
- Then, you have to type the following formula in cell D6:
=D5+$H$10
- Then, press Enter.
- Next, drag the Fill Handle icon.
- As a consequence, you will get the following Values column.
- Next, to determine Normal Values, we will use the following formula in the cell E5:
=NORM.DIST(D5,$H$5,$H$6,FALSE)
This formula returns the normal distribution for the given mean and standard deviation. We have set these values in the code. Moreover, we have set cumulative to False to ensure we will get the ‘probability density function’.
- Then, press Enter.
- Next, drag the Fill Handle icon
- As a consequence, you will get the following Normal Values column.
Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation
Step 5: Create Bell Curve
Now, we are going to create the bell curve. We have to follow the following process:
- Firstly, select the range of the cells D5:E12.
- Then, go to the Insert tab. Select Insert Scatter (X, Y) or Bubble Chart, and finally select Scatter with Smooth Lines.
- As a consequence, we will be able to get our basic bell curve.
- Now, we want to format our bell curve.
- First of all, double click on the horizontal axis and it will bring the Format Axis dialog box.
- Next, you have to set the Minumum Bounds to 30 and Maximum Bounds to 85 as shown below:
- Next, you have to uncheck Gridlines and Vertical Axis. Here, we get the Chart Elements by clicking on the Plus Sign.
- Next, we have to add Straight Lines from the Shape to dictate the standard deviation in the bell curve.
- Then, we add Bell Curve as a Chart Title to our curve.
- In addition, the Yellow line signifies the mean of the data in the bell curve. We have added these straight lines by turning on the Gridlines again.
- Finally, we have turned these lines off.
- As a consequence, we will be able to get the following bell curve.
Read More: How to Make Bell Curve in Excel for Performance Appraisal
💬 Things to Remember
✎ When you use the function carefully give all the required parentheses. Also, when you use NORM.DIST function, you’ve to make mean and standard deviation as an absolute cell reference.
✎ You have to adjust row height after following each method.
Practice Section
We have already added a practice dataset in the Excel file. Therefore, you can easily follow our methods and practice them yourself.
Conclusion
That’s the end of today’s session. I strongly believe that from now you may be able to create a bell curve with mean and standard deviation in Excel. l. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
