VAR.S

What it does

VAR.S(number1, [number2], ...)

VAR.S(number1, [number2], ...) measures sample variance. It measures squared distance from the mean, so its units are the square of the input units. The divisor is n-1: use this when the observations are a sample of a larger population.

Try it

=VAR.S(-2,0,2) returns 4. The mean is zero and the squared deviations total eight. Divide by two.

Quirks

Numbers in references and arrays count, including zero; referenced text, logical values and empty cells do not. Direct logical values and numeric text can count, so moving a constant into a cell can change the admitted data set. An empty numeric set gives #DIV/0! and a one-value sample does too. A centered mathematical formula alone does not reproduce the observed numerical path.

How to implement

Use the recorded staged algorithm when Excel compatibility is the goal. Here R(op) means: evaluate one operation with a 64-bit significand and extended exponent range, round to nearest with ties to even, then store to binary64 (53 significant bits). It is two rounding steps, not the language's ordinary double operation. The portable stored-arithmetic recipe implements both rounding steps with integer quotients and remainders, including binary64 subnormals and a staged square root. The product uses an x87 model. Disable contraction into a fused multiply-add.

1. Collect the admitted values in their traversal order. Set n to the count, S=0, Q=0. Reject an empty collection, and reject n<2 for a sample.
2. For each x, assign S=R(S+x) and Q=R(Q+R(x*x)). Then set T=R(S*S) and m=R(S/n).
3. For a sample, compute v=R(R(Q-R(T/n))/(n-1)). For a population, compute v=R(R(R(n*Q)-T)/R(n*n)). These expressions have different stored steps: do not algebraically combine them.
4. Test R(R(T/n)/Q)>0.99 and R(v*1000)<=abs(m). If either holds, recompute C from zero by d=R(x-m), C=R(C+R(d*d)), and return R(C/(n-1)) for a sample or R(C/n) for a population. The current code also takes this centered pass when raw moments are nonfinite.
5. For a standard deviation, take the square root at 64-bit significand precision and then store to binary64.

Worked raw path: for -2,0,2, n=3, S=0, Q=8, T=0, m=0. Neither guard holds. Sample variance is 8/2=4; population variance follows (3*8-0)/9=8/3. Sample standard deviation is 2.

Worked centered path: for 1000,1001,1002, S=3003, Q=3006005, T=9018009. The cancellation ratio is about 0.9999993, above 0.99. With m=1001, the centered squares are 1,0,1; their sum is 2, giving sample variance 1 or population variance 2/3.

The scalar guard edges and stored operations have discriminating recorded answers; range/array transfer at the newly located edges remains open. The overflow fallback is an inference from finite recorded answers, and the magnitude comparison has not been separated from its division-based rearrangement. This recipe describes a reconstruction over the cited evidence, not Microsoft's private source code.

Rust code

30 var_s "VAR.S" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    variance(s, a, sv, Held::Numbers, VarianceDivisor::Sample, false)
}

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.

Retained controls from the indexed captures:

These are earlier recorded answers, not new Excel runs or an additional cross-build comparison. The function JSON retains each source locator, exact argument bits where available, and input-route status. Call notation does not imply that captured binary64 values were entered as decimal formula literals.

Alternatives

Use VAR.P when these observations are the whole population. STDEV.S reports sample spread in the original units. VARA includes logical values and referenced text under its broader collection rules.

Named variants

No named variants are recorded yet.