NORM.INV function

The NORM.INV function in Excel returns the inverse of the cumulative distribution function (CDF) for the normal distribution. This function is used to find the value (or percentile) associated with a given cumulative probability for a normally distributed dataset. Essentially, it helps you find the value at a given probability level of a normal distribution.

Syntax:

NORM.INV(probability, mean, standard_dev)

Arguments:

• probability: Required. A probability value between 0 and 1. This is the cumulative probability for which you want to find the corresponding value from the normal distribution. It represents the area under the curve to the left of the value.
• mean: Required. The mean (average) of the normal distribution.
• standard_dev: Required. The standard deviation of the normal distribution. It must be a positive number.

Output:

The function returns the value corresponding to the given cumulative probability in the normal distribution with the specified mean and standard deviation.

How It Works:

• NORM.INV finds the value for which the cumulative probability (area under the normal distribution curve) is equal to the probability you provide. It “inverts” the cumulative distribution to give you the corresponding value.
• This is useful when you need to determine the threshold value for a given cumulative probability.

Example 1: Finding a Value at a Given Probability

Suppose you have a normal distribution with a mean of 50 and a standard deviation of 10, and you want to find the value that corresponds to the cumulative probability of 0.95 (i.e., the 95th percentile).

Use the formula:

=NORM.INV(0.95, 50, 10)

This will return the value in the normal distribution that has a cumulative probability of 0.95, which is typically the 95th percentile. It represents the value below which 95% of the data lies.

Example 2: Finding the Median (50th Percentile)

If you want to find the value at the 50th percentile (the median) for a normal distribution with a mean of 100 and a standard deviation of 15, use the formula:

=NORM.INV(0.5, 100, 15)

Since the 50th percentile corresponds to the mean of the normal distribution, the result will be 100, which is the center of the distribution.

Example 3: Finding a Value for a Lower Probability

If you want to find the value for the 5th percentile (a cumulative probability of 0.05) in a normal distribution with a mean of 75 and a standard deviation of 10, use the formula:

=NORM.INV(0.05, 75, 10)

This will return the value below which 5% of the data lies.

Key Points:

• NORM.INV is used to find the inverse of the cumulative probability in a normal distribution. It returns the value associated with a given probability.
• The function is useful when you need to determine a specific value or cutoff point for a given cumulative probability, such as in statistical analysis, risk assessment, and quality control.
• The cumulative probability must be between 0 and 1 (inclusive).

Use Cases:

• Statistics: Find percentiles (e.g., 95th percentile, 5th percentile) for normally distributed data.
• Finance: Estimate the value at risk (VaR) for financial returns using normal distributions.
• Quality control: Determine the tolerance limits or the cutoff value beyond which an outcome is considered a defect.
• Risk management: Calculate thresholds for decision-making, such as the likelihood of an event occurring above a certain level.

Notes:

• NORM.INV was introduced in Excel 2010 and is available in later versions. In earlier versions, the function was named NORMINV.
• Ensure that the mean and standard deviation you use in the formula are appropriate for your specific dataset or analysis.