SKEW

What it does

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

SKEW(number1, [number2], …) returns the sample skewness of the values it is given: a dimensionless measure of the asymmetry of their distribution about its mean, under the bias-corrected convention.

For values x_1, …, x_n with mean xbar and sample standard deviation

s  =  sqrt( sum (x_i - xbar)^2 / (n - 1) )

the returned quantity is

SKEW  =  G1  =  ( n / ((n-1)(n-2)) )  *  sum_{i=1..n} ( (x_i - xbar) / s )^3

This is the adjusted Fisher–Pearson standardised moment coefficient, conventionally written G1. It is the estimator obtained by correcting the raw third standardised moment so that it is unbiased for the population skewness when the underlying data is normal.

Its meaning is the classical one from the moment vocabulary of Abramowitz & Stegun chapter 26: the third standardised moment E[((X - mu)/sigma)^3] measures which tail is longer. SKEW returns zero for any symmetric sample, positive when the right tail is longer (mass bunched at the low end, a few large values), and negative for the mirror image. It is invariant under any positive affine change of units — shift the data, scale it by a positive factor, and SKEW does not move — which is what makes it comparable across variables measured in different units.

Skewness has more than one sample definition, and Excel publishes two of them. The uncorrected form, the plain third standardised moment about the sample mean using the population standard deviation, is

g1  =  m3 / m2^(3/2)  ,   where  m_k = (1/n) sum (x_i - xbar)^k

and that is what SKEW.P returns. The two are related by an exact algebraic identity:

G1  =  g1  *  sqrt( n (n-1) ) / (n - 2)

so SKEW is always the larger in magnitude, and the inflation factor tends to 1 as n grows — it is above 1.1 only for small samples. Joanes and Gill (1998) catalogue this estimator and its competitors and give the relations among them; G1 is the one used by SAS and SPSS, and g1 is the one used by many textbooks. Neither is wrong. A reader comparing Excel against another package should establish which convention that package used before calling anything a discrepancy.

The estimator is bounded, a fact that is easy to miss and useful to know. For the uncorrected form the classical bound is |g1| <= (n-2)/sqrt(n-1), attained when one observation sits apart from n-1 identical ones. Substituting into the identity above gives

|SKEW|  <=  sqrt(n)

So a sample of five can never produce a skewness beyond about 2.24, however extreme it looks. Small-sample skewness estimates are not merely noisy; they are structurally incapable of reporting the asymmetry that a heavy-tailed population actually has. Any workflow that flags "skewness above 2" as a data-quality signal is measuring the sample size as much as the shape.

Arguments

The arguments are a flat bag: everything is flattened and concatenated into one data set, and the argument boundaries have no effect on the answer.

SKEW is an aggregate, not a lift kernel, and it carries the dual coercion policy described in coercion and lifting:

• Values reached by scanning a range or array are used only if they are numbers. Text, logical values and blank cells are skipped — they do not become zeros and they do not enlarge n.
• Values passed directly as scalar arguments go through ordinary to-number coercion, so SKEW(1,2,"3") admits the text "3" as 3 where the same text in a scanned cell would be ignored. Direct text that does not parse is #VALUE!.

That asymmetry is engine-wide policy, not something specific to SKEW, and it is the single most common source of "the same data gives two answers" reports across the whole statistical category.

Try it

Quirks

Result and edge cases

Returns Number, dimensionless.

• Fewer than three numeric values. #DIV/0! — the (n-2) factor in the denominator is undefined at n = 2 and the estimator has no meaning below that. This matches Microsoft's documented condition.
• All values equal. The centred sum of squares is zero, s = 0, and the standardisation is 0/0. #DIV/0!, documented.
• A symmetric sample. SKEW is zero, and for data symmetric about its mean it is exactly zero only when the positive and negative cubes cancel exactly — which for a two-sided symmetric set in binary64 they generally do, since each +d^3 has an exact -d^3 partner. It is worth knowing that this exactness is a property of the summation order and the data, not a guarantee.
• Non-numeric cells in a scanned range are skipped and do not enlarge n.
• Errors anywhere propagate as themselves.
• The result is not bounded by any fixed constant, but is bounded by sqrt(n) as above.

Errors

How to implement

Numerical notes

SKEW is a third moment, and third moments amplify every error the mean carries. The reference engine's kernel is worth reading closely because it makes two good choices and one that is merely conventional.

