SKEW.P

What it does

SKEW.P(number1, [number2], ...)

SKEW.P(number1, [number2], …) returns the population skewness of the values it is given: the third standardised moment computed with the population standard deviation and no bias correction.

For values x_1, …, x_n with mean xbar, define the central moments

m_k  =  (1/n) * sum_{i=1..n} (x_i - xbar)^k

and the population standard deviation sigma = sqrt(m_2). Then

SKEW.P  =  g1  =  m_3 / m_2^(3/2)  =  (1/n) * sum ( (x_i - xbar) / sigma )^3

This is the plain third standardised moment about the sample mean — the estimator textbooks usually write as b1 or g1. It treats the supplied values as the whole population rather than as a sample drawn from a larger one, which is what the .P names throughout Excel's statistical category mean.

The interpretation is the classical one from the moment vocabulary of Abramowitz & Stegun chapter 26: zero for a symmetric distribution, positive when the right tail is longer, negative when the left one is. It is invariant under any positive affine change of units, and it is dimensionless.

The relationship to SKEW is an exact algebraic identity, not an approximation:

SKEW  =  SKEW.P  *  sqrt( n (n-1) ) / (n - 2)

for n >= 3. SKEW applies the bias correction that makes the estimator unbiased for a normal population; SKEW.P does not. The correction factor exceeds 1 always and decays toward 1 as n grows, so the two agree asymptotically and can differ noticeably on small samples. Joanes and Gill (1998) catalogue both, together with a third variant, and give the relations among them. Neither convention is wrong; a reader comparing Excel against another package must first establish which convention that package uses.

g1 is bounded, and by a tighter bound than SKEW's:

|SKEW.P|  <=  (n - 2) / sqrt(n - 1)

with equality when one observation stands apart from n-1 identical ones. A population of five cannot report a skewness beyond 1.5, however lopsided it looks. Small-sample skewness is structurally limited, not merely noisy.

Arguments

The arguments form a flat bag: everything is flattened and concatenated, and the argument boundaries have no effect on the answer.

SKEW.P is an aggregate, not a lift kernel, and it carries the dual coercion policy of coercion and lifting: values reached by scanning a range are used only if they are numbers, with text, logicals and blank cells skipped rather than converted, while values passed directly as scalar arguments go through ordinary to-number coercion. SKEW.P(1,2,"3") therefore admits "3" where the same text in a cell would be ignored.

Try it

Quirks

Result and edge cases

Returns Number, dimensionless.

• All values equal. m_2 = 0, the standardisation is 0/0, and the result is #DIV/0!.
• A symmetric sample. Zero, and exactly zero when the positive and negative cubes cancel exactly — which for a symmetric set in binary64 they usually do, each +d^3 having an exact -d^3 partner. The exactness follows from the data and the summation order, not from a guarantee.
• A single value. m_2 = 0 for one point, so the zero-variance guard fires and the answer is #DIV/0! regardless of the minimum-count question.
• Non-numeric cells in a scanned range are skipped and do not enlarge n.
• Errors anywhere propagate as themselves.

How to implement

Numerical notes

The kernel is the same two-pass centred construction as SKEW's, with n in place of n-1 in the standard deviation and 1/n in place of n/((n-1)(n-2)) in the scaling. Everything said there about algorithm choice applies here; the points specific to SKEW.P are these.

Two-pass centring, not raw power sums. The kernel computes xbar, then accumulates the centred sum of squares and the standardised cubes from stored deviations. The alternative — build m_3 from sum x, sum x^2, sum x^3 algebraically — cancels catastrophically when the mean is large relative to the spread, and loses digits in proportion to the cube of that ratio. A third moment computed that way on data with a large offset can come out with the wrong sign. Chan, Golub and LeVeque (1983) analyse the variance case; the third moment is strictly worse.

Per-element standardisation. Each deviation is divided by sigma before cubing rather than cubing first and scaling the total by sigma^3. This keeps the cubed quantities near unit scale and avoids the overflow that (x - xbar)^3 reaches at deviations beyond roughly 10^102, at the cost of n divisions.

The population divisor is the numerically friendlier one. sigma^2 = sumsq/n needs no n-1 and no guard against n = 1 beyond the zero-variance test, and g1 = standardised_sum/n is one division by an exact integer. There is no (n-1)(n-2) product to overflow or to lose precision in, and no compound rational factor. SKEW.P is, arithmetic for arithmetic, the cleaner of the two skewness functions — which is worth noting because the bias correction that makes SKEW statistically preferable also makes it numerically busier.

What remains. The mean and both accumulations are naive left-to-right sums, so error grows with n rather than being bounded; compensated summation is not used. The zero-variance guard is an exact comparison, not a tolerance, so data with a real but vanishing spread divides by a denormal and produces an enormous result rather than a diagnostic. Summation order is part of the answer, so two correct implementations traversing differently will disagree in the last bits.

The identity as a cross-check. SKEW = SKEW.P * sqrt(n(n-1))/(n-2) is exact in real arithmetic. Computing both and comparing against the closed form exercises the two kernels against each other without an external oracle, and any disagreement localises immediately to one of the two scaling paths — which is exactly the kind of self-consistency check a vector suite for this family should carry.

Rust code

48 skew_p "SKEW.P" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    out(s, k::moment_stats_family::skew_p_kernel(&all(s, a, sv, Held::Numbers)?))
}

pub(crate) fn skew_p_kernel(values: &[f64]) -> Result<f64, WorksheetErrorCode> {
    if values.is_empty() {
        return Err(WorksheetErrorCode::Div0);
    }
    let n = values.len();
    let avg = mean(values);
    let mut sumsq = 0.0;
    let mut deltas = Vec::with_capacity(n);
    for value in values {
        let delta = *value - avg;
        sumsq += delta * delta;
        deltas.push(delta);
    }
    if sumsq == 0.0 {
        return Err(WorksheetErrorCode::Div0);
    }

    let pop_std = (sumsq / n as f64).sqrt();
    let standardized_sum = deltas
        .iter()
        .map(|delta| {
            let z = *delta / pop_std;
            z * z * z
        })
        .sum::<f64>();
    Ok(standardized_sum / n as f64)
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SKEW is the bias-corrected sibling, related by the exact identity above. They are two estimators of the same population quantity, not a legacy/modern pair; neither supersedes the other. Note the asymmetry of the naming: SKEW is the older function and SKEW.P the newer one, so the usual "prefer the dotted name" migration advice does not apply to this pair.
• KURT is the fourth-moment companion. Excel publishes only the bias-corrected kurtosis; there is no KURT.P, which makes the SKEW/SKEW.P pair the only place in the category where both moment conventions are exposed.
• STDEV.P supplies the sigma in the denominator; SKEW.P is undefined exactly where STDEV.P is zero. AVERAGE supplies xbar.
• STANDARDIZE performs the same (x - mu)/sigma map one value at a time.
• SKEW.P has no Compatibility-category predecessor. It was added with the dotted-name reorganisation as a genuinely new function rather than as a rename.
• Readers confuse SKEW.P with a normality test. It is not one, and the sampling variability of g1 is large at the sample sizes spreadsheets usually hold.

Named variants

No named variants are recorded yet.