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 shown in the two formulas below: Note, see the NORM.DIST function for non-standardized input.
Cumulative Flag
The cumulative flag determines which distribution function is used. If the flag is set to FALSE, the standard normal PDF is used. If the flag is set to TRUE, the standard normal CDF is used. The output of the CDF corresponds to the area under the PDF to the left of a threshold value. For example, when the flag is set to TRUE the standard normal CDF is returned as shown in the graph below. The output of the CDF represents the likelihood of an event occurring below an input value.
When the cumulative flag is set to FALSE, the standard normal PDF is used. The output of the CDF corresponds to the area under the PDF to the left of a threshold value. For example, with an input of 1 and the cumulative flag set to FALSE the return value is 0.242. For the same input, with the cumulative flag set to TRUE, the function returns 0.841 which is the area to the left of 1 on the normal bell-shaped curve. This is shown below:
Explanation
The standard normal PDF is a bell-shaped probability density function described by two values: The mean represents the center or “balancing point” of the distribution. The standard deviation represents how spread out around the distribution is around the mean. The standard normal distribution is a special case of a normal distribution where the mean is 0 and the standard deviation is 1.
Probabilities
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 makes sense to compute the probability of the student scoring between 90% and 95% on the test. In this example, using a PDF that describes the distribution of test scores, the probability of an event occurring between two thresholds is equal to the area under the PDF’s curve for the two values. Note: Historically, due to the complexity of computing values on and areas below the normal PDF, a standardized version was created to make looking up pre-computed values in a table easier.
Calculating Probability Below a Threshold
To calculate the probability of an event occurring below the z-score value b the formula would be:
Calculating Probability Above a Threshold
To calculate the probability of an event occurring above the z-score value a the formula would be:
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. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
