DVAR

What it does

DVAR(database, field, criteria)

DVAR(database, field, criteria) selects the records of database that satisfy criteria, reads the column named by field, and returns the sample variance of the numeric values found there.

With R the set of selected record rows, c the resolved field column,

V = [ v₁ … vₙ ] = the Numbers in cell (r, c) for r in R, in record order

and n = |V|, the result is

x̄ = (Σ vᵢ) / n
s² = ( Σ (vᵢ − x̄)² ) / (n − 1)

Sample variance divides by n − 1, so at least two values are required; with fewer the divisor is zero or negative and the function is in error territory (see below).

Non-numeric cells in the field column — text, logicals, blanks — are skipped, so n counts numbers, not records. A matching record with a blank field cell reduces n by one; it does not contribute a zero.

The selection stage — header matching, AND across a criteria row, OR down the rows, blank criteria cells, and criteria-expression parsing — is identical for all twelve database functions and is explained once in the criteria-range mechanism.

Why the formula, not just the definition

The two textbook formulas for variance are algebraically identical and numerically are not:

(A)  two-pass:      s² = Σ(vᵢ − x̄)² / (n − 1)
(B)  sum-of-squares: s² = ( Σvᵢ² − (Σvᵢ)²/n ) / (n − 1)

Arguments

Three arguments, all required at the call site; the reference implementation declares an arity of exactly 3.

• database — the header-plus-records range or array.
• field — the column selector, as header text or as a 1-based column index within the database range. Required.
• criteria — the header-plus-conditions range or array.

Try it

Quirks

Result and edge cases

Returns a Number, always non-negative when computed by form (A).

• Fewer than two numeric values selected — no match, one match, or matches whose field cells are mostly blank: #DIV/0!. This is the boundary that separates DVAR from DVARP, which needs only one value.
• All selected values equal: 0, exactly, under form (A) — every deviation is exactly zero. Under form (B) it need not be.
• Text that looks numeric in the field column is skipped, per the range-scan side of the asymmetry in coercion and lifting.
• Logicals in the field column are skipped.
• Arrays: inline array literals resolve to grids and are accepted; DVAR is not a lift kernel.

Errors

How to implement

Notes for implementers

2. Both passes are sequential left folds in record order, per the family's declared reduction policy. That fixes the mean's last bits, which in turn fixes every deviation.
3. The minimum-count check precedes the arithmetic, so #DIV/0! is a domain decision, not a division that happened to have a zero denominator.
4. Skip, do not coerce. Admit only cells whose value kind is Number; n is the count of collected numbers.
5. Numeric criteria comparison is not exact double comparison — see the note in the shared mechanism. For the statistical members this has a second-order effect worth noting: a tolerant match can change n, and n appears in the divisor.

Rust code

11 dvar "DVAR" (3, 3) [R "database", V "field", R "criteria"] |s, a, sv| { database(s, a, sv, Kind::Var) }

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

• DVARP is the population form: divisor n instead of n − 1, and one value is enough. DVAR × (n − 1) = DVARP × n whenever both are defined.
• DSTDEV is SQRT(DVAR) — the same selection, the same estimator, one extra operation.
• Nearest modern equivalents: VAR.S combined with a filtered array, or VAR.S over a FILTER result in a dynamic-array workbook. Excel's own compatibility pairing is VAR (legacy) / VAR.S (modern); the database family was not renamed in that pass and DVAR keeps its name and its sample semantics.
• Confused with: VAR.P/DVARP (the divisor), and VARA, which counts logicals and text.

Named variants

No named variants are recorded yet.