Syntax. NORM.S.INV(probability)
Definition. This function returns the quantile of the standard normal distribution. The standard normal distribution has a mean of 0 and a standard deviation of 1.
Arguments
probability (required). A probability associated with the standard normal distribution
Note
If probability isn’t a numeric expression, the NORM.S.INV() function returns the #VALUE! error.
If probability is less than 0 or greater than 1, the NORM.S.INV() function returns the #NUM! error. If probability has a value, NORM.S.INV() looks for the value z so that NORMSDIST(z) = probability.
Therefore, the accuracy of NORM.S.INV() depends on the accuracy of NORM.S.DIST(). NORM.S.INV() uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error.
Background. The NORM.S.INV() function is the inverse function of the NORM.S.DIST() function and returns a standard normal distributed random variable or the z-value of a standard normal distribution associated with a probability.
NORM.S.INV() calculates the position of z on the horizontal axis that matches the cumulative area ratio of the standard normal distribution.
See Also
You will find more information about (standard) normal distributions in the descriptions of NORM.DIST(), STANDARDIZE(), and NORM.S.DIST().
Example. For NORM.S.INV() we use the same example as for STANDARDIZE() and NORM.S.DIST(). You are a light bulb manufacturer and want to analyze the performance of light bulbs. You already entered the measurements in an Excel table. You also calculated the average life cycle and the associated standard deviation, as well as the probabilities for all calculated performance values (see Figure 12-107).
You want to calculate the standard normal distributed x-values (variables) for the probabilities with the NORM.S.INV() function. What arguments does this function require?
probability = the probability associated with the standard normal distribution (column D)
Figure 12-108 shows the result.
The NORM.S.INV() function calculates the standard normal distributed values of the distribution based on a given probability.