fx
FunctionPedia
Quick Answer

DVAR Function

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

✓ Excel✓ Google SheetsExcel All versions

Syntax

DVAR
(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

Sample variance of Yield for apple trees taller than 10

=DVAR(A1:E7, "Yield", A9:B10)
Result8

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

Advanced Examples

Example 1: Compare with population version

Same data, DVARP instead

DVARP divides by n instead of n-1, giving a smaller value for the same two points.

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

Example 2: Wider sample of all apple trees

Criteria Tree=Apple across three rows

Compute the sample variance over three apple yields.

=DVAR(A1:E7, "Yield", A9:A10)
Result: 16
Apple yields 14, 10, 6 have mean 10; squared deviations 16, 0, 16 sum to 32; divided by 2 gives a sample variance of 16.

How DVAR Works

DVAR filters the database to rows matching the criteria, then computes the sample variance of the numeric values in the specified field: the sum of squared deviations from the mean divided by n-1 (the number of matching records minus one). Use DVAR when the matching records are a sample, not the whole population.

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 DVAR
Type =DVAR(A1:E7, "Yield", A9:B10) and press Enter.

Important Notes & Limitations

  • Assumes the matching records are a sample; use DVARP for an entire population.

  • Needs at least two matching numeric values, or the result is undefined (#DIV/0! when n is less than 2).

  • Criteria header labels must exactly match database headers.

Common Errors & Fixes

#DIV/0!Fewer than two records matched, so the sample (n-1) denominator is zero or negative.

Fix: Ensure the criteria match at least two records, or use a different statistic for single values.

#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 DVAR with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the DVAR 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 DVAR instead of DVARP?
Use DVAR when the matching rows are a sample of a larger population; use DVARP when they represent the whole population.
Why is my DVAR result larger than DVARP?
DVAR divides by n-1 while DVARP divides by n, so the sample estimate is generally larger.
What happens with only one matching row?
DVAR returns #DIV/0! because a sample needs at least two values.