Statistical & Aggregation
Z-Score Feature Normalization Formula (STANDARDIZE With AVERAGE & STDEV.S)
Transform raw observations into dimensionless standard-deviation units z = (x - mean) / sd to compare metrics on different scales and invert Z-scores back to raw units.
Compare apples and oranges on a common zero-mean unit-variance scale
Comparing an applicant’s 95-point technical assessment (mean 80, SD 10) against an 88-point structured interview (mean 82, SD 3) is misleading in raw points: z_tech = (95 - 80) / 10 = +1.50 SD above average, whereas z_interview = (88 - 82) / 3 = +2.00 SD above average.
Excel’s STANDARDIZE(x, mean, standard_dev) evaluates z = (x - mean) / standard_dev and requires standard_dev > 0. Computing mean and dispersion directly from sample range B2:B6 via AVERAGE and STDEV.S updates Z-scores dynamically whenever cohort benchmarks change.
| Cohort Sample (B2:B6) | Raw Score | Cohort Mean (mu) | Sample SD (s) | Standardized Z-Score |
|---|---|---|---|---|
| 66, 78, 80, 82, 94 | x = 95 | 80.0 | 10.0 | +1.50 SD |
| 66, 78, 80, 82, 94 | x = 80 | 80.0 | 10.0 | 0.00 SD (Exact mean) |
| 66, 78, 80, 82, 94 | x = 60 | 80.0 | 10.0 | -2.00 SD (Tail alert) |
Sample-Anchored Z-Score Normalization (STANDARDIZE)
Computes (E2 - AVERAGE(B2:B6)) / STDEV.S(B2:B6) while guarding zero or negative dispersion.
=LET(mu, AVERAGE(B2:B6), sd, STDEV.S(B2:B6), IF(sd <= 0, "Zero Dispersion", ROUND((E2 - mu) / sd, 4)))Inverse Z-Score Reconstruction (Raw Value From Target Z)
Converts target Z-score F2 back into raw units via mean + z * sd.
=ROUND(AVERAGE(B2:B6) + F2 * STDEV.S(B2:B6), 4)Try the example
Use the original values or change them to test your understanding.