NORMSINV is a built-in function in Excel under Statistical Functions that returns the inverse of the standard normal cumulative distribution, which has a zero mean and a standard deviation value “1”.
NORMSINV is the inverse of the function NORMDIST and is compatible with Excel version 2007 and earlier. Newer versions of Excel from 2010 to present features an updated function, NORM.S.INV.
Figure 1. Final result: NORMSINV and NORM.S.INV function
Syntax of NORMSINV and NORM.S.INV
- where probability is greater than zero “0” and less than 1
- When probability<=0 or probability>=1, the function returns the error value #NUM!
- When probability has a non-numeric value, the function returns the error #VALUE!
Calculate the inverse of the standard normal cumulative distribution with a probability of 0.90.
We simply enter the following formulas:
In cell D3:
In cell F3:
Figure 2. Entering the formula
Note that both functions have the same results. When we apply the functions for the succeeding probability values 0.75 and 0.50, the results are shown below.
Figure 3. Output: NORMSINV and NORM.S.INV functions
NORM.S.INV is an updated version of the function NORMSINV and in some key calculations, NORM.S.INV will be able to provide results with greater accuracy.
There are two common errors encountered with these functions:
- A probability value less than or equal to zero, or a value greater than or equal to 1 will result to a #NUM! Error
- A non-numeric value for probability will result to a #VALUE! error
Figure 4. Common errors
NORM.S.INV in menu options
The function can also be accessed through the ribbon under the Formulas tab > More Functions button > Statistical > NORM.S.INV
Figure 5. NORM.S.INV in menu options