Excel > Function References > Database Functions

DVARP function

Calculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match conditions you specify.

Syntax

DVARP(database,field,criteria)

Argument	Description	Remarks
database	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.	None.
field	Indicates which column is used in the function. Field can be given as text with the column label enclosed in quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.	None.
criteria	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 underneath the column label for specifying the condition. Although the criteria range can be located anywhere on the sheet, do not place the criteria range underneath the list. If you add more information to the list by using the Form command on the Data menu, the new information is added to the first row underneath the list. If the row underneath the list is not blank, Excel cannot add the new information. Make sure that the criteria range does not overlap the list. To perform an operation on a whole column in a database, enter a blank line underneath the column labels in the criteria range.

Example

The following illustration shows a database for a small orchard. Each record contains information about one tree.

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.


1
2
3
4
5
6
7
8
9
10

A	B	C	D	E	F
Tree	Height	Age	Yield	Profit	Height
Apple	>10				<16
Pear
Tree	Height	Age	Yield	Profit
Apple	18	20	14	105.00
Pear	12	12	10	96.00
Cherry	13	14	9	105.00
Apple	14	15	10	75.00
Pear	9	8	8	76.80
Apple	8	9	6	45.00
Formula	Description (Result)
=DVARP(A4:E10,"Yield",A1:A3)	The true variance in the yield of apple and pear trees if the data in the database is the entire orchard population ( 7.04)

See also
DVAR function
VAR.S function
VARA function
VAR.P function
VARPA function
List of all functions (by category)

Share your feedback about this topic

Rate this content: