Syntax
STANDARDIZE(x, mean, standard_dev)
| Parameter | Description |
|---|---|
| x | Parameter of the STANDARDIZE function. |
| mean | Parameter of the STANDARDIZE function. |
| standard_dev | Parameter of the STANDARDIZE function. |
Examples
How far from average?
Formula
=STANDARDIZE(85, 72, 8)
Returns 1.625 — a score of 85 is 1.625 standard deviations above the mean of 72 (with std dev 8).
Outlier detection
Formula
=ABS(STANDARDIZE(B5, AVERAGE(B2:B100), STDEV(B2:B100))) > 3
Returns TRUE if the value in B5 is more than 3 standard deviations from the mean — a common outlier threshold.
Comparing across scales
Formula
=STANDARDIZE(750, 500, 100)
Returns 2.5 — a score of 750 on a test with mean 500 and std dev 100 is 2.5 standard deviations above average.
Common Errors
#NUM!
Standard deviation is 0 or negative. Division by zero is undefined, and standard deviation cannot be negative.
#VALUE!
Non-numeric arguments.
Tips
Interpreting z-scores
z=0 is the mean, z=1 is one stdev above, z=-2 is two stdevs below. About 95% of normally distributed data falls between z=-2 and z=2.
Comparing apples to oranges
Z-scores let you compare values from different scales. A z-score of 2.0 on a math test and 1.5 on a verbal test means the math performance was relatively stronger, even if the raw scores differ dramatically.
Use with NORM.S.DIST
After standardizing, use NORM.S.DIST(z, TRUE) to find the percentile. A z-score of 1.96 corresponds to the 97.5th percentile.
Try STANDARDIZE in Viztab
Import your data and use STANDARDIZE with 370+ other formulas. No signup required.
Open Viztab