DVARP Function in Google Sheets: Formula, Examples & Troubleshooting
Use DVARP in Google Sheets to calculate selected population variance, inspect squared units, and avoid averaging subgroup variances incorrectly.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DVARP calculates population variance for one field in the records selected by a database criteria range. For the complete East sensor set below, the expected temperature variance is 8/3, approximately 2.666667 degrees Celsius squared.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Google’s documentation was reviewed October 17, 2026. Expected outputs are independently derived from the displayed synthetic readings, not native observations.
Syntax and interpretation
=DVARP(database, field, criteria)
database includes its header row; field identifies a heading or one-based column position; criteria contains field headings and selection tests. All three are required. Google documents DVARP as population variance, distinct from the sample statistic DVAR. DVARP reference.
Population variance divides the sum of squared deviations from the population mean by n. The population here is the defined set of current sensor readings, not every possible temperature the building may experience. A population calculation is only as complete as the population definition and its records.
Prepare the complete sensor inventory
Enter A1:D6:
| Row | A · Sensor | B · Wing | C · Floor | D · Celsius |
|---|---|---|---|---|
| 1 | Sensor | Wing | Floor | Celsius |
| 2 | S-01 | East | 1 | 18 |
| 3 | S-02 | East | 1 | 20 |
| 4 | S-03 | East | 2 | 22 |
| 5 | S-04 | West | 1 | 24 |
| 6 | S-05 | West | 2 | 26 |
- Put
Wingin F1 andEastin F2. - Enter
=DVARP(A1:D6,"Celsius",F1:F2)in H2. - Expected result: 2.666666666666667.
- In H3,
=DVARP(A1:D6,4,F1:F2)has the same expected result.
The selected readings are 18, 20, and 22. Their mean is 20; the squared deviations 4, 0, 4 total 8. The population variance is 8/3. The corresponding population standard deviation is sqrt(8/3), approximately 1.632993161855452 degrees Celsius. Squared-unit variance and original-unit standard deviation should not share a mislabeled chart axis.
Change F2 to West. For readings 24 and 26, the expected variance is 1: mean 25, squared deviations 1 and 1, divided by two.
Advanced application: combined variance is not average variance
Set F5 to Wing, F6 to East, and F7 to West. The criteria have two alternative rows. Enter:
=DVARP(A1:D6,"Celsius",F5:F7)
Expected result: 8. All five readings are selected; their overall mean is 22. Squared deviations are 16, 4, 0, 4, 16, totaling 40. Dividing by 5 gives 8.
The population-weighted average of the two wing variances would be only 2: (3*(8/3)+2*1)/5. It omits the separation between the wing means, 20 and 25. The between-wing contribution is 6, giving the combined variance 2+6 = 8. This independent decomposition is a useful audit when a dashboard aggregates regional summaries.
Keep subgroup counts and means with any stored variance. Without them, a downstream report cannot reconstruct combined variation from subgroup variances alone.
Apply two criteria on one row
Put Wing in F9, Floor in G9, East in F10, and 1 in G10. Then =DVARP(A1:D6,"Celsius",F9:G10) selects 18 and 20 and has expected result 1.
Google’s database criteria guide defines same-row restrictions as AND and separate rows as OR. Empty conditions impose no restriction. A range containing an unused blank condition row can therefore select more than the intended wing and floor.
Calibration and units
Suppose a verified calibration correction adds exactly 2 degrees to every sensor. The corrected East values become 20, 22, 24, and their mean becomes 22, but their population variance remains 8/3. Shifting every value and its mean by the same amount leaves deviations unchanged.
Scaling is different. Multiplying every reading by a factor multiplies variance by the factor squared. This follows from squaring the scaled deviations. A change of unit or normalization requires corresponding changes to a variance report, not just a new label.
Errors and troubleshooting
Check the selected records, database headings, and the field name before changing the statistical function. The header row is part of A1:D6; beginning the database at A2 loses that schema. A column index is relative to the database, so field 4 means Celsius even if the table moves to a different worksheet column.
The function-specific source does not establish exhaustive error codes for empty results, invalid fields, or mixed-type data. No native error table is invented here. Use complete numeric readings for the worked checks, investigate missing records, and do not substitute zeros for unknown temperatures. A zero can be a legitimate reading and changes the mean and variance.
Filtering a complete, explicitly defined group is compatible with a population statistic. Treating a small sample as the complete population is a modeling mistake that the formula cannot detect.
Availability and first-party sources
The current rolling Google Sheets service documents DVARP. Its first introduction and last functioning release are not established; no retirement is established. This is a documentation check rather than a software-version execution test. Cross-product error/coercion behavior is not presumed equivalent.
Related published guides: VAR.P, STDEV.P, and FILTER.
Reviewed October 17, 2026: DVARP, database criteria, and Google’s sample/population denominator explanation.