In this article
DVAR returns the sample variance for matching records. Like all D-functions it uses a database range and a criteria range you can edit on the sheet.
Advertisement
Syntax
=DVAR(database, field, criteria)| Argument | What it means |
|---|---|
database |
The table including headers |
field |
Column name in quotes or its number |
criteria |
Range with headers and conditions |
Examples
Example 1
=IFERROR(DVAR(DB,"Sales",CHOOSE(A2,CritA,CritB,CritC)),"error "&ERROR.TYPE(DVAR(DB,"Sales",CHOOSE(A2,CritA,CritB,CritC))))Result: 4500000. Database H1:J6 (North 12000, South 8000, North 15000, West 22000, South 9000) with three criteria blocks.
DVAR in practice
Where you will use it
- Reports where users edit the criteria on the sheet
- OR conditions on separate rows
- Legacy templates
Worked example
Database H1:J6 (North 12000, South 8000, North 15000, West 22000, South 9000) with three criteria blocks.
| Case | Criteria | Formula | Result |
|---|---|---|---|
| 1 | Region = North | =IFERROR(DVAR(DB,"Sales",CHOOSE(A2,CritA,CritB,CritC)),"error "&ERROR.TYPE(DVAR(DB,"Sales",CHOOSE(A2,CritA,CritB,CritC)))) | 4500000 |
| 2 | Sales > 10000 | =IFERROR(DVAR(DB,"Sales",CHOOSE(A3,CritA,CritB,CritC)),"error "&ERROR.TYPE(DVAR(DB,"Sales",CHOOSE(A3,CritA,CritB,CritC)))) | 26333333.33 |
| 3 | North OR South | =IFERROR(DVAR(DB,"Sales",CHOOSE(A4,CritA,CritB,CritC)),"error "&ERROR.TYPE(DVAR(DB,"Sales",CHOOSE(A4,CritA,CritB,CritC)))) | 10000000 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Criteria headers that don't match | Headers must match the database exactly |
| One matching record for D-SD/VAR | Sample versions need 2+ records (#DIV/0!) |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Advertisement
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong