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 that you specify.
Syntax
DVARP(database,field,criteria)
Database is 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 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 is 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.
Criteria examples
Important
 Because the equal sign is used to indicate a formula when you type text or a value in a cell, Microsoft Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:
=''=entry''
Where entry is the text or value you want to find. For example:
What you type in the cell 
What Excel evaluates and displays 
="=Davolio" 
=Davolio 
="=3000" 
=3000 
The following sections provide examples of complex criteria.
Multiple criteria in one column
Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")
To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range.
In the following data range (A6:C10), the criteria range (B1:B3) displays the rows that contain either "Davolio" or "Buchanan" in the Salesperson column (A8:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 

=Davolio 

3 

=Buchanan 

4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple criteria in multiple columns where all criteria must be true
Boolean logic: (Type = "Produce" AND Sales > 1000)
To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range.
In the following data range (A6:C10), the criteria range (A1:C2) displays all rows that contain "Produce" in the Type column and a value greater than $1,000 in the Sales column (A9:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 
=Produce 

>1000 
3 



4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple criteria in multiple columns where any criteria can be true
Boolean logic: (Type = "Produce" OR Salesperson = "Davolio")
To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in different rows of the criteria range.
In the following data range (A6:C10), the criteria range (A1:B3) displays all rows that contain "Produce" in the Type column or "Davolio" in the Salesperson column (A8:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 
=Produce 


3 

=Davolio 

4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple sets of criteria where each set includes criteria for multiple columns
Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )
To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate rows.
In the following data range (A6:C10), the criteria range (B1:C3) displays the rows that contain both "Davolio" in the Salesperson column and a value greater than $3,000 in the Sales column, or displays the rows that contain "Buchanan" in the Salesperson and a value greater than $1,500 in the Sales column (A9:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 

=Davolio 
>3000 
3 

=Buchanan 
>1500 
4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple sets of criteria where each set includes criteria for one column
Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )
To find rows that meet multiple sets of criteria, where each set includes criteria for one column, include multiple columns with the same column heading.
In the following data range (A6:C10), the criteria range (C1:D3) displays rows that contain values between 5,000 and 8,000 and values less than 500 in the Sales column (A8:C10).

A 
B 
C 
D 
1 
Type 
Salesperson 
Sales 
Sales 
2 


>6000 
<6500 
3 


<500 

4 




5 




6 
Type 
Salesperson 
Sales 

7 
Beverages 
Suyama 
$5122 

8 
Meat 
Davolio 
$450 

9 
produce 
Buchanan 
$6328 

10 
Produce 
Davolio 
$6544 

Top of Page
Criteria to find text values that share some characters but not others
To find text values that share some characters but not others, do one or more of the following:
 Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."
 Use a wildcard character.
How?
The following wildcard characters can be used as comparison criteria.
Use 
To find 
? (question mark) 
Any single character For example, sm?th finds "smith" and "smyth" 
* (asterisk) 
Any number of characters For example, *east finds "Northeast" and "Southeast" 
~ (tilde) followed by ?, *, or ~ 
A question mark, asterisk, or tilde For example, fy91~? finds "fy91?" 
In the following data range (A6:C10), the criteria range (A1:B3) displays rows with "Me" as the first characters in the Type column or rows with the second character equal to "u" in the Salesperson column (A7:C9).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 
Me 


3 

=?u* 

4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Criteria created as the result of a formula
You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) as your criterion. Remember the following important points:
 The formula must evaluate to TRUE or FALSE.
 Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:
=''=entry''
 Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match).
If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.
The following subsections provide specific examples of criteria created as the result of a formula.
Top of Page
Filtering for values greater than the average of all values in the data range
In the following data range (A6:D10), the criteria range (D1:D2) displays rows that have a value in the Sales column greater than the average of all the Sales values (C7:C10). In the formula, "C7" refers to the filtered column (C) of the first row of the data range (7).

A 
B 
C 
D 
1 
Type 
Salesperson 
Sales 
Calculated Average 
2 



=C7>AVERAGE($C$7:$C$10) 
3 




4 




5 




6 
Type 
Salesperson 
Sales 

7 
Beverages 
Suyama 
$5122 

8 
Meat 
Davolio 
$450 

9 
produce 
Buchanan 
$6328 

10 
Produce 
Davolio 
$6544 

Top of Page
Filtering for text by using a casesensitive search
In the data range (A6:D10), the criteria range (D1:D2) displays rows that contain "Produce" in the Type column by using the EXACT function to perform a casesensitive search (A10:C10). In the formula, "A7" refers to the filtered column (A) of the first row of the data range (7).

A 
B 
C 
D 
1 
Type 
Salesperson 
Sales 
Exact Match 
2 



=EXACT(A7, "Produce") 
3 




4 




5 




6 
Type 
Salesperson 
Sales 

7 
Beverages 
Suyama 
$5122 

8 
Meat 
Davolio 
$450 

9 
produce 
Buchanan 
$6328 

10 
Produce 
Davolio 
$6544 

Remarks
 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 for specifying the condition.
For example, if the range G1:G2 contains the column label Income in G1 and the amount $10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.
 Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below 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 below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
 Make sure that the criteria range does not overlap the list.
 To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

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) 




=DCOUNT(A4:E10,"Age",A1:F2) 
This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers. (1) 




=DCOUNTA(A4:E10,"Profit",A1:F2) 
This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank. (1) 




=DMAX(A4:E10,"Profit",A1:A3) 
The maximum profit of apple and pear trees. (105) 




=DMIN(A4:E10,"Profit",A1:B2) 
The minimum profit of apple trees over 10 in height. (75) 




=DSUM(A4:E10,"Profit",A1:A2) 
The total profit from apple trees. (225) 




=DSUM(A4:E10,"Profit",A1:F2) 
The total profit from apple trees with a height between 10 and 16. (75) 




=DPRODUCT(A4:E10,"Yield",A1:B2) 
The product of the yields from apple trees with a height greater than 10. (140) 




=DAVERAGE(A4:E10,"Yield",A1:B2) 
The average yield of apple trees over 10 feet in height. (12) 




=DAVERAGE(A4:E10,3,A4:E10) 
The average age of all trees in the database. (13) 




=DSTDEV(A4:E10,"Yield",A1:A3) 
The estimated standard deviation in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (2.97) 




=DSTDEVP(A4:E10,"Yield",A1:A3) 
The true standard deviation in the yield of apple and pear trees if the data in the database is the entire population. (2.65) 




=DVAR(A4:E10,"Yield",A1:A3) 
The estimated variance in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (8.8) 




=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) 




=DGET(A4:E10,"Yield",A1:A3) 
Returns the #NUM! error value because more than one record meets the criteria. 




