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:
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.INVreturns the#VALUE!error value. - If
probabilityis less than or equal to 0 or greater than or equal to 1,NORM.INVreturns the#NUM!error value. - If
standard_devis less than or equal to 0,NORM.INVreturns the#NUM!error value. - If
meanequals 0 andstandard_devequals 1,NORM.INVuses the standard normal distribution (seeNORMS.INV). - Given a value for
probability,NORM.INVseeks that valuexsuch thatNORM.DIST(x, mean, standard_dev, TRUE) = probability. Thus, the precisionNORM.INVdepends on the accuracy ofNORM.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.
| A | B | C | |
|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev |
| 2 | 0.90 | 50000 | 7000 |
| 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.
| A | B | C | |
|---|---|---|---|
| 1 | Probability | Mean Score | Std Dev |
| 2 | 0.95 | 70 | 10 |
| 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.
| A | B | C | |
|---|---|---|---|
| 1 | Probability | Mean Weight | Std Dev |
| 2 | 0.15 | 1.5 | 0.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.
| A | B | C | |
|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev |
| 2 | 0.25 | 50000 | 7000 |
| 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.
| A | B | C | |
|---|---|---|---|
| 1 | Probability | Mean Score | Std Dev |
| 2 | 0.20 | 70 | 10 |
| 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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result | Commission Table | |
| 2 | 0.90 | 50000 | 7000 | =NORM.INV(A2, B2, C2) | 60000 | Sales Target | Commission Rate |
| 3 | =VLOOKUP(D2, F2:G4, 2, TRUE) | 10% | 50000 | 8% | |||
| 4 | 60000 | 10% | |||||
| 5 | 70000 | 12% |
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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Probability | Mean Sales | Std Dev | Formula | Result |
| 2 | 0.90 | 50000 | 7000 | =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
- Always ensure that the
probabilityargument is between 0 and 1. Any value outside this range will result in an error. - The
meanandstandard_devarguments should be based on your data set. Incorrect values can lead to misleading results. - The
NORM.INVfunction is handy when dealing with normally distributed data. If your data is not normally distributed, consider using other statistical functions. - Remember that the precision of
NORM.INVdepends on the accuracy ofNORM.DIST. - Be aware of the potential for errors. If any argument is non-numeric,
NORM.INVit will return the#VALUE!error value. Itprobabilityis less than or equal to 0 or greater than or equal to 1