Statistical
Normalize data between 0 and 1 (Min-Max scaling vs Z-score)
Rescale disparate KPIs onto a common 0–1 (or 0–100) scale with (x - MIN) / (MAX - MIN) or standardize into Z-scores.
Min-Max [0, 1] scaling vs Z-score standardization
Combining Revenue ($10k–$90k) and NPS (-20 to +80) in a composite index fails unless both columns share a common scale. Min-Max normalization (x - MIN(r)) / (MAX(r) - MIN(r)) maps the minimum value to 0.00, the maximum to 1.00, and every intermediate point proportionally.
Z-score scaling expresses observations in standard deviations from the mean. Both mean and standard deviation remain sensitive to outliers; this is not a robust scaling method.
| MetricVal (A) | Min-Max [0..1] | 0–100 Score | Z-Score |
|---|---|---|---|
| 20 | 0.00 | 0 | -1.0247 |
| 40 | 0.25 | 25 | -0.4392 |
| 60 | 0.50 | 50 | +0.1464 |
| 100 | 1.00 | 100 | +1.3175 |
Min-Max scaling with a constant-column policy
Guard MAX = MIN (all values identical) so constant columns return 0 instead of #DIV/0!.
=LET(lo, MIN($A$2:$A$5), hi, MAX($A$2:$A$5), IF(hi=lo, 0, ROUND((A4 - lo) / (hi - lo), 4)))Standardized Z-score formula
Measures how many sample standard deviations A4 sits above (+) or below (-) the column mean.
=ROUND((A4 - AVERAGE($A$2:$A$5)) / STDEV.S($A$2:$A$5), 4)Inputs and interpretation
The zero result for a constant column is an explicit display policy, not a defined ratio. The Z-score formula requires at least two numeric observations and nonzero sample standard deviation. Use numeric target cells; blank targets must not be treated as real zero measurements. For predictive models, fit scaling on training data only. New values outside the fitted range can map below 0 or above 1.
Try the example
Use the original values or change them to test your understanding.