Bell Curve — колоколообразная кривая

Bell Curve in Excel | How To Make Bell Curve in Excel?

Bell Curve - колоколообразная кривая

Excel Bell Curve (Table of Contents)

  • Bell Curve in Excel
  • How to Make a Bell Curve in Excel?

Bell Curve in Excel

Bell curve in excel is mostly used in the Employee Performance Appraisal or during Grading of the Exam Evaluation. Bell curve is also known as the Normal Distribution Curve.

The main idea behind bell curve is when everybody in the team or class is a good performer, how will you identify who is the best performer, who is the average performer and who is the poor performer in the team or class.

So before we proceed further let us first understand the concept of the Bell Curve in Excel with the help of a simple example.

Suppose there are 100 students in a class who appeared for the exam. As per the education system whoever gets more than 80 will get A grade. But then there will be no difference between a student who scores 99 and a student who scores 81 as both would get A grade.

Now using the Bell curve approach, we can convert the marks of the students into Percentile and then they are compared with each other. Students getting lower marks will be on the left side of the curve and students getting higher marks will be on the right side of the curve and most of the students which are average will be in the middle of the curve.

We need to understand two concepts to understand this theory better.

  1. Mean – It is the average value of all Data Points.
  2. Standard Deviation – It shows how much Dataset varies from the Mean of the Dataset.

How to Make a Bell Curve in Excel?

To make a bell curve in Excel is very simple and easy. Let’s understand how to make a bell curve in excel with some examples.

Example #1

Suppose there are 10 students in a class which has got the below marks 100.

You can calculate the Mean with the help of the Average Function.

In cell B12 I have inserted the Average Function as you can see in the below screenshot.

The result of the Average Function is 86. So we can say that Mean in our example is 86 which will be the center of the Bell curve.

Now we need to calculate the Standard Deviation which we can do with the help of STDEV.P function.

So the result of the standard deviation in our case is 2.58.

In this case, the value of 2.58 means, that most of the students will be in the range of 86-2.58 or 86+2.08.

Now to calculate the normal distribution you need to insert a formula for normal distribution in the next cell of Marks. The syntax of the formula is as below.

So let us insert the formula from cell C2. Please make sure that you freeze the cells for Mean and standard deviation in the formula.

The Result is given below.

Now drag the formula in below cells till cell C10.

Insert a Bell Curve in Excel (Normal Distribution Curve)

Now as all the data is ready with us for the Bell curve, we can insert a Bell curve chart in excel.

First, select the Marks of all student and Normal Distribution column which we calculated above and under the Insert tab, click on Recommended Charts as shown below.

Now under Recommended chart, you will see lots of option for different types of charts. But to get a normal distribution curve (Bell Curve) follow the below steps.

  • First, click on All Charts.
  • Now select XY Scatter Chart Category on the left side.
  • At the top of the dialog box, you can see the built-in styles click on the third style Scatter with Smooth Lines.
  • Select the Second chart and click on Ok.
  • So now you will be able to see the Bell curve in your excel sheet as below.

Now when you look at the Bell Curve, you can see that maximum student will be in the range of 83.42 & 88.58(86-2.58 =83.42 & 86+2.58 = 88.58).

In our example, there are 6 students who are in between 83 & 88. So I can say that they are the average performers in the class. Only 2 students have scored more than 88 so they are the best performers in the class. Only one student has scored below 83, so he is a poor performer in the class.

Remove the Vertical axis from the graph

The Horizontal axis is the Marks scored and Vertical axis is the Normal Distribution. If you do not want to see the Vertical axis of Normal Distribution, you just need to follow the below steps.

  • Click on the graph and you will see the “+” sign on the right corner of the graph area.
  • After clicking on the + sign, you will see an option for Axis as below. Click on the Axis button and you will see two options for Horizontal Axis and Vertical Axis. Just uncheck the Vertical Axis.
  • This Bell curve helps you to identify who is the Top performer in your team and who is the lowest performer and help you decide the ratings of the employee.

When Data is not sorted in Ascending Order

So in the above example, Marks were sorted in Ascending order, but what if the data is not arranged in the ascending order. Then we will not be able to get a smooth bell curve as above. So it is very important to arrange the data in ascending order to get a smooth bell curve in excel.

Example #2

Let’s take a similar example but this time data will not be sorted in ascending order.

The Average (which is Mean), Standard Deviation and Normal Distribution will all remain the same.

But the Bell curve graph of the same example will look different because Marks were not sorted in the Ascending order. The Bell curve graph will now look as below.

So as you can see in the graph, it started with 83 and ends with 88. Also, you can notice that the graph is not as smooth as it was in example 1. So to get a smooth Bell curve in excel it is very important to sort the data in ascending order.

Things to Remember

  • Always make sure to sort the data in the Ascending order to get a smooth bell curve in Excel.
  • Remember to Freeze the Cell of Average (Mean) & Standard Deviation when inputting the formula for the Normal Distribution.
  • There are two formulas for Standard Deviation – STDEV.P & STDEV.S (P stands for Population & S stands for Sample). So when you are working on Sample data, you need to use STDEV.S.

This has been a guide to Bell Curve in Excel. Here we discuss how to make a Bell Curve in Excel along with excel examples and downloadable excel template. You can also go through our other suggested articles –

