How to Use the GAUSS and PHI Functions in Microsoft Excel for Normal Distribution Modeling

Statistical analysis, quality control tolerance testing, and quantitative financial modeling frequently require evaluating probabilities under a standard Gaussian normal distribution curve (a bell curve with a mean of 0 and standard deviation of 1). While Microsoft Excel provides comprehensive normal distribution tools like NORM.S.DIST and NORM.DIST, these long formulas require specifying cumulative distribution flags and handling tail probabilities manually. For dedicated statistical workflows, Excel offers two streamlined, specialized mathematical functions: GAUSS and PHI.

The GAUSS function calculates the probability that a random variable falls specifically between the mean and a given z-score threshold, while PHI calculates the standard normal probability density function (the height of the bell curve). In this guide, you will learn the statistical theory, worksheet syntax, practical examples, and bell-curve charting applications of both functions in Microsoft Excel.

Understanding the GAUSS Function in Excel

The GAUSS function calculates the probability that a member of a standard normal population falls between the mean (0) and z standard deviations from the mean.

Mathematical Definition of GAUSS

Mathematically, GAUSS(z) calculates:

GAUSS(z) = NORM.S.DIST(z, TRUE) - 0.5

Because the total area under half of a symmetric standard normal bell curve equals 0.5, GAUSS measures strictly the area bounded between the center line (mean 0) and the specified z-value.

GAUSS Syntax and Return Values

=GAUSS(z)

• z: Required. The number or cell reference representing the z-score (number of standard deviations from the mean).

Key Behaviour of GAUSS

• If z = 0, =GAUSS(0) returns 0 (zero probability area between the mean and itself).
• If z > 0, =GAUSS(z) returns a positive probability between 0 and 0.5. For example, =GAUSS(1) returns approximately 0.3413 (indicating 34.13% of data lies between the mean and +1 standard deviation).
• If z < 0, =GAUSS(z) returns a negative probability between -0.5 and 0, preserving the directional deviation of negative z-scores.
• If z is non-numeric text, Excel returns a #VALUE! error.

Understanding the PHI Function in Excel

The PHI function calculates the probability density function (PDF) for a standard normal distribution at a specific value x. Rather than calculating a cumulative probability area under the curve, it calculates the vertical height of the normal distribution curve at point x.

Mathematical Definition of PHI

Mathematically, PHI(x) evaluates the Gaussian density equation:

PHI(x) = (1 / SQRT(2 * PI())) * EXP(-0.5 * x^2)

This is functionally equivalent to entering =NORM.S.DIST(x, FALSE), but PHI requires far fewer keystrokes and simplifies formula construction.

PHI Syntax and Return Values

=PHI(x)

• x: Required. The number for which you want the density function evaluated.

Key Behaviour of PHI

• At the peak of the bell curve where x = 0, =PHI(0) returns approximately 0.3989 (the maximum height of a standard normal distribution).
• As x moves further away from zero in either direction (positive or negative), PHI(x) approaches zero asymptotically.
• Because the curve is symmetric, PHI(-x) = PHI(x). For example, both =PHI(-1.5) and =PHI(1.5) return 0.1295.

Step-by-Step Tutorial: Plotting a Standard Bell Curve Using PHI

To plot a smooth standard normal distribution bell curve in Excel using PHI:

1. In Column A, create a series of z-scores ranging from -3.0 to +3.0 in increments of 0.2 (e.g. cells A2:A32).
2. In cell B2, enter the formula to calculate curve height:

   =PHI(A2)

3. Copy the formula down through cell B32.
4. Highlight the data range A2:B32.
5. Go to the Insert tab on the ribbon and click Scatter Chart > Scatter with Smooth Lines.
6. Excel plots an aesthetically perfect Gaussian bell curve centered precisely at zero with a peak height of 0.3989.

Practical Example: Two-Tailed Quality Control Range with GAUSS

In manufacturing Six Sigma quality control, you frequently need to find the total probability of an item landing within ±z standard deviations of the production target:

=GAUSS(z) * 2

Because the normal distribution is perfectly symmetric around the mean:

• =GAUSS(1) * 2 returns 0.6827 (the empirical 68.3% rule for ±1σ).
• =GAUSS(2) * 2 returns 0.9545 (the empirical 95.4% rule for ±2σ).
• =GAUSS(3) * 2 returns 0.9973 (the empirical 99.7% rule for ±3σ).

Using GAUSS eliminates cumbersome nested subtraction formulas when evaluating symmetrical tolerance bands.