Two-pass centring, not raw power sums. The kernel computes xbar first, then accumulates sum (x_i - xbar)^2 and the standardised cubes in a second pass over stored deviations. The alternative — accumulating sum x, sum x^2, sum x^3 and assembling the central moments algebraically — is the form that appears in textbooks and in one-pass streaming code, and it is much worse. The third central moment obtained that way is

m3_raw  =  (1/n) sum x^3  -  3*xbar*(1/n) sum x^2  +  2*xbar^3

Per-element standardisation rather than a final scaling. The kernel divides each deviation by s and then cubes, rather than cubing the deviations and dividing the sum by s^3. This costs n divisions instead of one, and buys range: ((x-xbar)/s)^3 stays near unit scale for typical data, whereas (x - xbar)^3 overflows for deviations beyond about 10^102 and underflows below about 10^{-102}. Data at those scales is rare but reachable, and the chosen form is the robust one. It is the same argument that makes hypot-style scaling standard practice.

What remains.

• The mean and both accumulations are naive left-to-right sums, so their error grows with n rather than being bounded by a constant. Compensated summation would fix this and is not used.
• The s = 0 guard is an exact comparison of the centred sum of squares against zero, not a tolerance. Data with a real but vanishing spread divides by a denormal and produces an enormous standardised cube rather than a diagnostic. There is no correct answer here, only a choice.
• Cancellation still bites when the data has a large mean relative to its spread, because x_i - xbar is a subtraction of nearby numbers even in the two-pass form. The two-pass form reduces the damage from catastrophic to proportionate; it does not eliminate it. A classic test case is the same values shifted by a large constant: the true skewness is invariant, so any change in the answer is pure floating-point residue.
• Summation order is part of the answer. Two correct implementations traversing the data differently will disagree in the last bits, and nothing in the mathematics fixes those bits.

Where the identity helps. SKEW = SKEW.P * sqrt(n(n-1))/(n-2) is exact in real arithmetic and is a strong cross-check: computing both and comparing the ratio against the closed form exercises the two kernels against each other with no external oracle. Because the reference engine computes them by parallel code paths with different denominators, agreement is informative and disagreement localises immediately.

Rust code

47 skew "SKEW" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    out(s, k::moment_stats_family::skew_kernel(&all(s, a, sv, Held::Numbers)?))
}

pub(crate) fn skew_kernel(values: &[f64]) -> Result<f64, WorksheetErrorCode> {
    if values.len() < 3 {
        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 sample_std = (sumsq / (n as f64 - 1.0)).sqrt();
    let standardized_sum = deltas
        .iter()
        .map(|delta| {
            let z = *delta / sample_std;
            z * z * z
        })
        .sum::<f64>();
    let n = n as f64;
    // F1 (dc-8dq): the factor n/((n-1)(n-2)) first, then times the sum, as Excel's SKEW gives
    // SKEW(1,2,3,5) (the catalogue oracle's capture); n*sum/((n-1)(n-2)) is one ulp above it.
    Ok(n / ((n - 1.0) * (n - 2.0)) * standardized_sum)
}

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

• SKEW.P is the population-convention sibling, g1, related by SKEW = SKEW.P * sqrt(n(n-1))/(n-2). It is not a legacy/modern pair and neither supersedes the other: they are two different estimators of the same population quantity, and Excel names both. Note the domain difference — SKEW requires n >= 3 by construction, and the reference engine's SKEW.P does not.
• KURT is the fourth-moment companion, with the same shape of bias correction and the same family kernel. KURT requires n >= 4 for the same structural reason SKEW requires n >= 3.
• STDEV.S supplies the s in the denominator; SKEW is undefined exactly where STDEV.S is zero. AVERAGE supplies xbar.
• STANDARDIZE performs the same (x - mu)/sigma map that SKEW applies internally, one value at a time.
• SKEW has no Compatibility-category predecessor. It is one of the older statistical functions that was left undotted when the .P/.S reorganisation happened, so SKEW is both the historical name and the current one, and SKEW.P is the newcomer. This is the reverse of the usual pattern and is worth stating because migration guides that say "prefer the dotted name" do not apply here.
• Readers confuse SKEW with a normality test. It is not one: a skewness near zero is consistent with many non-normal distributions, and the sampling distribution of G1 under normality has a standard error near sqrt(6/n), which is large for the sample sizes spreadsheets usually hold.

Named variants

No named variants are recorded yet.