The `NORM.INV` function in Excel is used to find the inverse of the normal cumulative distribution for a specified mean and standard deviation. It essentially returns the value `x` such that the probability of the variable being less than `x` is equal to a given probability. This is useful in statistical analysis when you need to find the value corresponding to a particular percentile in a normal distribution.
Here’s how to use the `NORM.INV` function in Excel:
Syntax
NORM.INV(probability, mean, standard_dev)
- probability: This is the probability corresponding to the normal distribution; a value between 0 and 1.
- mean: The mean (average) of the normal distribution.
- standard_dev: The standard deviation of the normal distribution.
Example
Suppose you want to find the value at which 90% of the distribution falls below for a normal distribution with a mean of 100 and a standard deviation of 15.
=NORM.INV(0.9, 100, 15)
- In an Excel spreadsheet, click on the cell where you want the result to appear.
- Enter the formula:
- Press `Enter`.
Explanation
In this example:
- `0.9` is the probability (90th percentile).
- `100` is the mean of the distribution.
- `15` is the standard deviation.
After entering the formula, Excel will calculate and display the value `x` such that there’s a 90% chance a randomly selected point from the distribution is less than or equal to `x`.
Important Notes
- The `NORM.INV` function is available in Excel 2010 and later versions. In earlier versions, the function to use was `NORMINV` (without the dot).
- Ensure that the standard deviation is positive; otherwise, the function will return an error.
- The probability should be between 0 and 1; otherwise, Excel will return an error.
Use Cases
- Quality Control: Determine the threshold for outliers in a process.
- Risk Management: Calculate value-at-risk in finance.
- Data Analysis: Find cut-off points for test scores or survey results.
Using the `NORM.INV` function helps in making informed decisions based on statistical insights derived from normal distributions.
Unlock Your Potential
Excel
Access
Power BI
