DSTDEV

What it does

DSTDEV(database, field, criteria)

DSTDEV(database, field, criteria) selects the records of database that satisfy criteria, reads the column named by field, and returns the sample standard deviation 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
s  = √( Σ (vᵢ − x̄)² / (n − 1) )

Operationally it is DVAR followed by one square root; the reference implementation computes it exactly that way. Sample semantics mean the divisor is n − 1 and at least two numeric values are required.

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.

The extra rounding step

DSTDEV is not a single rounded operation. The variance is computed and rounded to a double, and then the square root of that rounded value is taken and rounded again. The result is therefore not in general the correctly rounded standard deviation of the input data: two roundings sit between the exact answer and the published one. IEEE-754 square root is itself correctly rounded, so the discrepancy comes entirely from the intermediate — which means an implementation that computed s directly, more accurately, would differ from the composed route on some inputs. Matching a composed route is a compatibility target, not an accuracy target, and the Handbook has not established which route Excel takes.

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, non-negative.

• Fewer than two numeric values selected: #DIV/0! — the error comes from the variance stage, before any square root is attempted.
• All selected values equal: 0, exactly, since the variance is exactly zero and √0 is 0.
• 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; DSTDEV is not a lift kernel.
• No negative-argument path. Because the two-pass variance cannot be negative, the square root has no domain failure. An implementation using the sum-of-squares form could produce a negative variance and hence a #NUM! from the root — an error DSTDEV should never be able to return.

Errors

How to implement

Notes for implementers

1. Compose, do not reformulate. Computing the variance and taking its square root is what the reference implementation does; a more accurate direct route is a different function in the last bits.
2. Guard the count before the arithmetic. #DIV/0! at n < 2 is a domain decision.
3. Both accumulation passes are sequential left folds in record order, per the family's declared reduction policy.
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.

Rust code

8 dstdev "DSTDEV" (3, 3) [R "database", V "field", R "criteria"] |s, a, sv| { database(s, a, sv, Kind::Stdev) }

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

• DSTDEVP is the population form: divisor n, and one value is enough.
• DVAR is the square of this function, and the page that carries the shared numerical discussion.
• Nearest modern equivalent: STDEV.S over a filtered array. Excel's compatibility rename pass produced STDEV / STDEV.S; the database family was not renamed, so DSTDEV is not a deprecated name and has no .S-suffixed replacement.
• Confused with: DSTDEVP (the divisor and the minimum count) and STDEVA, which counts logicals and text.

Named variants

No named variants are recorded yet.