VARA

What it does

VARA(value1, [value2], ...)

VARA(value1, [value2], ...) 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. This A-family function also admits logical values and text under its inclusion rules.

Try it

Put these four entries in B2:B5:

=VARA(B2:B5) returns 12.916666666666666. The admitted values are 4,8,1,0: their mean is 3.25, and their squared deviations total 38.75. Divide by 4-1=3.

Compare =VARA({4,8,TRUE,"missing"}): the current array rule skips the logical and text elements, leaving only 4,8, and returns 8. The range and array forms therefore use different counts.

Quirks

Keep the argument's origin until collection is complete:

These are the current implementation rules. Retained Excel build 20527 controls distinguish the array route; the source records, for example, VARPA({1,TRUE,3})=1 and STDEVPA({1,"a",3})=1. They do not establish every collection edge on every build. Microsoft’s remarks describe broad inclusion, then also say array/reference text is ignored; that wording does not settle the observed range/array distinction.

No admitted values, or only one admitted value, gives #DIV/0! for this sample statistic. Worksheet errors propagate. Changing the collection rule changes the count as well as the total. A centered mathematical formula alone does not reproduce the observed numerical path.

How to implement

Apply the collection rules above, then use the stored variance recipe. This function uses the sample branch, with final divisor n-1 and no square root.

For the reference example, n=4, S=13, Q=81 and T=R(S*S)=169. The sample numerator is Q-R(T/n)=81-42.25=38.75; dividing by 3 gives 12.916666666666666. Neither centered-pass guard fires: the cancellation ratio is 42.25/81, below 0.99, and the variance is too large for the mean-size test. These integer and quarter-valued intermediate steps are exact; the division by three and any square root still use the prescribed stores.

The same two guards can redirect this call to the centered pass. For 1000,1001,1002, that pass sums 1+0+1=2 and divides by 2. The exact scalar guard edges are measured; their transfer to references and arrays remains open. Nonfinite raw-moment fallback is inferred. No Handbook vector suite is published for this function.

Rust code

32 vara "VARA" (1, 255) [R "value1", R "value2"] |s, a, sv| {
    variance(s, a, sv, Held::Values, 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 VARPA for the population version with the same broad collection policy. VAR.S skips referenced logical values and text. STDEVA takes the sample standard deviation after the same collection.

Named variants

No named variants are recorded yet.