In this page, we will talk about what we teach in excel corporate training in Gurgaon.
The normal distribution is a bell shape frequency distribution curve with one single peak. It helps us to find the probability distribution.
To create the normal distribution graph we need two values, means, and standard deviation.
Mean: Where the peak occurs in the normal distribution graph.
Standard Deviation: determine how much wider your distribution is.
All normal distribution curves satisfy the (68-95-99.7% Rule) Empirical Rule.
As per the Empirical Rule:
- 68% of the observations fall within 1 standard deviation of the mean
- 95% of the observations fall within 2 standard deviations of the mean.
- 99.7% of the observations fall within 3 standard deviations of the mean.
Thus, for a normal distribution, almost all values lie within 3 standard deviations of the mean.
Let’s take this example and create normal distribution curve,
On a recent Class test, the scores were normally distributed with a mean of 80 and a standard deviation of 5. So what is the range within which 95% of the scores will lie?
we will use here Empirical rule to calculate the range and NORM.DIST function in excel to create a graph.
- In the attached workbook, we have updated Mean and Standard Deviation value in cell C5 and C6.
- Let’s calculate range where value 95% of will lie by using Empirical in cell G5 and G6, (Mean – 2* Standard Deviation) and (Mean + 2* Standard Deviation).
- Now we will use the NORM.DIST function in cell C9 to calculate curve value
- NORM.DIST function required four arguments. X value, mean, standard deviation and True/False.
- X values we have updated in the B9: B29, mean we have in cell C5 and standard deviation in C6.
- In the fourth argument, we will pass False as we don’t want to calculate normal distribution curve for cumulative value.