Excel NORM.S.DIST Function
- z - Numeric z-score value.
- cumulative - Logical value that determines the form of the function.
The NORM.S.DIST function returns the standard normal cumulative distribution function (CDF) when the cumulative flag is set to 1. For example, NORM.S.DIST(1,TRUE) returns the value 0.8413 which represents the probability of an event occurring below the threshold of a z-score value of 1.
NORM.S.DIST expects Standardized Input
NORM.S.DIST expects standardized input in the form of a z-score value. A z-score value represents how far a value is from the mean of a distribution in terms of the standard deviation of the distribution. To calculate the z-score, subtract the mean from the value and then divide by the standard deviation or use the STANDARDIZE function as show in the two formulas below:
=(x-mean)/standard_deviation // calculates z-score
=STANDARDIZE(x, mean, standard_deviation) // calculates z-score
The cumulative flag determines which distribution function is used within the function. If the flag is set to true, the standard normal CDF is returned whose output is visualized in the graph below:
If the flag is set to false, the standard normal probability density function (PDF) is returned. The output of the standard normal CDF is equal to the area under the curve to the left of a value on the PDF as shown in the graph below. For example, with an input of 1 and the cumulative flag set to false the function returns 0.242. For the same input, with the cumulative flag set to TRUE, the function returns the area to the left of 1 on the normal bell shaped curve.
The standard normal distribution is a probability density function, where the area under the curve represents the probability of an event between two "standardized" values. Probability density functions model problems concerning continuous ranges. For example, the probability of a student scoring exactly 93.41% on a test is very unlikely. Instead, it is reasonable to compute the probability of the student scoring between 90% and 95% on the test. A probability density functions is a good choice to model these types of problems.
Calculating Probability Below a Threshold
To calculate the probability of an event occurring below the z-score value b the formula would be:
=NORM.S.DIST(b, TRUE)// Returns probability x less than b
Calculating Probability Above a Threshold
To calculate the probability of an event occurring above the z-score value a the formula would be:
=1-NORM.S.DIST(a, TRUE)// Returns probability x greater than a
Calculating Probability Between Thresholds
To calculate the probability of an event occurring above a and below b, where b is greater than a, the formula is:
NORM.S.DIST versus NORM.DIST
The difference between the functions NORM.DIST and NORM.S.DIST is NORM.S.DIST uses the standard normal distribution which is a special case of the normal distribution where the mean is 0 and the standard deviation is 1.
When the cumulative flag is set to 0 or FALSE, the functions return the respective points along the distributions.
When the cumulative flag is set to TRUE and the input to NORM.S.DIST is standardized (discussed above), the output of the two functions is the same.
=NORM.DIST(x, mean, standard_deviation, TRUE)
One way to visualize the relationship between the two functions is to highlight the relative areas, divided by standard deviations, underneath the standard normal distribution and a more general normal distribution with a mean of 0 and a standard deviation of 1. This is shown in the graphic below:
Images courtesy of wumbo.net.