DVAR function
Estimates variance based on a sample from selected database entries.
Database · Guided Excel example
Where can you use it?
Teams working with structured records. When summarising matching records in a headed Excel list, match the criteria header to the source header.
Scenario in the workbook
Find the sample variance for each sales group. The criteria header must match Group in the source table.
A worked example from the workbook
| Row | A | B | C |
|---|---|---|---|
| 6 | Group | Amount | Criteria blocks |
| 7 | A | 12 | Group |
| 8 | A | 24 | A |
| 9 | A | 36 | Group |
| 10 | B | 8 | B |
| 11 | B | 16 | Group |
| 12 | B | 24 | C |
| 13 | C | 5 | — |
| 14 | C | 10 | — |
| 15 | C | 15 | — |
Record 1
=DVAR($A$6:$B$15,"Amount",$C$7:$C$8)Illustrative output144
See the inputs and instructions for all 3 examplesWorked examples
Find the sample variance for each sales group. The criteria header must match Group in the source table.
Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.
| Row | A | B | C |
|---|---|---|---|
| 6 | Group | Amount | Criteria blocks |
| 7 | A | 12 | Group |
| 8 | A | 24 | A |
| 9 | A | 36 | Group |
| 10 | B | 8 | B |
| 11 | B | 16 | Group |
| 12 | B | 24 | C |
| 13 | C | 5 | — |
| 14 | C | 10 | — |
| 15 | C | 15 | — |
| Trial | Expression | Sample output |
|---|---|---|
| Record 1 | =DVAR($A$6:$B$15,"Amount",$C$7:$C$8) |
144 |
| Record 2 | =DVAR($A$6:$B$15,"Amount",$C$9:$C$10) |
64 |
| Record 3 | =DVAR($A$6:$B$15,"Amount",$C$11:$C$12) |
25 |