How to use the NORM.INV function
What is the NORM.INV function?
The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.
Table of Contents
1. Introduction
What is the NORM.DIST function?
The NORM.DIST function calculates the normal distribution for a given mean and standard deviation.
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 standard 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 cumulative 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.INV function Syntax
NORM.INV(probability, mean, standard_dev)
3. NORM.INV function Arguments
| probability | Required. A probability corresponding to the normal distribution. |
| mean | Required. The average of the distribution. |
| standard_dev | Required. The standard deviation of the distribution. |
4. NORM.INV Function Example 1
The heights of a certain species of tree follow a normal distribution with a mean of 25 meters and a standard deviation of 2 meters. If a researcher wants to study the tallest 10% of trees, what is the minimum height they should consider?
The arguments are:
- probability = 0.9
- mean = 25 m
- standard_dev = 2 m
These arguments are specified in cells C17,C18,and C19 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 25 and the standard deviation is 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 27.56 meters which represents the minimum height of the 10% tallest trees.
In the image above, locate the value 0.9 on the y-axis. From that point, draw an imaginary horizontal line until it intersects with the black curve, which represents the cumulative distribution function. Then, follow the point of intersection vertically towards the x-axis below. You will find that the corresponding value on the x-axis is approximately 27.5.
5. NORM.INV Function Example 2
The daily returns of a particular stock are normally distributed with a mean of 0.05% and a standard deviation of 1.2%. If an investor wants to know the daily return value that corresponds to the 10th percentile of the distribution, what is that value?
The arguments are:
- probability = 0.9 (10th percentile)
- mean = 0.05 %
- standard_dev = 1.2 %
These arguments are specified in cells C17,C18,and C19 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 1.588 % which represents the daily return value of the 10th percentile.
In the image above, locate the value 0.9 on the right y-axis. From that point, draw an imaginary horizontal line until it intersects with the black curve, which represents the cumulative distribution function. Then, follow the point of intersection vertically towards the x-axis below. You will find that the corresponding value on the x-axis is approximately 1.58.
6. NORM.INV Function Example 3
The concentration of a certain pollutant near a specific factory follows a normal distribution with a mean of 15 parts per billion (ppb) and a standard deviation of 3 ppb. If the regulatory agency wants to set a limit such that only 5% of the samples exceed that limit, what should the limit be?
The arguments are:
- probability = 0.05
- mean = 15 ppb
- standard_dev = 3 ppb
These arguments are specified in cells C17,C18,and C19 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 15 and the standard deviation is 3. 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 10.07 which represents the limit that only 5% of the samples exceed.
In the image above, locate the value 0.05 on the right y-axis. From that point, draw an imaginary horizontal line until it intersects with the black curve, which represents the cumulative distribution function. Then, follow the point of intersection vertically towards the x-axis below. You will find that the corresponding value on the x-axis is approximately 10.
7. NORM.INV function not working
NORM.INV returns
- #VALUE! if argument is non-numeric.
- #NUM! if standard_dev ≤ 0
- #NUM! if probability <= 0 or if probability >= 1
- the standard normal distribution if mean = 0 and standard_dev = 1
Functions in 'Statistical' category
The NORM.INV 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