DVARP

What it does

DVARP(database, field, criteria)

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

With V = [ v₁ … vₙ ] the Numbers collected from the field column of the matching records, in record order, and n = |V|:

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

The only difference from DVAR is the divisor: n rather than n − 1. That single change has two consequences worth stating separately, because readers usually notice the first and are caught by the second:

1. DVARP is always the smaller of the two, by the factor (n − 1)/n.
2. DVARP is defined for n = 1 — the variance of a one-element population is exactly zero — whereas DVAR requires two values. The two functions therefore disagree about whether a given selection is an error at all, not merely about the number.

Choosing between them is a modelling question, not a numerical one: divide by n when the selected records are the whole population you are describing, and by n − 1 when they are a sample drawn from a larger one. A criteria-filtered subset of a spreadsheet is very often the whole population of interest, which is why DVARP is less exotic here than VAR.P is in general statistics.

Non-numeric cells in the field column — text, logicals, blanks — are skipped, so n counts numbers, not records.

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.

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 under the two-pass form.

• No numeric value selected: #DIV/0!. Unlike the count and sum members, the statistical members do error on an empty selection.
• Exactly one numeric value selected: 0, not an error. This is the sharpest observable difference from DVAR, which returns #DIV/0! on the same input.
• All selected values equal: 0, exactly, under the two-pass form.
• 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; DVARP is not a lift kernel.

Errors

How to implement

Notes for implementers

1. Guard on n = 0 only. The sample and population members differ in exactly this predicate; sharing the code path and switching only the divisor, without also switching the minimum count, is the natural bug here.
2. Use the two-pass form — see DVAR.
3. The n = 1 answer should be exactly +0, not a denormal residual. The two-pass form gives that for free; a sum-of-squares form may not.
4. Skip, do not coerce. Admit only cells whose value kind is Number.
5. Numeric criteria comparison is not exact double comparison — see the note in the shared mechanism. A tolerant match changes n, and here n is the divisor.

Rust code

12 dvarp "DVARP" (3, 3) [R "database", V "field", R "criteria"] |s, a, sv| { database(s, a, sv, Kind::VarP) }

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

• DVAR is the sample form. DVARP × n = DVAR × (n − 1) whenever both are defined.
• DSTDEVP is SQRT(DVARP).
• Nearest modern equivalent: VAR.P over a filtered array. Excel's compatibility rename pass produced the VARP / VAR.P pair; the database family was left alone, so DVARP is not a deprecated name and has no .P-suffixed replacement.
• Confused with: DVAR (the divisor and the one-value boundary) and VARPA, which counts logicals and text as values.

Named variants

No named variants are recorded yet.