Excel DVAR Function

Microsoft Excel DVAR Function

Basic Description

The Excel Dvar function calculates the sample variance of a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DVAR( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, for which the sample variance is to be calculated. This can either be a field number, or it can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Subject", "Name", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below. eg. Age Name >8 Amy

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character

*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria supplied beneath each field heading can be either:

	-	a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE)
or
	-	a text string (eg. "Name", "Monday")
or
	-	an expression (eg. ">8", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="Name" will be satisfied by cells containing the text "Name" or "name".

Excel Dvar Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	10	Math	63%
3	Amy	Female	10	English	78%
4	Amy	Female	10	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	51%
8	Sam	Male	9	Math	39%
9	Sam	Male	9	English	52%
10	Sam	Male	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

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

	G	H
1	Subject	Gender
2	Math	Male
3	=DVAR( A1:E13, "Score", G1:H2 )

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

Example 2

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

	G	H
1	Subject	Age
2	Science	>8
3	=DVAR( A1:E13, "Score", G1:H2 )

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

Note that, 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).