NORMINV Function

What is the NORMINV Function?

Excel, developed by Microsoft, is a highly popular spreadsheet software tool widely used in businesses. It is a valuable resource for systematically analyzing, studying, and organizing data. Excel enables users to create charts and graphs, providing a visual representation that enhances data interpretation and communication.

In addition to its mathematical and arithmetic capabilities, Excel offers a wide range of functions that simplify complex mathematical tasks, thereby facilitating work processes within the software.

We already know about some of the financial functions in Excel, like PMT, PPMT, and IPMT functions. However, apart from the financial formulas, we also sometimes need Statistical functions in Excel when dealing with the probability of events and their statistical implications. 

Since its initial release, Microsoft has launched more than 14 versions of Excel. Each new version introduces additional features and capabilities to enhance the software's usability, provide more comprehensive information, and improve clarity. 

These updates aim to optimize the user experience and ensure Excel remains a powerful data analysis and manipulation tool.

The Norminv function is a statistical function in Excel that helps calculate the inverse of the cumulative normal distribution for a given mean and standard deviation. 

Suppose we have a normally distributed random variable denoted by x. Using the Norminv function, we can obtain the value of x corresponding to the bottom 5% of the distribution. This is helpful in understanding the probability associated with a particular value in a normal distribution.

In financial analysis, the Norminv function helps in stock market analysis. For example, this function can help us understand how a portfolio can be affected by any addition or withdrawal made.

NORMINV Function Formula

The formula or syntax for the Norminv function in Excel is given by:

=NORMINV(probability, mean, standard_dev)

The terms in the parenthesis are called functional arguments. These are the arguments or variables which the Norminv function requires to do the computations. The arguments that the Norminv function uses are explained below:

1. Probability

This term 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 function. The probability must be within the range of 0 and 1 for the probability distribution function to be valid.

2. Mean

This term refers to the arithmetic mean of the normal distribution. Mean implies the average value of the distribution. 

3. Standard_dev 

This term refers to the standard deviation of the normal distribution. In statistics, standard deviation signifies the amount of variation or dispersion in a data set.

While a large standard deviation indicates that the data is widely spread, a low standard deviation indicates that the data is closer to the distribution's mean.

The normal distribution of a random variable x is a continuous probability function, whose probability density function (pdf) is given by:

Here, x is the independent variable for which we want to evaluate the function, in other words, the random variable. µ refers to the mean of the normal distribution, and σ^2 is the variance. Hence the square root of the variance(σ) denotes the standard deviation of the distribution.

NORMINV Function Examples

Having looked at the formula and syntax of the Norminv function, let us now consider an example to understand and appreciate the uses of the Norminv function in Excel.

Suppose a hypothetical normal distribution for a random variable x. The probability corresponding to the normal distribution is 0.8671, and the arithmetic mean(µ) of the distribution is 27. The standard deviation(σ) of the distribution is 1.3.

The data looks as illustrated below. The cells C2, C3, and C4 have been filled with the probability corresponding to the distribution, its mean(µ), and its standard deviation(σ), respectively.

To obtain the inverse of the normal cumulative distribution for the data above, we use the following formula:

=NORMINV(C2, C3, C4)

On using the above formula in cell C6, we obtain the following result:

Hence, we get the inverse of the cumulative normal distribution of the random variable x to be 28.44662286.

In this way, we can use the Norminv function in Excel to calculate the inverse of any normal cumulative distribution with known probability, mean, and standard deviation.

Types of Error in NORMINV Function

The Norminv function in Excel may return different error values in specific cases. Here are the possible error values and their explanations:

1. #VALUE! Error Value

If the arguments are non-numeric, like a text or a special character, then the Norminv function will return the #VALUE! Error value. The Norminv function requires numeric arguments for calculations.

Logically, the arguments of the Norminv function, probability, mean, and standard deviation have to be numeric.

2. #N/A Error Value

The Norminv function uses the iterative search approach to evaluate the inverse of any normal distribution. If the search does not converge beyond 100 iterations, the Norminv function returns this error value.

3. #NUM! Error Value

The Norminv function returns this error value in two cases:

a. Case 1

If the probability corresponding to a normal distribution is less than or equal to zero/greater than or equal to one, in other words, if the probability does not lie between 0 and 1(both exclusive), then the Norminv function returns the #NUM! Error value.
In the below example, the Norminv function returns the #NUM! Error value because the probability of the distribution is more than 1.

 b. Case 2

If the standard deviation of the distribution is less than or equal to 0, then the function returns the #NUM! Error value, as shown in the example below 

It is important to ensure that the arguments provided to the Norminv Fn are valid and meet the required criteria to avoid these error values and obtain accurate results.

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:

• CLEAN Function
• INFO Function
• MODE Function
• PPMT Function
• SEARCH Function