DVARP in Collabora Office: Filtered Population Variance
Describe variance across selected numeric records, check incomplete measurements, and see why averaging group variances gives the wrong combined result.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DVARP calculates population variance for the numeric values in a database field after criteria select the records. For 4, 8 and 12 minutes, the expected result is 32/3, approximately 10.666667 square minutes.
Availability and version history
The official DVARP reference was retrieved on October 22, 2026. Its saved page is labeled Collabora Office 26.04 Help, while /latest/ remains a rolling URL. It does not establish the first release, current free-edition entitlement, an installed build, or Online/mobile parity. This guide combines that documentation with independent arithmetic, not native Collabora execution.
Syntax and what population means
=DVARP(Database; DatabaseField; SearchCriteria)
All three arguments are required. Include the header row in the database and matching headers in the separate criteria range. A quoted header, header-cell reference or one-based database-column position identifies the calculation field.
Use population variance when describing the entire set of numeric observations you have defined as the population. DVARP cannot tell whether your data actually contains every required observation. If some runs are still unmeasured, a result for the recorded values must not be presented as complete coverage of all planned runs.
Set up the practice database
Enter this original practice log in A1:D11. Type pending as text. Keep the two cells marked leave empty physically empty; do not enter those words or an empty-string formula.
| Run | Batch | Shift | Minutes |
|---|---|---|---|
| 101 | 10 | 1 | 4 |
| 102 | 10 | 1 | 8 |
| 103 | 10 | 2 | 12 |
| 104 | 10 | 2 | pending |
| 105 | 10 | 2 | leave empty |
| 106 | 20 | 1 | 6 |
| 107 | 20 | 1 | 6 |
| 108 | 20 | 2 | 6 |
| 109 | 30 | 1 | pending |
| 110 | 30 | 2 | leave empty |
Put Batch in F1 and numeric 10 in F2. Enter output formulas away from the database and criteria, for example in H1 downward. The formulas use English names and semicolon separators.
Example 1: describe the recorded values
=DVARP(A1:D11;"Minutes";F1:F2)
Expected result: 32/3, approximately 10.666667. The selected numeric values are 4, 8 and 12. Their mean is 8 and their squared deviations total 32. Population variance divides by 3, the number of numeric observations.
=DCOUNT(A1:D11;"Minutes";F1:F2) gives 3, while =DCOUNT(A1:D11;;F1:F2) gives 5 matching records. The result therefore describes three recorded times, not five complete measurements. DCOUNT is a useful completeness check beside the variance.
If the three values are a sample for estimating a broader population’s variance, DVAR instead gives 16. Do not select the function merely because one number is smaller.
Example 2: combine groups without averaging their variances
For Batch 20, change F2 to 20. Its three numeric measurements are 6, 6 and 6, so the expected DVARP result is 0.
To describe both batches together, set F2 to the criterion <=20. The selected numeric values are now 4, 8, 12, 6, 6 and 6. The combined result is not the simple average of the two batch variances.
| Calculation | Expected value |
|---|---|
| Combined mean | 7 |
| Combined squared deviations | 9 + 1 + 25 + 1 + 1 + 1 = 38 |
=DVARP(A1:D11;"Minutes";F1:F2) |
38/6 = 19/3, approximately 6.333333 |
| Incorrect average of the two group variances | (32/3 + 0)/2 = 16/3, approximately 5.333333 |
Both batches have three numeric observations, but their means differ: 8 versus 6. Averaging their internal variances loses the variation between the batch means. Recalculate on the intended union of records instead.
Example 3: define a narrower population explicitly
Put Batch and Shift in F4:G4, then numeric 10 and 1 in F5:G5. The same-row conditions require both matches.
=DVARP(A1:D11;"Minutes";F4:G5)
Expected result: 4. Only 4 and 8 minutes remain, their mean is 6, and their squared deviations total 8. Dividing by the two numeric observations gives 4. The result now describes a different population, not an improvement in the original batch’s variability.
For other AND/OR layouts and overlapping conditions, use DSUM’s shared criteria guide rather than adding independently calculated variances.
Troubleshooting missing data and unexpected results
- No records match: with F2 set to 99, the official DVARP reference specifies #NUM!. This is a source-documented outcome, not an observed native result.
- Records match but no measurements are numeric: with F2 set to 30, the two selected Minutes cells contain text and an empty cell. The source again specifies #NUM!.
- A valid result is zero: Batch 20 contains three equal numeric measurements. Its independently calculated zero reflects no spread in those observations. An empty selection is different.
- The result is larger than expected: check the selected field and units. The result is in square minutes, not minutes. DSTDEVP returns the corresponding population standard deviation.
- The subset is wrong: confirm that F1 exactly matches Batch, the database includes A1:D1, and the formula references only the intended criteria rows. Text/wildcard matching depends on calculation settings; these examples use numeric criteria to avoid that ambiguity.
Do not copy DVAR’s documented no-match zero into DVARP. Numeric-looking text, formula-empty strings, error-valued fields and platform-specific behavior are outside these controls.
References and verification
- Collabora Help: DVARP contract, syntax and boundaries.
- Shared database arguments and criteria.
- Editorial testing standards.
Row selections, counts, exact variances and rounded values were independently checked with rational arithmetic and Python statistics. Error outcomes above are documentation statements, not mathematical test outputs or native observations. No native execution or screenshot is claimed.