NORM.S.INV function

The NORM.S.INV function in Excel returns the inverse of the standard normal cumulative distribution. This means it finds the z-score (the number of standard deviations away from the mean) corresponding to a given cumulative probability in a standard normal distribution. Essentially, it helps you find the value (z-score) at a given cumulative probability for a standard normal distribution (with a mean of 0 and a standard deviation of 1).

Syntax:

NORM.S.INV(probability)

Arguments:

• probability: Required. The cumulative probability associated with the normal distribution. It must be a value between 0 and 1, representing the area under the curve to the left of the z-score you want to find. For example, a probability of 0.95 corresponds to the z-score for which 95% of the distribution lies to the left.

Output:

• The function returns the z-score corresponding to the given probability in a standard normal distribution. This is the number of standard deviations away from the mean that corresponds to the given cumulative probability.

How It Works:

• The standard normal distribution has a mean of 0 and a standard deviation of 1.
• The function uses the cumulative probability you provide to return the z-score. For instance, if you provide a probability of 0.95, NORM.S.INV will return the z-score corresponding to the 95th percentile in the standard normal distribution.
• This function is often used in statistical hypothesis testing and in confidence interval calculations.

Example 1: Finding the Z-Score for a Given Probability

Suppose you want to find the z-score that corresponds to the 95th percentile (a cumulative probability of 0.95) in the standard normal distribution.

Use the formula:

=NORM.S.INV(0.95)

This will return the z-score that corresponds to a cumulative probability of 0.95 in a standard normal distribution. The result will be approximately 1.645, meaning that the value at the 95th percentile is 1.645 standard deviations above the mean.

Example 2: Finding the Z-Score for the 50th Percentile (Median)

If you want to find the z-score for the 50th percentile (which corresponds to a cumulative probability of 0.5) in the standard normal distribution:

Use the formula:

=NORM.S.INV(0.5)

This will return 0, as the 50th percentile (median) of the standard normal distribution corresponds to the mean, which is 0.

Example 3: Finding the Z-Score for a Low Probability (1st Percentile)

If you want to find the z-score for the 1st percentile (cumulative probability of 0.01) in the standard normal distribution:

Use the formula:

=NORM.S.INV(0.01)

This will return approximately -2.33, indicating that the 1st percentile lies about 2.33 standard deviations below the mean of the standard normal distribution.

Key Points:

• NORM.S.INV is used to find the z-score corresponding to a given cumulative probability in a standard normal distribution.
• The probability must be between 0 and 1, and it represents the cumulative probability (area under the curve to the left of the z-score).
• The function is useful in hypothesis testing, confidence intervals, and other statistical analyses that involve the standard normal distribution.

Use Cases:

• Hypothesis testing: Find the critical z-scores for one-tailed or two-tailed tests based on significance levels (e.g., 0.05 or 0.01).
• Confidence intervals: Calculate z-scores for constructing confidence intervals around a sample mean, particularly when the population is assumed to be normally distributed.
• Risk management: Calculate z-scores to determine value-at-risk (VaR) in financial analysis using a standard normal distribution.

Notes:

• NORM.S.INV is used for the standard normal distribution, which has a mean of 0 and a standard deviation of 1. If you’re working with a normal distribution that has a different mean and standard deviation, use NORM.INV instead.