DVAR Function in Microsoft Excel
Introduction
The DVAR function in Microsoft Excel is a database function that estimates the variance of a population based on a sample. It uses the numbers in a field (column) of records in a list or database that match the conditions that you specify.
Syntax & Arguments
The syntax for the DVAR function is as follows:
DVAR(database, field, criteria) Arguments:
- Database: This is the range of cells that comprise the list or database. A database is a list of related data in which rows of related information are records and columns of data are fields. The first row of the list contains labels for each column.
- Field: This indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as “Age” or “Yield,” or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
- Criteria: This is the range of cells containing the conditions you specify. You can use any content for the criteria argument as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
The function returns the variance of a population based on a sample by using the numbers in a field (column) of records in a list or database that match the conditions you specify.
Examples
Example 1
Purpose: To calculate the variance of sales volumes for a specific product category.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Sales | |
| 2 | Product A | Category 1 | 1200 | |
| 3 | Product B | Category 2 | 1500 | |
| 4 | Product C | Category 1 | 1300 | |
| 5 | Product D | Category 3 | 1100 | |
| 6 | Product E | Category 1 | 1400 | |
| 7 | Criteria | |||
| 8 | Category | |||
| 9 | Category 1 | |||
| 10 | Formula | |||
| 11 | =DVAR(A2:C6, "Sales", A8:A9) |
Explanation: This formula calculates the variance of sales volumes for products in “Category 1”. The database range is A2:C6, the field is “Sales”, and the criteria are specified in A8:A9. The result is the variance in the sales volumes of the products that meet the requirements.
Example 2
Purpose: To calculate the variance of the ages of employees in a specific department.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Department | Age | |
| 2 | Employee A | HR | 35 | |
| 3 | Employee B | Sales | 42 | |
| 4 | Employee C | HR | 39 | |
| 5 | Employee D | IT | 33 | |
| 6 | Employee E | HR | 37 | |
| 7 | Criteria | |||
| 8 | Department | |||
| 9 | HR | |||
| 10 | Formula | |||
| 11 | =DVAR(A2:C6, "Age", A8:A9) |
Explanation: This formula calculates the variance of the ages of employees in the HR department. The database range is A2:C6, the field is “Age”, and the criteria are specified in A8:A9. The result is the variance of the ages of the employees that meet the requirements.
Example 3
Purpose: To calculate the variance of the scores of students in a specific class.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Class | Score | |
| 2 | Student A | Class 1 | 85 | |
| 3 | Student B | Class 2 | 90 | |
| 4 | Student C | Class 1 | 88 | |
| 5 | Student D | Class 3 | 92 | |
| 6 | Student E | Class 1 | 87 | |
| 7 | Criteria | |||
| 8 | Class | |||
| 9 | Class 1 | |||
| 10 | Formula | |||
| 11 | =DVAR(A2:C6, "Score", A8:A9) |
Explanation: This formula calculates the variance of the scores of students in “Class 1”. The database range is A2:C6, the field is “Score”, and the criteria are specified in A8:A9. The result is the variance of the scores of the students that meet the requirements.
Tips and Tricks
- Make sure that the criteria range does not overlap the list.
- Do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot count the further information.
- To operate on an entire column in a database, enter a blank line below the column labels in the criteria range.