fx
FunctionPedia
Quick Answer

DVARP Function

Population variance of a database column for rows that meet the criteria.

✓ Excel✓ Google SheetsExcel All versions

Syntax

DVARP
(database, field, criteria)

Parameters

ParameterDescriptionRequired
databaseThe range containing the database, including the header row as the first row.Required
fieldThe column to analyze, given as the column label in quotes (e.g. "Yield") or its 1-based column index.Required
criteriaThe range with a header row matching database column names and one or more condition rows; only matching records are analyzed.Required

Basic Example

Population variance of Yield for apple trees taller than 10

=DVARP(A1:E7, "Yield", A9:B10)
Result4

The two matching rows have Yield 14 and 10. The mean is 12, squared deviations are 4 and 4 (sum 8), divided by n = 2 gives a population variance of 4.

Advanced Examples

Example 1: Compare with sample version

Same data, DVAR instead

DVAR divides by n-1, so it is larger for the same two points.

=DVAR(A1:E7, "Yield", A9:B10)
Result: 8
With n=2, the sample variance is 8/1 = 8, versus the population variance 4.

Example 2: Population over all apple trees

Criteria Tree=Apple across three rows

Compute the population variance over three apple yields.

=DVARP(A1:E7, "Yield", A9:A10)
Result: 10.6666666666667
Apple yields 14, 10, 6 have mean 10; squared deviations 16, 0, 16 sum to 32; divided by 3 gives a population variance of about 10.667.

How DVARP Works

DVARP filters the database to rows matching the criteria, then computes the population variance of the numeric values in the specified field: the sum of squared deviations from the mean divided by n (the number of matching records). Use DVARP when the matching records constitute the whole population rather than a sample.

1
Set up the database
Enter your table with a header row, e.g. A1:E7 with Tree, Height, Age, Yield, Profit.
2
Set up criteria
Place criteria headers and values in a separate range, e.g. A9:B10.
3
Enter DVARP
Type =DVARP(A1:E7, "Yield", A9:B10) and press Enter.

Important Notes & Limitations

  • Assumes the matching records are the whole population; use DVAR for a sample.

  • Needs at least one matching numeric value; with none it returns #DIV/0!.

  • Criteria header labels must exactly match database headers.

Common Errors & Fixes

#DIV/0!No records matched, so there are no values to compute a deviation from.

Fix: Verify the criteria so at least one record matches.

#VALUE!The field was an unquoted column name.

Fix: Wrap the field name in quotes, e.g. "Yield", or use the column index.

Download Practice File

Practice DVARP with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the DVARP function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

When should I use DVARP instead of DVAR?
Use DVARP when the matching rows represent the entire population; use DVAR when they are just a sample.
Why is my DVARP result smaller than DVAR?
DVARP divides by n while DVAR divides by n-1, so the population estimate is generally smaller.
What happens with only one matching row?
DVARP returns 0, since a single value has no deviation from itself.