NORM.DIST & NORM.INV Normal Distribution

NORM.DIST & NORM.INV Normal Distribution

In financial risk management, manufacturing quality control, and actuarial modeling, data naturally clusters around a mean in a symmetrical bell curve (Gaussian normal distribution). Operations leaders often need to answer two opposing questions:

1. "What is the probability that daily sales will drop below $4,000 if our average is $5,000 with a standard deviation of $500?" (Forward probability calculation).
2. "What is the revenue threshold that represents our 5% worst-case loss scenario (Value-at-Risk)?" (Inverse threshold calculation).

Instead of manually looking up Z-score tables in statistical textbooks, Excel provides the pair of NORM.DIST and NORM.INV to perform forward and inverse normal curve calculations automatically.

Quick Comparison: Forward vs Inverse Normal Engines

How the Distribution Engines Work

1. Syntax of NORM.DIST

=NORM.DIST(x, mean, standard_dev, cumulative)

• x: The value for which you want the distribution.
• mean: The arithmetic mean of the distribution.
• standard_dev: The standard deviation (must be strictly greater than 0).
• cumulative: A logical value. Always set to TRUE to calculate the cumulative probability area under the curve. (Setting to FALSE returns curve height, used only for plotting charts).

2. Syntax of NORM.INV

=NORM.INV(probability, mean, standard_dev)

To find the 5th percentile worst-case revenue threshold (95% confidence):

=NORM.INV(0.05, 5000, 500)    ' Returns $4,177.57

This tells the risk officer that 95% of days will generate more than $4,177.57 in revenue.

3. Mathematical Boundary Rules

• standard_dev must be positive. If zero or negative, Excel triggers a #NUM! error.
• probability in NORM.INV must be strictly between 0 and 1 (0 < p < 1).

Real-Life Workplace Scenarios

1. Banking Value-at-Risk (VaR)

Portfolio managers evaluate the maximum expected loss over a trading day at a 99% confidence level using NORM.INV(0.01, MeanReturn, StdevReturn).

2. Manufacturing Six-Sigma Defect Rates

Quality engineers calculate the probability that a metal bolt will fall outside tolerance limits.

Common Mistakes to Avoid

• Setting Cumulative to FALSE: In risk analysis, setting cumulative to FALSE returns the height of the curve, NOT the probability! Always use TRUE for probability areas.
• Probability Outside (0, 1): Passing 0.0 or 1.0 into NORM.INV causes a #NUM! error because the normal curve extends infinitely in both directions.

Model normal curve probabilities and Value-at-Risk in our spreadsheet playground below.