Источник: https://www.educba.com/bell-curve-in-excel/

Bell Curve (Formula, Examples)| What is Bell Shaped Graph?

Bell Curve - колоколообразная кривая

Bell Curve is a normal probability distribution of variables that is plotted on the graph and is a shape of a bell where highest or top point of the curve represents most probable event all the data of the series.

The formula for Bell Curve as per below:

Where,

  • μ is mean
  • σ is a standard deviation
  • π is 3.14159
  • e is 2.71828
  • The mean is denoted by μ, which denotes the center or the mid-point of the distribution.
  • The horizontal symmetry about the vertical line, which is x = μ as there is square in the exponent.
  • The standard deviation is denoted by σ and is related to the spread of the distribution. As σ increases, the normal distribution will spread out more. Specifically, the distribution’s peak is not as high, and the distribution’s tail shall become thicker.
  • π is constant pi and has an infinite, which is not repeating decimal expansion.
  • E represents another constant and is also transcendental and irrational pi.
  •  There is a non-positive sign in the exponent, and the rest of the terms are squared in the exponent.  Which means the exponent will always be negative. And because of that, the function is an increasing function for all x < mean μ. The opposite is true when all x > mean μ.
  • Another horizontal asymptote corresponds to the horizontal line y, which equals 0, which would mean that the graph of the function will never touch the x-axis and will have a zero.
  • The square root in the excel term will normalize the formula, which means that when one integrates the function for searching the area under the curve where the whole area will be under the curve, and it is one, and that corresponds to 100%.
  • This formula is related to a normal distribution and is used for calculating probabilities.

Example# 1

Consider the mean given to you 950, standard deviation as 200. You are required to calculate y for x = 850 using the bell curve equation.

Solution:

Use the following data for the calculation.

First, we are given all the values, i.e., mean as 950, standard deviation as 200, and x as 850. We just need to plug in the figures in the formula and try to calculate the y.

The formula for Bell-Shaped Curve as per below:

y = 1/(200√2*3.14159)e-(850 – 950)/2*(2002)

y will be –

y = 0.0041

After doing the above math (check excel template), we have the value of y as 0.0041.

Example# 2

Sunita is a runner and is preparing for the upcoming Olympics, and she wants to determine that the race she is going to run has perfect timing calculation as a split delay can cause her the gold in Olympics.

Her brother is a statistician, and he noted that the mean timing of her sister is 10.33 seconds, whereas the standard deviation of her timing is 0.57 seconds, which is quite risky as such split delay can cause her to win gold in the Olympics.

Using the bell-shaped curve equation, what is the probability of Sunita completing the race in 10.22 seconds?

Solution:

Use the following data for the calculation.

First, we are given all the values, i.e., mean as 10.33 seconds, standard deviation as 0.57 seconds, and x as 10.22. We just need to plug in the figures in the formula and try to calculate the y.

Popular Course in this categoryAll in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
4.9 (1,067 ratings) 250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion

The formula for Bell Curve as per below:

y = 1/(0.57√2*3.14159)e-(850 – 950)/2*(2002)

y will be –

y = 0.7045

After doing the above math (check excel template), we have the value of y as 0.7045.

Example# 3

Hari-baktii limited is an audit firm.

It has recently received a statutory audit of ABC bank, and they have noted that in the last few audits, they have picked up an incorrect sample which was giving misrepresentation of the population for example, in the case of receivable, the sample they picked up was depicting that receivable was genuine but later it was discovered that receivable population had many dummy entries.

So now, they are trying to analyze what is the probability of picking up the bad sample, which would generalize the population as correct even though the sample was not a correct representation of that population. They have an article assistant who is good at statistics, and recently he has learned about the bell curve equation.

So, he decides to use that formula to find the probability of picking up at least seven incorrect samples. He went into the history of the firm and found that the average incorrect sample they collect from a population is between 5 to 10, and the standard deviation is 2.

Solution:

Use the following data for the calculation.

First, we need to take the average of the two numbers given, i.e., for mean as (5+10)/2, which is 7.50, standard deviation as 2 and x as 7, we just need to plug in the figures in the formula and try to calculate the y.

The formula for Bell Curve as per below:

y = 1/(2√2*3.14159)e-(7 – 7.5)/2*(22)

y will be –

y = 0.2096

After doing the above math (check excel template), we have the value of y as 0.2096

So, there is a 21% chance that this time also they could take 7 incorrect samples in the audit.

Relevance and Uses

This function will be used to describe the events which are physical, i.e., the number of events is humongous.

In simple words, one may not be able to predict what the outcome of the item will perform if there are a whole ton of observations, but one shall be able to predict what those shall do a whole.

Take an example, suppose one has a gas jar at a constant temperature, the normal distribution, or the bell curve will enable that person to figure out the probability of one particle which shall move at a specific velocity.

The financial analyst will often use the normal probability distribution or say the bell curve while analyzing the returns of overall market sensitivity or security.

E.g., stocks which display a bell curve are usually the blue-chip ones, and those shall have the lower volatility and often more behavioral patterns which shall be predictable. Hence, they make use of the normal probability distribution or bell curve of a stock’s previous returns to make assumptions about the expected returns.

Все термины
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: