# NORM.INV Function in Excel

Part 1: Introduction to the NORM.INV Function in Microsoft Excel

Definition & Purpose

The `NORM.INV` function in Excel is a statistical function that returns the inverse of the normal cumulative distribution for a specified mean and standard deviation. This function is commonly used in statistics when dealing with normal distributions.

Syntax & Arguments

The syntax for the `NORM.INV` function is as follows:

syntax
```NORM.INV(probability, mean, standard_dev) ```

The function has the following arguments:

• `probability`: This is a required argument. It represents a probability corresponding to the normal distribution.
• `mean`: This is also a required argument. It represents the arithmetic mean of the distribution.
• `standard_dev`: This is the third argument needed. It represents the standard deviation of the distribution.

Return Value

The `NORM.INV` function returns a numeric value, the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Remarks

• If any argument is non-numeric, `NORM.INV` returns the `#VALUE!` error value.
• If `probability` is less than or equal to 0 or greater than or equal to 1, `NORM.INV` returns the `#NUM!` error value.
• If `standard_dev` is less than or equal to 0, `NORM.INV` returns the `#NUM!` error value.
• If `mean` equals 0 and `standard_dev` equals 1, `NORM.INV` uses the standard normal distribution (see `NORMS.INV`).
• Given a value for `probability`, `NORM.INV` seeks that value `x` such that `NORM.DIST(x, mean, standard_dev, TRUE) = probability`. Thus, the precision  `NORM.INV` depends on the accuracy of `NORM.DIST`.

Part 2: Examples of Using the NORM.INV Function in Business

Example 1

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed.

ABC
1ProbabilityMean SalesStd Dev
20.90500007000
3`=NORM.INV(A2, B2, C2)`

Explanation: In this example, we have a mean sales value of \$50,000 and a standard deviation of \$7,000. We want to determine the sales target the top 10% of salespeople will exceed. We use the `NORM.INV` function with a probability of 0.90 (since we’re looking at the top 10%, the corresponding possibility is 1 – 0.10 = 0.90). The result of the formula will give us the sales target.

Example 2

Purpose of Example: To calculate the minimum test score that the top 5% of students will exceed.

ABC
1ProbabilityMean ScoreStd Dev
20.957010
3`=NORM.INV(A2, B2, C2)`

Explanation: We have a mean test score of 70 and a standard deviation of 10 in this example. We want to determine the minimum test score the top 5% of students will exceed. We use the `NORM.INV` function with a probability of 0.95 (since we’re looking at the top 5%, the corresponding possibility is 1 – 0.05 = 0.95). The result of the formula will give us the minimum test score.

Example 3

Purpose of Example: To calculate the weight that the bottom 15% of a product’s weight will be less than.

ABC
1ProbabilityMean WeightStd Dev
20.151.50.2
3`=NORM.INV(A2, B2, C2)`

Explanation: In this example, we have a mean weight of 1.5 kg and a standard deviation of 0.2 kg. We want to determine the importance that the bottom 15% of the product will be less than. We use the `NORM.INV` function with a probability of 0.15. The result of the formula will give us the importance.

Example 4

Purpose of Example: To calculate the sales target that the bottom 25% of salespeople will not reach.

ABC
1ProbabilityMean SalesStd Dev
20.25500007000
3`=NORM.INV(A2, B2, C2)`

Explanation: In this example, we have a mean sales value of \$50,000 and a standard deviation of \$7,000. We want to determine the sales target the bottom 25% of salespeople will not reach. We use the `NORM.INV` function with a probability of 0.25. The result of the formula will give us the sales target.

Example 5

Purpose of Example: To calculate the minimum test score that the bottom 20% of students will not reach.

ABC
1ProbabilityMean ScoreStd Dev
20.207010
3`=NORM.INV(A2, B2, C2)`

Explanation: We have a mean test score of 70 and a standard deviation of 10 in this example. We want to determine the minimum test score the bottom 20% of students will not reach. We use the `NORM.INV` function with a probability of 0.20. The result of the formula will give us the minimum test score.

Example 6

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and return a custom message if the target is unrealistic.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=IF(NORM.INV(A2, B2, C2)>70000, "Target not realistic", NORM.INV(A2, B2, C2))`60000

Explanation: In this example, we use the `IF` function nested with the `NORM.INV` function. If the sales target that the top 10% of salespeople will exceed exceeds \$70,000, the formula will return the message “Target not realistic”. Otherwise, it will replace the sales target.

Example 7

Purpose of Example: To calculate the total sales target that the top 10%, 20%, and 30% of salespeople will exceed.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=SUM(NORM.INV(A2, B2, C2), NORM.INV(A2+0.1, B2, C2), NORM.INV(A2+0.2, B2, C2))`180000

Explanation: In this example, we use the `SUM` function nested with the `NORM.INV` function to calculate the total sales target that the top 10%, 20%, and 30% of salespeople will exceed.

Example 8

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and look up the corresponding commission rate.

ABCDEFG
1ProbabilityMean SalesStd DevFormulaResultCommission Table
20.90500007000`=NORM.INV(A2, B2, C2)`60000Sales TargetCommission Rate
3`=VLOOKUP(D2, F2:G4, 2, TRUE)`10%500008%
46000010%
57000012%

Explanation: In this example, we use the `VLOOKUP` function nested with the `NORM.INV` function. We calculate the sales target that the top 10% of salespeople will exceed and then look up the corresponding commission rate from the commission table. The commission rate for a sales target of 60000 is 10%.

Example 9

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and round the result to the nearest hundred.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=ROUND(NORM.INV(A2, B2, C2), -2)`60000

Explanation: In this example, we use the `ROUND` function nested with the `NORM.INV` function to calculate the sales target that the top 10% of salespeople will exceed and round the result to the nearest hundred. The rounded sales target is 60000.

Example 10

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and return the absolute value of the result.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=ABS(NORM.INV(A2, B2, C2))`60000

Explanation: In this example, we use the `ABS` function nested with the `NORM.INV` function to calculate the sales target that the top 10% of salespeople will exceed and return the absolute value of the result. The total value of the sales target is 60000.

Example 11

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and return the integer part of the result.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=INT(NORM.INV(A2, B2, C2))`60000

Explanation: In this example, we use the `INT` function nested with the `NORM.INV` function to calculate the sales target that the top 10% of salespeople will exceed and return the integer part of the result. The integer part of the sales target is 60000.

Example 12

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and return the result raised to the power of 2.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=POWER(NORM.INV(A2, B2, C2), 2)`3600000000

Explanation: In this example, we use the `POWER` function nested with the `NORM.INV` function to calculate the sales target that the top 10% of salespeople will exceed and return the result raised to the power of 2. The sales target raised to the power of 2 is 3600000000.

Example 13

Purpose of Example: To calculate the sales target that the top 10% of salespeople will exceed and return the result as a text string.

ABCDE
1ProbabilityMean SalesStd DevFormulaResult
20.90500007000`=TEXT(NORM.INV(A2, B2, C2), "0.00")`“60000.00”

Explanation: In this example, we use the `TEXT` function nested with the `NORM.INV` function to calculate the sales target that the top 10% of salespeople will exceed and return the result as a text string. The sales target as a text string is “60000.00”.

Part 3: Tips and Tricks

1. Always ensure that the `probability` argument is between 0 and 1. Any value outside this range will result in an error.
2. The `mean` and `standard_dev` arguments should be based on your data set. Incorrect values can lead to misleading results.
3. The `NORM.INV` function is handy when dealing with normally distributed data. If your data is not normally distributed, consider using other statistical functions.
4. Remember that the precision of `NORM.INV` depends on the accuracy of `NORM.DIST`.
5. Be aware of the potential for errors. If any argument is non-numeric, `NORM.INV` it will return the `#VALUE!` error value. It `probability` is less than or equal to 0 or greater than or equal to 1