How to Use NORM.S.DIST in Excel
NORM.S.DIST is Excel's implementation of the standard normal distribution function. It calculates probabilities and density values for a normal distribution with a mean of 0 and standard deviation of...
Key Insights
- NORM.S.DIST calculates probabilities for the standard normal distribution (mean=0, std dev=1), with cumulative=TRUE returning the area under the curve and cumulative=FALSE returning the curve height at a specific z-score
- Use cumulative distribution (TRUE) for real-world probability questions like “what percentage of values fall below this point” and probability density (FALSE) primarily for visualizing the bell curve shape
- The function only works with standardized z-scores; if your data has a different mean or standard deviation, you must either standardize it first or use NORM.DIST instead
Understanding NORM.S.DIST
NORM.S.DIST is Excel’s implementation of the standard normal distribution function. It calculates probabilities and density values for a normal distribution with a mean of 0 and standard deviation of 1—what statisticians call the “standard normal distribution” or “z-distribution.”
This function answers two fundamental questions: “What’s the probability that a random value falls below a certain point?” (cumulative distribution) and “What’s the relative likelihood of a value occurring at exactly this point?” (probability density). Understanding when to use each mode is critical for proper statistical analysis.
The standard normal distribution is the foundation of many statistical techniques, from hypothesis testing to quality control. Every normally distributed dataset can be converted to this standard form using z-scores, making NORM.S.DIST universally applicable across different domains.
Function Syntax and Parameters
The NORM.S.DIST function follows this structure:
=NORM.S.DIST(z, cumulative)
z: The z-score you’re evaluating. A z-score represents how many standard deviations away from the mean a value sits. Positive z-scores are above the mean, negative z-scores are below it.
cumulative: A logical value (TRUE or FALSE) that determines what the function returns:
- TRUE: Returns the cumulative distribution function (CDF)—the probability that a value is less than or equal to z
- FALSE: Returns the probability density function (PDF)—the height of the normal curve at z
Here’s a basic demonstration:
# Cell A1: Z-score
# Cell B1: Cumulative Probability
# Cell C1: Probability Density
# Cell A2: -2
# Cell B2: =NORM.S.DIST(A2, TRUE) # Returns 0.0228
# Cell C2: =NORM.S.DIST(A2, FALSE) # Returns 0.0540
# Cell A3: 0
# Cell B3: =NORM.S.DIST(A3, TRUE) # Returns 0.5000
# Cell C3: =NORM.S.DIST(A3, FALSE) # Returns 0.3989
# Cell A4: 1.96
# Cell B4: =NORM.S.DIST(A4, TRUE) # Returns 0.9750
# Cell C4: =NORM.S.DIST(A4, FALSE) # Returns 0.0584
The z-score of 0 always returns 0.5 for cumulative probability because exactly half the distribution falls below the mean.
Calculating Cumulative Probabilities
Cumulative probabilities answer the question: “What percentage of values fall at or below this z-score?” This is the most common use case for NORM.S.DIST in business and research contexts.
The classic example is z = 1.96. In hypothesis testing with a 95% confidence level, this z-score represents the critical value:
=NORM.S.DIST(1.96, TRUE)
# Returns 0.975 or 97.5%
This tells us that 97.5% of values in a standard normal distribution fall below z = 1.96. Conversely, 2.5% fall above it (1 - 0.975 = 0.025).
Here’s how to create a comprehensive probability table:
# Cell A1: "Z-Score"
# Cell B1: "Cumulative Probability"
# Cell C1: "Percentage Below"
# Cell A2: -3
# Cell B2: =NORM.S.DIST(A2, TRUE)
# Cell C2: =B2*100 & "%"
# Cell A3: -2.5
# Cell B3: =NORM.S.DIST(A3, TRUE)
# Cell A4: -2
# Cell B4: =NORM.S.DIST(A4, TRUE)
# Continue with: -1.5, -1, -0.5, 0, 0.5, 1, 1.5, 2, 2.5, 3
This table reveals important patterns:
- z = -3: 0.13% (virtually nothing falls below)
- z = -2: 2.28%
- z = -1: 15.87%
- z = 0: 50.00%
- z = 1: 84.13%
- z = 2: 97.72%
- z = 3: 99.87%
The empirical rule (68-95-99.7) becomes visible: approximately 68% of values fall within ±1 standard deviation, 95% within ±2, and 99.7% within ±3.
Calculating Probability Density
Setting cumulative to FALSE returns the probability density—the y-axis value on the bell curve at a specific z-score. While less intuitive than cumulative probabilities, density values are essential for visualizing distributions.
To create a bell curve in Excel:
# Cell A1: "Z-Score"
# Cell B1: "Density"
# Cell A2: -4
# Cell B2: =NORM.S.DIST(A2, FALSE)
# Cell A3: =A2+0.1
# Cell B3: =NORM.S.DIST(A3, FALSE)
# Drag down to create values from -4 to 4 in 0.1 increments
Select both columns and insert a scatter plot with smooth lines to visualize the classic bell curve. The peak occurs at z = 0 with a density of approximately 0.3989—this is the height of the curve at the mean.
Key observations about probability density:
- The maximum density is always at z = 0
- Density values are symmetric around zero
- Density never equals zero (the tails extend infinitely)
- Density values don’t represent probabilities directly (they can exceed 1.0 for other distributions)
The area under the entire curve equals 1.0, representing 100% probability. The area between any two z-scores gives the probability of values falling in that range.
Real-World Applications
Quality Control Scenario: A manufacturing process produces bolts with a mean diameter of 10mm and standard deviation of 0.5mm. Specifications require diameters between 9mm and 11mm. What percentage of bolts meet specifications?
First, convert to z-scores:
- Lower bound: (9 - 10) / 0.5 = -2
- Upper bound: (11 - 10) / 0.5 = 2
# Cell A1: "Lower Z-Score"
# Cell B1: -2
# Cell C1: =NORM.S.DIST(B1, TRUE) # Returns 0.0228
# Cell A2: "Upper Z-Score"
# Cell B2: 2
# Cell C2: =NORM.S.DIST(B2, TRUE) # Returns 0.9772
# Cell A3: "Within Spec %"
# Cell B3: =C2-C1 # Returns 0.9544 or 95.44%
Approximately 95.44% of bolts meet specifications. If you’re producing 10,000 bolts daily, expect about 456 defects.
Risk Analysis: An investment portfolio has returns that follow a normal distribution. If the standardized return threshold for “significant loss” is z = -1.5, what’s the probability of experiencing this loss?
=NORM.S.DIST(-1.5, TRUE)
# Returns 0.0668 or 6.68%
There’s a 6.68% chance of significant loss in any given period.
Hypothesis Testing: For a two-tailed test at 95% confidence, you need z-scores that capture the middle 95% of the distribution:
# Lower critical value (2.5% in left tail)
# Use NORM.S.INV(0.025) = -1.96
# Upper critical value (97.5% cumulative)
# Use NORM.S.INV(0.975) = 1.96
# Verify with NORM.S.DIST
=NORM.S.DIST(-1.96, TRUE) # Returns 0.025
=NORM.S.DIST(1.96, TRUE) # Returns 0.975
Common Pitfalls and Tips
NORM.S.DIST vs. NORM.DIST: The “S” stands for “standard.” NORM.S.DIST assumes mean=0 and std dev=1. NORM.DIST accepts any mean and standard deviation as additional parameters.
Wrong approach:
# Data with mean=100, std dev=15
# Value to evaluate: 115
=NORM.S.DIST(115, TRUE) # WRONG! Returns nearly 1.0
Correct approaches:
# Option 1: Calculate z-score first
=(115-100)/15 # Returns 1
=NORM.S.DIST(1, TRUE) # Returns 0.8413
# Option 2: Use NORM.DIST directly
=NORM.DIST(115, 100, 15, TRUE) # Returns 0.8413
Interpreting Negative Z-Scores: Negative z-scores aren’t “bad”—they simply indicate values below the mean. A z-score of -1 is just as “normal” as +1.
# Both are equally distant from the mean
=NORM.S.DIST(-1, TRUE) # Returns 0.1587 (15.87% below)
=NORM.S.DIST(1, TRUE) # Returns 0.8413 (84.13% below)
Cumulative vs. Non-Cumulative Decision: Use cumulative=TRUE for probability questions (“What percentage…?”, “What’s the chance…?”). Use cumulative=FALSE only when creating visualizations or when you specifically need the probability density function for advanced statistical calculations.
Precision Matters: For critical applications, use enough decimal places. The difference between z=1.96 and z=2.00 is small but meaningful in hypothesis testing:
=NORM.S.DIST(1.96, TRUE) # 0.9750 (2.5% in upper tail)
=NORM.S.DIST(2.00, TRUE) # 0.9772 (2.28% in upper tail)
NORM.S.DIST is your gateway to probability calculations in Excel. Master it by remembering: cumulative=TRUE for “percentage below” questions, standardize your data first, and always verify your z-score calculations before plugging them into the function. With these fundamentals, you can tackle everything from quality control to advanced statistical analysis.