Does DVAR calculate sample variance or population variance?

DVAR calculates sample variance using the n-1 denominator formula. This is appropriate when your database represents a sample. If you need population variance, you would need to manually adjust by multiplying by (n-1)/n or rebuilding the formula.

How do I set up criteria that match multiple conditions?

Include additional columns in your criteria range with their respective headers and values. For example, if you want rows where Category="Groceries" AND Month="February", put those headers and values in your criteria range. DVAR applies AND logic across all columns.

What's the difference between DVAR and DSTDEV?

DVAR returns variance (the squared deviation), while DSTDEV returns standard deviation (the square root of variance). Use DVAR when analyzing variance directly, and DSTDEV when you need to compare spread in the same units as your data.

Why does my DVAR formula return #N/A even though the data looks correct?

Check that your field name matches a column header exactly (including case and spaces), and that at least one row in your database matches all your criteria. A single mismatch in spelling or criteria will cause #N/A.

DVAR function

Returns the variance of values in a database column that match specified criteria, measuring spread in a filtered dataset.

=DVAR(database, field, criteria)

Generate a DVAR formula

Describe what you need. The generator will reach for DVAR where DVAR is the right tool, and tell you when it is not.

Arguments

How DVAR reads its arguments

Argument	Required	Description
database	Required	The range containing the data table, including headers; typically a contiguous block like A1:E9. Must include all columns that might be referenced.
field	Required	The column to calculate variance for, specified as either the header name in quotes (e.g., "Actual") or the column number within the database range (e.g., 4).
criteria	Required	A range defining which rows match; typically two rows with column headers in the first and criteria values in the second. All criteria must match for a row to be included.

Returns

A single number representing the sample variance of the selected field values.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate spending variance for a single category

Category	Month	Budgeted	Actual	Variance
Groceries	January	300	280	-20
Utilities	January	150	160	10
Transport	January	200	190	-10
Groceries	February	300	320	20
Utilities	February	150	150	0
Transport	February	200	210	10
Groceries	March	300	300	0
Utilities	March	150	145	-5
Transport	March	200	220	20
Category
Groceries

=DVAR(A1:E9,"Actual",A12:A13)

Result: 400

This calculates variance of actual grocery spending across three months: 280, 320, and 300. With a mean of 300, the variance is 400, indicating moderate variability. This tells you how much grocery spending fluctuated around its average.

2. Measure budget spread across categories in a single month

Category	Month	Budgeted	Actual	Variance
Groceries	January	300	280	-20
Utilities	January	150	160	10
Transport	January	200	190	-10
Groceries	February	300	320	20
Utilities	February	150	150	0
Transport	February	200	210	10
Groceries	March	300	300	0
Utilities	March	150	145	-5
Transport	March	200	220	20
Month
February

=DVAR(A1:E9,"Budgeted",B12:B13)

Result: 5833.33

February budgets are 300 (Groceries), 150 (Utilities), and 200 (Transport). These vary significantly from their mean of 216.67, yielding a variance of 5833.33. High variance here shows that budget allocation is unequal across categories.

3. Analyze consistency of actual-to-budget performance

Category	Month	Budgeted	Actual	Variance
Groceries	January	300	280	-20
Utilities	January	150	160	10
Transport	January	200	190	-10
Groceries	February	300	320	20
Utilities	February	150	150	0
Transport	February	200	210	10
Groceries	March	300	300	0
Utilities	March	150	145	-5
Transport	March	200	220	20
Category
Transport

=DVAR(A1:E9,"Variance",A12:A13)

Result: 233.33

Transport's variance column shows -10, 10, and 20, meaning it ranged from 10 under budget to 20 over. The variance of 233.33 reflects this inconsistency. Use this to identify categories with unpredictable spending patterns.

Common errors

Which DVAR error are you seeing?

Error	Why it happens	How to fix it
#N/A	The field argument doesn't match any column header in the database, or no rows satisfy the criteria.	Check that the field name exactly matches a column header (including capitalization), or use the correct column number. Verify your criteria are set up to match at least one row.
#REF!	The criteria range references cells that have been deleted, moved, or are on a sheet that no longer exists.	Verify the criteria range still exists and refers to the correct location. Use the Name Manager to check named ranges if applicable. Rewrite the formula with updated cell references.
#VALUE!	The criteria range structure is malformed (e.g., missing a header row, or not in a proper two-row format), or the field parameter is of an unexpected type.	Ensure criteria range has exactly two rows: headers in row one and criteria values in row two. Check that field is either text (in quotes) or a valid positive integer representing a column number.

Tips and when to use something else

DVAR calculates sample variance (using n-1 denominator). If you need standard deviation instead, use DSTDEV with the same criteria.
Use multiple criteria by including multiple columns in your criteria range, with one column per criterion. DVAR matches all criteria simultaneously.
For average instead of variance, use DAVERAGE; for sum, use DSUM. Choose the database function that matches your analysis question.
When criteria are complex, consider using FILTER or array formulas as an alternative, especially for dynamic datasets or multiple conditions combined with OR logic.

Related functions

DSTDEVReturns the standard deviation of values in a database field that match criteria, showing how much the numeric data spreads out.DAVERAGECalculates the average of values in a database field that meet specified criteria, useful for finding filtered averages without creating separate tables.DSUMDSUM sums values in a database column that meet specified criteria—use it to total filtered column values from a data table.DCOUNTDCOUNT counts cells in a database field that meet specified criteria, returning the total count of matching non-blank cells.

Frequently asked questions

Does DVAR calculate sample variance or population variance?: DVAR calculates sample variance using the n-1 denominator formula. This is appropriate when your database represents a sample. If you need population variance, you would need to manually adjust by multiplying by (n-1)/n or rebuilding the formula.
How do I set up criteria that match multiple conditions?: Include additional columns in your criteria range with their respective headers and values. For example, if you want rows where Category="Groceries" AND Month="February", put those headers and values in your criteria range. DVAR applies AND logic across all columns.
What's the difference between DVAR and DSTDEV?: DVAR returns variance (the squared deviation), while DSTDEV returns standard deviation (the square root of variance). Use DVAR when analyzing variance directly, and DSTDEV when you need to compare spread in the same units as your data.
Why does my DVAR formula return #N/A even though the data looks correct?: Check that your field name matches a column header exactly (including case and spaces), and that at least one row in your database matches all your criteria. A single mismatch in spelling or criteria will cause #N/A.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17