How to use the Excel DVAR function

The Excel DVAR function returns the variance of a sample extracted from records that match the given criteria. If data represents the entire population, use the DVARP function.

Syntax: =DVAR (database, field, criteria)

The DVAR function syntax has the following arguments:

  • Database    Required. The range of cells that makes up 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    Required. 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    Required. The range of cells that contains the conditions that you specify. You can use any range 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.

Example: Let’s look at some Excel DVAR function examples and explore how to use the DVAR function as a worksheet function in Microsoft Excel:

Example 1:

In the example below, the Dvar function is used to find the sample variance of scores obtained in Physics, by male children. The criteria are specified in cells G1 – H2 and the Dvar formula is shown in cell G3.

Syntax:  =DVAR(A1:E13,”Score”,G1:H2)

Result:

The above Dvar function calculates the sample variance of the values in cells E5, E8 & E11, and therefore returns the value 3.6%

Example 2:

In the example below, the Dvar function is used to find the sample variance for the Chemistry scores obtained by students over 8 years of age.

Syntax: =DVAR(A1:E14,”Score”,G1:H2)

Result:

The above Dvar function finds the sample variance of the values in cells E4, E10 & E13 and so returns the value 0.6%

In the above two examples, instead of typing in “Score” for the field argument, we could have simply used the number 5 (to denote the 5th column of the database).

Note:

  • DVAR is mean to calculate variance for a sample. If data represents the entire population, use the DVARP function.
  • DVAR supports some wildcards in criteria
  • Criteria can include more than one row (as explained above)
  • The field argument can be supplied as a name in double quotes (“”) or as a number representing field index.
  • The database and criteria ranges must include matching headers.

Add a Comment

Your email address will not be published. Required fields are marked *