How to use the NORM.DIST function
What is the NORM.DIST function?
The NORM.DIST function calculates the normal distribution for a given mean and standard deviation.
Table of Contents
1. Introduction
What is a normal distribution?
The normal distribution is a symmetric bell-shaped probability distribution described by its mean and standard deviation. Used by many to model a plethora of natural phenomena and represent unknown processes.
What is the mean?
It is also known as the average. It is calculated by adding up all the values in the data set and dividing by the number of values.
For example, if you have a data set of 5, 7, 9, 11, and 13, the mean is (5 + 7 + 9 + 11 + 13) / 5 = 9.
What is deviation?
In statistics, deviation is a measure of how far each value in a data set lies from the mean (the average of all values). A high deviation means that the values are spread out widely, while a low deviation means that they are clustered closely around the mean.
What is standard deviation?
Standard deviation measures dispersion from the mean by taking the square root of the average of squared deviations, useful for assessing variability and spread in data.
What is the difference between deviation and standard deviation?
- Deviation is the difference between an individual data point and the mean.
- Standard deviation measures the variation across all deviations by using the square root of the average squared deviation.
What is the difference between the NORM.DIST function vs NORM.INV function?
NORM.INV function returns the inverse of the normal cumulative distribution for a given mean and standard deviation.
NORM.INV(probability, mean, standard_dev)
The NORM.DIST function calculates the normal distribution for a given mean and standard deviation.
NORM.DIST(x, mean, standard_dev, cumulative)
For example, the chart above demonstrates a normal distribution with a mean of 0 (zero) and standard deviation of 1.
The NORM.DIST(-1,0,1,TRUE) returns 0.158655253931457 which is the orange area below the curve up to x = -1 that represents the cumulative probability.
The NORM.INV(0.158655253931457,0,1) returns -1 which is the x value given the probability of 0.158655253931457
What is a standard normal distribution?
A standard normal distribution is a normal distribution with the mean of 0 (zero) and the standard deviation is 1. You can standardize any normal distribution using the STANDARDIZE function in Excel, it works like this:
z = (x - µ)/σ
z = z-score
µ is the mean.
σ is the standard deviation.
2. NORM.DIST function Syntax
NORM.DIST(x, mean, standard_dev, cumulative)
3. NORM.DIST function Arguments
| x | Required. A number to calculate the distribution for. |
| mean | Required. The average of the distribution. |
| standard_dev | Required. The standard deviation of the distribution. |
| cumulative | Required. A boolean value that determines which distribution the NORM.DIST function returns. TRUE - Cumulative distribution FALSE - Probability mass function |
If mean = 0, standard_dev = 1, and cumulative = TRUE, the standard normal distribution is returned.
If cumulative = TRUE, the formula is calculated the integral from negative infinity to x.
What is the probability mass function?
NORM.DIST with the cumulative parameter set to FALSE returns the value of the probability density function which is the value at the y-axis for a given x-axis value. The image above shows the y value 0.24197 for x-axis value -1.
NORM.DIST(-1,0,1,FALSE) returns approx. 0.24197
NORM.DIST(-1,0,1,TRUE) returns approx. .15866 which is the integral from negative infinity to -1
The image above shows the integral from negative infinity to x axis value -1.
4. NORM.DIST function Example 1
In a manufacturing process, the diameter of a particular component follows a normal distribution with a mean of 10 mm and a standard deviation of 0.2 mm. What is the probability that a randomly selected component will have a diameter less than 9.8 mm?
The arguments are:
- x = 9.8 mm
- mean = 10 mm
- standard_dev = 0.2
- Cumulative = True
These arguments are specified in cells C17,C18,C19,and C20 respectively, in the image above.
The image above shows a chart containing a blue curve representing the probability mass function of a normal distribution where the mean is 10 and the standard deviation is 0.2. The black curve represents the cumulative distribution based on the same mean and standard deviation as described above.
Formula in cell C22:
The formula returns 0.159 which represents the area below the blue curve between 0 (zero) and x= 9.8.
In the image above, locate the value 9.8 on the x-axis. From that point, draw an imaginary vertical line upwards until it intersects with the black curve, which represents the cumulative distribution function. Then, follow the point of intersection horizontally towards the y-axis to the right. You will find that the corresponding value on the y-axis is approximately 0.159.
5. NORM.DIST Function Example 2
The daily returns of a particular stock follow a normal distribution with a mean of 0.05% and a standard deviation of 1.2%. What is the probability that the daily return will be greater than 2%?
The arguments are:
- x = 2 %
- mean = 0.05 %
- standard_dev = 1.2%
- Cumulative = True
These arguments are specified in cells C17,C18,C19,and C20 respectively, in the image above.
The image above shows a chart containing a blue curve representing the probability mass function of a normal distribution where the mean is 0.05 and the standard deviation is 1.2. The black curve represents the cumulative distribution based on the same mean and standard deviation as described above.
Formula in cell C22:
The formula returns 0.052 (5.2%) which represents the area below the blue curve between x= 2 and infinity.
In the image above, locate the value 2 on the x-axis. From that point, draw an imaginary vertical line upwards until it intersects with the black curve, which represents the cumulative distribution function. Then, follow the point of intersection horizontally towards the y-axis to the right. You will find that the corresponding value on the y-axis is approximately 0.95. This value represents the area below the blue curve between x = 0 and x = 2, however, we need the area below the blue curve from x=2 to x = infinity. We need to calculate the complement to get the correct value.
6. NORM.DIST function not working
The NORM.DIST returns a
- #VALUE! error value if the mean or standard_dev is nonnumeric.
- #NUM! error value if standard_dev ≤ 0
7. How is the NORM.DIST function calculated?
The NORM.DIST function is very useful if you are working with statistics. Here is how the function works in detail (cumulative = FALSE).
µ is the mean.
σ is the standard deviation.
Use the AVERAGE function to calculate the arithmetic mean, used in the second argument in the NORM.DIST function.
Recommended articles
Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]
Use the STDEV.P function to calculate the standard deviation used in the third argument.
Recommended articles
What is the STDEV.P function? The STDEV.P function returns standard deviation based on the entire population. STDEV.P is an abbreviation […]
'NORM.DIST' function examples
The following article has a formula that contains the NORM.DIST function.
Table of Contents How to graph a normal distribution How to build an arrow chart How to graph an equation […]
Functions in 'Statistical' category
The NORM.DIST function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form