DVAR Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate selected sample variance with DVAR in Google Sheets. Audit a packaging dataset, combine criteria, and interpret squared measurement units.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DVAR returns the sample variance of a chosen numeric database field after criteria select the records. The four Line A package weights below have expected sample variance 6.6666666667 grams squared.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Documentation was checked October 17, 2026. The formula examples were independently calculated from synthetic inputs, without native execution.
Syntax and arguments
=DVAR(database, field, criteria)
All arguments are required. database contains headings in its first row and records below. field is a heading string or a one-based position within that database. criteria is a separate range of headings and conditional tests. Google’s DVAR reference defines the result as sample variance.
Variance averages squared deviations using a sample denominator of n−1. Google’s VAR explanation distinguishes that denominator from population variance’s n. The numeric field’s units are squared, so a variance of 6.67 is not a typical weight difference of 6.67 grams.
Worked example: sampled package weights
Enter this dataset in A1:D8. These are seven inspected packages, rather than every package the lines produced.
| Row | A · Package | B · Line | C · Shift | D · Grams |
|---|---|---|---|---|
| 1 | Package | Line | Shift | Grams |
| 2 | P-01 | A | Day | 98 |
| 3 | P-02 | A | Day | 100 |
| 4 | P-03 | A | Day | 102 |
| 5 | P-04 | A | Day | 104 |
| 6 | P-05 | B | Day | 99 |
| 7 | P-06 | B | Day | 101 |
| 8 | P-07 | B | Night | 103 |
- Put
Linein F1 andAin F2. - Enter
=DVAR(A1:D8,"Grams",F1:F2)in H2. - Expect 6.666666666666667, apart from displayed rounding.
=DVAR(A1:D8,4,F1:F2)selects the same field by position.
For the selected weights 98, 100, 102, 104, the mean is 101. Deviations are −3, −1, 1, 3; their squares total 20. Dividing by 4−1 gives 20/3. For spread in the original unit, take the square root: =SQRT(H2) in H3 has expected result approximately 2.5819888975 grams.
Keep the number of sampled packages beside a variance report. A variance based on four values and one based on hundreds of representative values carry different evidential weight, even when their displayed numbers match.
Compare another selected sample
Change F2 to B. The selected weights are 99, 101, 103. Their mean is 101, squared deviations total 8, and sample variance is 8/(3−1) = 4.
The mean of both line samples is identical, but their variances differ. Conversely, equal variances would not establish equal average package weights. Variance concerns dispersion around each group’s own mean, not deviation from a specification such as exactly 100 grams.
For example, the Line A mean is one gram above that specification. Its average squared deviation from 100 would be 6, a different quantity from the sample variance 20/3. Keep target-error measures and variability measures separately labeled.
Advanced application: AND and OR criteria
Place Line and Shift in F5:G5, then B and Day in F6:G6. =DVAR(A1:D8,"Grams",F5:G6) selects 99 and 101. Expected result: 2, because the squared deviations from 100 total 2 and the denominator is 1.
For alternatives, enter this in F9:G11:
| Row | F · Line | G · Shift |
|---|---|---|
| 9 | Line | Shift |
| 10 | A | Day |
| 11 | B | Night |
=DVAR(A1:D8,"Grams",F9:G11) selects 98, 100, 102, 104, and 103. Expected variance: 5.8. Their mean is 101.4, squared deviations total 23.2, and the denominator is 4. The criteria mean (A and Day) or (B and Night), following Google’s database criteria rules.
Do not average the group variances to obtain the combined sample variance. Combining groups changes the overall mean and requires the observations or sufficient group totals.
Errors and troubleshooting
A selected sample needs at least two numeric observations for its n−1 denominator. Selecting one package is not a valid sample-variance estimate. The DVAR-specific reference does not provide a complete error-token table, so no exact application error is invented here.
Check headers, field choice, and selected records before debugging numerical precision. A blank extra criteria row can broaden the filter because empty tests impose no restriction. Avoid open-ended criteria areas filled with unused rows. Numeric-looking text, missing weights, and source errors should be corrected or explicitly handled in the data preparation step; silently substituting zero changes the measured process.
For a report, retain original precision in calculations and round only the display. If you convert every weight from grams to kilograms, variance changes by the square of 0.001: 20/3 grams² becomes approximately 0.000006666666667 kilograms².
Availability and related guides
DVAR is documented for current Google Sheets. First introduction, last functioning release, and retirement are not established by the reviewed sources. This guide describes ordinary cells, not the separate BigQuery VAR syntax. No cross-engine coercion or error equivalence is claimed.
First-party references
Reviewed October 17, 2026: DVAR, database criteria, and sample variance arithmetic.