DVARP Function

Calculates the variance of matching records, treating them as the whole population.

Category

Database

Available in

All versions, Microsoft 365

Returns

A number

Want to practice DVARP in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

DVARP returns the variance of the matching records when they are the entire population (divides by n, like VAR.P).

Use DVAR when the records are only a sample.

Syntax

=DVARP(database, field, criteria) Explain

Arguments

ArgumentRequired?What it does
databaseYesThe whole list, including the header row — e.g. A1:E7. Each row is a record, each column a field.
fieldYesWhich column to calculate: its header in quotes ("Sales"), a cell holding the header, or its position (5 = the fifth column).
criteriaYesA small range with at least one header copied exactly from the list and the conditions underneath, e.g. G1:G2.

Examples

Example 1 — Population variance of Paper sales

All three Paper orders, treated as the full set.

G5fx=DVARP(A1:E7,"Sales",G1:G2)
ABCDEFG
1RegionRepProductUnitsSalesProduct
2NorthAmaraPaper40520Paper
3SouthBenToner12576
4NorthChloeToner8384Variance (pop.)
5EastDevPaper253256572.22
6NorthAmaraPens100120
7SouthEllaPaper30390
=DVARP(A1:E7,"Sales",G1:G2) Explain

Common mistakes

  • Sample or population? If the records are a sample, use DVAR, which gives a slightly larger answer.

Notes

  • Criteria rules: conditions on the same row must all be true (AND); conditions on separate rows are alternatives (OR). Text such as Nor matches anything that begins with it — type ="=North" for an exact match. Use >500, <>East, or wildcards like *Paper*.
  • Related functions: DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.