NORM.S.INV Function

Excel is one of the most widely used spreadsheet software tools developed by Microsoft. It helps to get a more visual representation of data through charts and graphs.

Excel provides many functions to simplify complex mathematical, algebraic, and arithmetic operations, which makes Excel very user-friendly and an effective tool for working professionals to manage data.

Excel has many financial functions, like the PMT, PPMT, and IPMT functions, which have their applications. In addition, excel also provides conditional functions like the IF function.

However, we sometimes require Statistical functions in Excel when dealing with a probability distribution and its statistical implications. The need for a command depends on our use case and the data we are working with.

One such statistical function we will discuss in this article is the NORM.S.INV function. This function calculates the inverse normal cumulative distribution for any given probability.

The normal distribution is a form of probability distribution in statistics that exhibits symmetry around the mean and demonstrates that data close to the distribution's arithmetic mean occur more frequently than data far from the arithmetic mean.

Graphically, a normal distribution appears as a bell curve. It is a symmetrical distribution.

The normal distribution is also known as the Gaussian distribution. The normal distribution of any random variable x is a continuous function whose probability density function(pdf) is given by:

Here,

• x is the unknown data point or the random variable,
• µ is the mean of the distribution, and
• σ is the standard deviation of the distribution, which shows the variation or dispersion in the data set.

A high standard deviation refers to more widely spread data, while a low standard deviation refers to data closer to the distribution’s mean.

NORM.S.INV Function in Excel

The inverse of a normal distribution is a reverse process of normal distribution, which calculates the value of the unknown data or the random variable from a known probability.

The probability density function is calculated for a given random variable x, with a known mean and standard deviation in a normal distribution.

Microsoft introduced the NORM.S.INV function in MS Excel 2010. It is an upgraded version of the NORMSINV function and falls under the category of financial functions in Excel.

Let us take a practical example to understand and appreciate the use of the NORM.S.INV function in Excel.

1. Suppose there is a factory manufacturing washing machines. The factory owners want to investigate the failure times of the rotating part of the washing machines to determine the appropriate warranty period.
2. The failure time of the rotor of the washing machines follows a normal distribution, with a mean of 8,000 hours and a standard deviation of 200 hours.
3. The plant processors can use the NORM.S.INV function to find the time by which 10% of the rotors fail, the times between which 90% of all the rotors fail, and the time at which only 10% of the rotors are functioning.
4. Assuming the data set of the times of failure of the rotors follows a normal distribution, with a mean of 8,000 and a standard deviation of 200, the time by which 10% of the rotors of the machines would fail is the inverse cumulative distribution function of (10/100) or 0.10.

The formula or syntax of the NORM.S.INV function in Excel is as follows:

=NORM.S.INV(probability)

The NORM.S.INV function requires only one argument to make the computations. Here the argument probability refers to the probability corresponding to the normal distribution. It signifies the value of the random variable at which one wants to obtain the inverse distribution.

The probability should always lie between 0 and 1 for the probability distribution to be valid and realizable. If the probability is less than 0 or more than 1, the NORM.S.INV function returns the #NUM! Error value.

Calculation of the Inverse Normal Cumulative Distribution in Excel Using the NORM.S.INV Function

Having seen the theoretical aspects and the formula of the NORM.S.INV function, let us now see some examples to appreciate the applications of the NORM.S.INV function in Excel.

Suppose the probability of a given data set is 0.84. Then, we intend to obtain the inverse of the standard normal cumulative distribution for the given probability.

To achieve this, we use the NORM.S.INV function in Excel. As seen earlier, this function requires only one argument to compute: the probability corresponding to the distribution.

Given the probability of the data set is 0.84, as illustrated below:

Cell C2 in the spreadsheet above shows the probability corresponding to the distribution. We need to calculate the inverse normal cumulative distribution for the above data. We can achieve this by using the following formula:

=NORM.S.INV(C2)

By applying the above formula, in cell C4, we get the desired result, the inverse of the normal cumulative distribution, as shown below:

Hence, the inverse distribution for a probability of 0.84 is calculated as 0.99445788. Using the NORM.S.INV function in Excel, we can obtain the inverse of the cumulative normal distribution for any given probability. 

NORM.S.INV vs. NORMINV Function in Excel

The NORM.S.INV and the NORMINV functions have been classified as statistical functions in Excel. This is because they are designed to work with data that follows a normal distribution.

Both these functions evaluate the inverse of the normal cumulative distribution. However, the distinction lies in the type of distribution they operate on. The NORM.S.INV function returns the inverse of a standard normal distribution, while the NORMINV function returns the inverse of a generalized normal distribution.

A standard normal distribution is a simplified form of the normal distribution function. For example, when the mean of the distribution comes to 0, and the standard deviation becomes 1, it is called a standard normal distribution.

The probability density function of a normal distribution is as follows:

Here,

• x is the unknown data point or the random variable,
• µ is the mean of the distribution, and
• σ is the standard deviation of the distribution, showing the variation or dispersion in the data set.

The probability density function for a standard normal distribution is obtained by setting the mean(µ) equal to 0 and the standard deviation(σ) equal to 1 in the formula above.

Hence, the standard normal distribution for a continuous random variable x is as follows:

So, the NORMINV function requires three arguments to do the computations. They are:

• The probability corresponding to the distribution
• The mean of the distribution
• The standard deviation of the distribution

On the other hand, the NORM.S.INV function requires only one argument to do the computations, the probability corresponding to the distribution.

The formula for using the NORMINV function in Excel is as follows:

=NORMINV(probability, mean, standard_dev)

where the arguments have the same meanings as mentioned above.

The formula for using the NORM.S.INV function in Excel is as follows:

=NORM.S.INV(probability)

Let's consider an example to understand the difference between the two functions. Suppose we have a hypothetical normal distribution for a continuous random variable x. Then, the probability corresponding to the distribution is 0.51, the mean of the distribution is 30, and the standard deviation is 2.1.

The data looks as illustrated below:

Using the NORMINV function, the following formula can be used to obtain the inverse of the normal distribution:

=NORMINV(C2, C3, C4)

By applying this formula, we obtain the following result in cell C6:

If the mean of the above distribution becomes 0 and the standard deviation comes to 1, it becomes a standard normal distribution.

To calculate the inverse of the cumulative normal distribution, using the NORM.S.INV function, we use the following formula:

=NORM.S.INV(C2)

By applying this formula in cell C7, we get the following result:

Hence, we can see that the inverse of the standard normal distribution, calculated by the NORM.S.INV function, is less when compared to the inverse of the normal distribution, calculated by the NORMINV function.

Researched and Authored by Devang Shekhar | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Inverse Lognormal Distribution Excel
• ISBLANK Function
• LEN Function
• LOGNORM.DIST Function
• VALUE Function