Z Score

Calculating Z-Score in Excel

The Z-score (or standard score) measures how many standard deviations a data point is from the mean of a dataset. The formula is: Z = (x - μ) / σ Where:

• x = individual data point
• μ = population mean
• σ = population standard deviation

Excel has a built-in function STANDARDIZE for this, which uses population mean and standard deviation. For sample-based calculations, compute mean and std dev manually first.

Step-by-Step Guide Using Formulas (Easiest Method)

1. Enter your data: In a column (e.g., A1:A5 for values, B1:B5 for Z-scores). Example dataset:

  Calculate mean and standard deviation (if not already done):

• In C1: =AVERAGE(A1:A5) (mean ≈ 11.8)
• In C2: =STDEV.P(A1:A5) (population std dev ≈ 2.68; use STDEV.S for sample)

  Calculate Z-score for each point:

• In B1: =STANDARDIZE(A1, $C$1, $C$2) (locks mean and std dev with $)
• Drag the formula down to B5.

Results for the example:

1. Alternatively, use the manual formula in B1: =(A1 - $C$1) / $C$2 and drag down.

Using Descriptive Statistics (ToolPak for Batch Analysis)

If you want Z-scores alongside other stats:

1. Enable Data Analysis ToolPak (File > Options > Add-ins > Analysis ToolPak).
2. Go to Data > Data Analysis > Descriptive Statistics.
3. Input range: e.g., A1:A5. Check Summary statistics.
4. Output to a new sheet. It includes mean and std dev—then manually apply the Z-score formula as above. (No direct Z-score in ToolPak, but you can add a column for it.)

Tips and Common Errors

• Sample vs. Population: Use STDEV.S and AVEDEV (or manual) for samples to avoid bias.
• For Normal Distribution: To find probability from Z-score, use =NORM.S.DIST(B1, TRUE) (cumulative probability).
• Errors: #DIV/0! if std dev is 0. #VALUE! for non-numbers.
• Dynamic Ranges: Use Excel Tables (Insert > Table) for auto-expanding formulas.
• Visualization: Insert a scatter plot (Insert > Charts > Scatter) with Z-scores on the x-axis for distribution view.