STANDARDIZE

What it does

STANDARDIZE(x, mean, standard_dev)

STANDARDIZE(x, mean, standard_dev) returns the z-score of x relative to a distribution with the stated centre and scale:

STANDARDIZE(x, mu, sigma)  =  ( x - mu ) / sigma        for sigma > 0

It is the affine map T(x) = (x - mu)/sigma, and everything about it follows from that being an affine map with positive slope:

• It is a bijection of the real line onto itself, strictly increasing, with inverse x = mu + sigma*z. Nothing is lost and nothing is folded.
• It is the unique affine map sending mu to 0 and mu + sigma to 1. That is what fixes the convention: STANDARDIZE(mu, mu, sigma) = 0 and a value one standard deviation above the mean maps to exactly 1.
• Applied to a whole data set with its own mean and standard deviation, the image has mean 0 and standard deviation 1. Applied to a random variable X with E[X] = mu and SD(X) = sigma, the image Z has E[Z] = 0 and SD(Z) = 1 — for any distribution, not only the normal one. Standardisation is a change of units, not a normality assumption.

Its role in the statistical category is as the argument reduction for the normal family. The substitution z = (x - mu)/sigma is what reduces a general normal density and distribution function to the standard ones — it is the substitution behind Abramowitz & Stegun's treatment in chapter 26, where every general-normal quantity is tabulated through its standard form. In Excel terms the mathematical identity is

NORM.DIST(x, mu, sigma, TRUE)  =  NORM.S.DIST( STANDARDIZE(x, mu, sigma), TRUE )

and the corresponding one for the density carries an extra 1/sigma from the Jacobian:

NORM.DIST(x, mu, sigma, FALSE)  =  NORM.S.DIST( STANDARDIZE(x, mu, sigma), FALSE ) / sigma

Both are exact in real arithmetic. Neither is a good way to compute the left-hand side in floating point — see Numerical notes.

The name is slightly misleading in one respect worth stating: STANDARDIZE does not compute mean or standard_dev from data. It is a three-argument scalar transform, and the caller supplies the parameters. Standardising a column against its own statistics means writing STANDARDIZE(A1, AVERAGE($A$1:$A$100), STDEV.S($A$1:$A$100)), which is where the population versus sample choice actually enters.

Arguments

Try it

Quirks

Result and edge cases

Returns Number, dimensionless — the units of x and mu cancel against those of sigma.

• x = mean. Exactly 0, and exactly +0 for mu finite, since x - mu is exactly zero and 0/sigma is +0 for positive sigma.
• standard_dev negative. #NUM!. A negative scale would give a well-defined order-reversing affine map, and it is refused rather than accepted — the function insists on the orientation convention.
• Very small standard_dev. Subnormal scales are accepted by the <= 0 test and produce correspondingly enormous or infinite results.
• Errors in any argument propagate as themselves; under the lifted path an error in one element of an array argument stays in that element.

Errors

There is no #DIV/0! reachable from this function: the only division is by a value the guard has already established to be strictly positive.

How to implement

Numerical notes

The function is one subtraction and one division, both correctly rounded, so the implementation contributes at most one ulp of relative error to each. All of the interesting behaviour is in the conditioning of the map itself.

Catastrophic cancellation at the centre. x - mu is a subtraction of two nearby quantities whenever x is close to mu, and that is exactly the region where users spend most of their time. If x and mu are each around 10^6 and differ by around 10^{-6}, the subtraction is exact in binary64 (Sterbenz's lemma covers the case where the two operands are within a factor of two of each other), but the inputs generally are not: mu came from an AVERAGE that carried its own rounding, and that error is transmitted to the result multiplied by 1/sigma. The relative error of the z-score is unbounded near x = mu even though its absolute error is fine. This matters for anything downstream that cares about the relative accuracy of a small z-score, and it does not matter at all for a tail probability, where the absolute accuracy is what governs.

The general statement: the condition number of z with respect to mu is |mu|/|x - mu|, which blows up at the centre; the condition number with respect to sigma is 1, uniformly. Errors in the mean are dangerous; errors in the standard deviation are not.

Why the composition identity is not a computational recipe. NORM.DIST is mathematically NORM.S.DIST(STANDARDIZE(...)), but computing it that way costs accuracy twice. First, the standardisation rounds z, and then the distribution function amplifies that rounding by its own derivative — which in the far tail is the density, so the relative error of the tail probability grows like |z| times the relative error in z. Second, the composition throws away any opportunity for the distribution routine to use a better-conditioned form of its argument. This is the standard reason that libraries expose a general-parameter entry point rather than telling callers to standardise first, and it is the reason NORM.DIST is a separate function rather than a wrapper. Do not use STANDARDIZE as a preprocessing step for a tail probability if accuracy in the tail matters.

The guard is exact, not tolerant. sigma <= 0 is an exact comparison. A subnormal sigma passes it and produces an enormous quotient, possibly an infinity. There is no tolerance and no scaling, which is a defensible choice — any threshold would be arbitrary — but it means the function has no "nearly degenerate" diagnostic, only a degenerate one.

Overflow and underflow. (x - mu)/sigma can overflow when the numerator is large and the scale is subnormal, and can underflow to zero when the reverse holds. Neither is diagnosed. A scale-aware implementation would compute the quotient by rescaling both operands, as hypot does; this one does not need to for ordinary data and would need to for adversarial data.

Rust code

50 standardize "STANDARDIZE" (3, 3) [V "x", V "mean", V "standard_dev"] |s, a, sv| {
    let (x, mean, sd) = (num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?);
    out(s, k::standardize_fn::standardize_kernel(x, mean, sd))
}

pub fn standardize_kernel(x: f64, mean: f64, stdev: f64) -> Result<f64, WorksheetErrorCode> {
    if stdev <= 0.0 {
        return Err(WorksheetErrorCode::Num);
    }
    let difference = x - mean;
    let result = difference / stdev;
    if !difference.is_finite() || !result.is_finite() {
        return Err(WorksheetErrorCode::Num);
    }
    Ok(if result.abs() < f64::MIN_POSITIVE { 0.0 } else { result })
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• NORM.S.DIST and NORM.DIST are the functions STANDARDIZE feeds. The composition identity above is exact mathematically, and NORM.DIST exists as a separate function precisely so that callers do not have to route through it.
• NORM.S.INV and NORM.INV run the inverse map: NORM.INV(p, mu, sigma) = mu + sigma * NORM.S.INV(p), which is STANDARDIZE read backwards.
• AVERAGE, STDEV.S and STDEV.P are the usual sources of the two parameters. Choosing between the sample and population standard deviation is a statistical decision that STANDARDIZE does not make and cannot see.
• Z.TEST is the hypothesis test built on the same standardisation, with the standard error sigma/sqrt(n) in place of sigma.
• PERCENTRANK.INC is the non-parametric alternative for "where does this value sit": it uses the empirical distribution instead of a location-scale model, and needs no parameters.
• Readers confuse STANDARDIZE with normalisation to [0, 1], which is (x - min)/(max - min) — a different affine map with different invariances. STANDARDIZE centres on the mean and scales by spread; min-max scaling pins the extremes. Neither is "the" normalisation.

Named variants

No named variants are recorded yet.