**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.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.

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
`probability`

argument is between 0 and 1. Any value outside this range will result in an error. - The
`mean`

and`standard_dev`

arguments should be based on your data set. Incorrect values can lead to misleading results. - The
`NORM.INV`

function 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.INV`

depends on the accuracy of`NORM.DIST`

. - 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