DVARP in ONLYOFFICE: Population Variance for Matching Database Records

=DVARP(database, field, criteria)

Database includes its first-row headers. Field is a quoted header or a one-based column position within that database. Criteria is a separate block with recognized headers and at least one condition row. The official help documents this three-argument interface. Keep headers unique in the database and copy criteria headers exactly.

Create a blank sheet named DatabaseChecks. Enter this database in A1:D6, including the header row. Entries marked empty must be genuinely empty cells, not the word “empty”, a space or a formula returning empty text.

These are fictional records. Amount is deliberately numeric, including a genuine zero. Note provides a separate text/blank field for count comparisons. Headers are unique and consistent.

Build these separate criteria blocks; none overlaps A1:D6:

Enter >=20 as criterion text, not a formula. Place result formulas in U2:U15, outside every data/criteria block. The extra spaces used in this explanation are not part of the header text.

The three selected values have mean 20. Their squared deviations 100,0,100 sum to 200, and 200/3 is the population variance. If the values measure hours, the variance is expressed in hours squared.

Enter 10,20,30 in W2:W4, calculate their mean in Y2 and square deviations in X2:X4 with =(W2-$Y$2)^2. Use =SUM(X2:X4)/COUNT(W2:W4) as the independent target calculation. Taking its square root gives 8.16496580927726, the population standard-deviation target from DSTDEVP.

For 20,30, the numerator 50 is divided by 2. For the four East/West values,500 is divided by 4. The all-record numerator 1000 is divided by 5. The unique value 20 has no deviations from its own mean and therefore has variance 0.

For the East records, DVAR gives the sample target 100 while DVARP gives 200/3. Their ratio is 3/2. This difference follows from the divisor, so compare formulas only after confirming that both use the same database, field and criteria range.

Population variance describes the complete group chosen for the question. It does not automatically become the right estimator for a larger process because all currently available rows were included. Choose the interpretation before looking at the results.

Scaling every selected amount by−2 multiplies population variance by 4, giving the East target 800/3. The sign disappears because deviations are squared. Translating all values by a constant leaves variance unchanged. These independently derived properties can help diagnose unit mistakes, but they are not proof of native application behavior.

The recognized South criterion is source-predicted #DIV/0!, distinct from the valid zero variance of a numeric singleton. A matching record with an empty selected Note may also return the shared helper’s empty-result error. Do not convert all errors to 0 and call the subset constant.

The typed aggregation collects numbers and propagates error elements, but the following deviation loop iterates the original selected length rather than the numeric-only length. If selected values include retained text, the filtered numeric array can be shorter; the resulting undefined access can create NaN. The number constructor maps a nonfinite numeric value to #NUM! in the checked code. Thus this guide deliberately avoids a blanket “text is ignored” promise.

Use a clean numeric Amount field for the worked examples. Inspect matching counts and types before treating a very small or zero variance as meaningful. The implementation is not interchangeable with DVAR or DSTDEVP for every mixed-type edge case merely because their mathematical formulas are related.

Conditions in one criteria row are combined with AND; separate rows supply OR alternatives. The first main OR row above matches East, and the second matches West. A record matching both alternatives is collected once in the inspected helper.

A blank criterion means no restriction for that column in the checked source. Thus M1:M2 selects all records; it is not a request to find blank Team cells. An accidental blank OR row can similarly broaden the selection. Keep the criteria rectangle tight rather than including unused rows for convenience.

Bare text is another trap: the helper appends a wildcard to a text criterion without an operator. East can therefore match a longer prefix such as Eastside. This database contains no such team, so the main results are unambiguous. To request explicit equality in a future test, store =East as text, for example by typing a leading apostrophe before it; do not accidentally enter it as a formula. Wildcard, case and locale edge behavior needs its own bounded validation.

There is also a version-specific OR caveat. The inspected helper can return early when the first recognized criteria row has no matches, before considering later OR rows. Do not promise order-independent OR behavior from this implementation. Reversing a deliberately unmatched first row and a matching second row is a separate source-predicted regression probe, not a native test performed here.

Unknown criterion headers are not a safe way to request “no match”: the helper skips unrecognized headers and, if none are recognized, can select every record. Copy valid headers exactly and distinguish a misspelled criterion heading from an unknown selected field. These observations come from the shared source helper, not from applying another product’s database-function rules.

DPRODUCT, DSTDEV, DSTDEVP, DVAR provide related database aggregations. DAVERAGE describes the selected mean, DCOUNT helps audit clean numeric counts, and DSUM adds selected values.

The function is currently documented in ONLYOFFICE’s unversioned Spreadsheet Editor help. Its first introduction release/date, final supported version and any retirement announcement are not established by these sources. Documentation presence does not establish support in every older Desktop Editors build, Docs deployment or mobile client.

This guide checks official SDKJS tag v10.0.0.169, commit 3679e0375a758d7dc59d31337a45461be3525ed8, alongside current help on October 18, 2026. Main arithmetic is independently calculated; implementation-specific branches are labeled source-predicted. No ONLYOFFICE engine or application was executed for these examples. No native screenshots or recalculated workbook are claimed.

Formulas use English names and comma separators. Adapt separators to the actual editor locale. Similar function names elsewhere do not guarantee identical criteria matching, text coercion, errors or blank-field handling. Keep the database header in the formula range and preserve genuinely empty cells when transferring the example.

• Official DVARP reference: purpose and arguments.
• Pinned shared criteria helper: matching and selected-field handling.
• Pinned DVARP calculation: operation-specific implementation.
• Pinned numeric-result constructor: nonfinite numeric-result handling.