Exceljet

Quick, clean, and to the point

Excel NORM.S.DIST Function

Excel NORM.S.DIST function
Summary 
The Excel NORM.S.DIST function returns the standard normal cumulative distribution when the cumulative flag is set to 1 or true. If the cumulative flag is set to 0 or false, the function returns the standard normal distribution.
Purpose 
Get standard normal cumulative distribution or standard normal probability density function.
Return value 
The standard normal cumulative distribution function.
Syntax 
=NORM.S.DIST (z, cumulative)
Arguments 
  • z - Numeric z-score value.
  • cumulative - Logical value that determines the form of the function.
Usage notes 

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(1,TRUE)=0.8413

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

Cumulative Flag

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:

=NORM.S.DIST(1,TRUE)=0.8413

Standard Normal Cumulative Distribution Function

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.

=NORM.S.DIST(1,FALSE)=0.242

Standard Normal Probability Distribution Function

Explanation

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

CDF Probability Less than a Threshold

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

CDF Probability Greater than a Threshold

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(b, TRUE) - NORM.S.DIST(a, TRUE)

CDF Probability Greater than A and less than B

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.

=NORM.DIST(x,0,1,cumulative)=NORM.S.DIST(x,cumulative)

When the cumulative flag is set to 0 or FALSE, the functions return the respective points along the distributions.

Points on Standard Normal PDF

=NORM.S.DIST(1,FALSE)=0.2420
=NORM.S.DIST(2,FALSE)=0.0540
Normal PDF Example
=NORM.DIST(1,3,2,FALSE)=0.1210
=NORM.DIST(2,3,2,FALSE)=0.1760

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.S.DIST((x-mean)/standard_deviation, TRUE)
=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:

Relative Area Under Normal Distribution

Images courtesy of wumbo.net.