DVARP

DVARP(database, field, criteria)

Returns the variance of the values in a column that satisfies multiple conditions based on an entire population.

database	The range of cells that make up the table or list.
field	The column name or number indicating which column to use.
criteria	The range of cells that contain the conditions.

REMARKS

* For an illustrated example refer to the Specifying Database Criteria page.
* The variance is a measure of how Dispersed the values are from the mean.
* Any cells containing logical values, text and errors are not included. See Example 7.
* This function supports wildcards (? and *).
* The "database" range of cells can be a single column. See Example 5.
* The "database" range of cells must contain column headings in the first row.
* If the "database" range refers to a cell inside a pivot table, this will refer to the data currently displayed in the pivot table.
* The "field" can be a column label, a column number (left to right) or a cell reference.
* The "field" label is not case sensitive. See Example 2.
* If the "field" is left blank, then #VALUE! is returned.
* If the "field" column contains some text entries, then these are ignored.
* The "criteria" must include at least one column heading and one cell below the column heading.
* The "criteria" must be in a contiguous block of adjacent cells.
* The "criteria" can be anywhere on the active sheet, although it is recommended not to put it below the data, in case more data is added later on.
* If the "criteria" is left blank, then #VALUE! is returned. See Example 18.
* Make sure the "criteria" and "database" ranges do not overlap and that they are always separated by at least one blank row or column.
* To perform an operation on an entire column in a database, enter a blank cell below the column heading in the "criteria" range. See Example 10.
* If no rows match the criteria, then #DIV/0! is returned. See Example 15.
* You can use the DVAR function to return the variance of the values that satisfies multiple conditions based on a sample.
* You can use the COVARIANCE.P function to return the covariance based on an entire population.
* You can use the VAR.P function to return the variance based on an entire population.
* You can use the VARPA function to return the variance based on an entire population (including logical values and text).
* This is similar to DAVERAGE, DCOUNT, DGET, DMAX, DMIN, DPRODUCT, DSTDEV and DSUM functions.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=DVARP(B1:D10, "Qty", B12:B13) = 0	Date	Qty	Cost
2	=DVARP(B1:D10, "QTY", B12:B13) = 0	Jan	19	18,000
3	=DVARP(B1:D10, 2, B12:B13) = 0	Mar	20	24,000
4	=DVARP(B1:D10, "Cost", B12:B14) = 1,000,000	Feb	17	16,500
5	=DVARP(C1:C10, "Qty", C12:C13) = 2	Jun	22	20,000
6	=DVARP(B1:D10, "Qty", C12:C14) = 5		18	19,500
7	=DVARP(B1:D10, "Cost", C12:D13) = 0	Nov	21	24,000
8	=DVARP(B1:D10, "Qty", D12:D13) = 3	May	text	26,500
9	=DVARP(B1:D10, "Cost", B12:D14) = 0	Apr	17	18,500
10	=DVARP(B1:D10, "Cost", D16:D17) = 16,172,840	Mar	24	29,000
11	=DVARP(B1:D10, "Cost", C16:D17) = 14,400,000
12	=DVARP(B1:D10, "Cost", B19:B20) = 1,000,000	Date	Qty	Cost
13	=DVARP(B1:D10, "Date", C12:C13) = #DIV/0!	Jan	<22	>20000
14	=DVARP(B1:D10, "Qty", B12:D13) = #DIV/0!	Jun	>18
15	=DVARP(B1:D10, "text", B12:B13) = #VALUE!
16	=DVARP(B1:D10, "Qty", C12) = #VALUE!	Date	Qty	Cost
17	=DVARP(B1:D10, "Cost", C19) = #VALUE!	Jun	>18
18	=DVARP(B1:D10, "Qty", ) = #VALUE!
19	=DVARP(B2:D10, "Qty", B12:B13) = #VALUE!	Date
20	=DVARP(B1:D1, "Qty", B12:B14) = #VALUE!	J*

1 - What is the variance of the "Qty" when Date="Jan", using a field label. (19) (one value)
2 - What is the variance of the "QTY" when Date="Jan", with the field label in uppercase. (19) (one value)
3 - What is the variance of the "Qty" when Date="Jan", using a field number. (19) (one value)
4 - What is the variance of the "Cost" when Date="Jan" or "Jun". (18000, 20000)
5 - What is the variance of the "Qty" when Qty<22. This uses a single column. (19, 20,17,18,21,17)
6 - What is the variance of the "Qty" when Qty<22 OR Qty>18. (all the numbers)
7 - What is the variance of the "Cost" when Qty<22 AND Cost>20,000. The Cost for May is not included. (24000, 24000) (same value)
8 - What is the variance of the "Qty" when Cost>20,000. (20, 21, 24)
9 - What is the variance of the "Cost" when Date="Jan", Qty<22 and Cost>20,000 OR when Date="Jun" and Qty>18. (20000) (one value)
10 - What is the variance of the "Cost" for all the items. Putting a blank cell below the column heading will refer to the entire column. (all the numbers)
11 - What is the variance of the "Cost" when Qty>18 and Cost has no criteria. (18000, 24000, 20000, 24000, 29000)
12 - What is the variance of the "Cost" for all the dates that start with the letter "J". (18000, 20000)
13 - If the "field" column does not contain any numbers, then #DIV/0! is returned.
14 - If the "criteria" you specify does not return any rows, then #DIV/0! is returned. The Cost for January was only 18,000.
15 - If the "field" does not match one of the column headings, then #VALUE! is returned.
16 - If the "criteria" only includes a column heading, then #VALUE! is returned.
17 - If the "criteria" refers to a single empty cell, then #VALUE! is returned. C19 is empty.
18 - If the "criteria" is left blank, then #VALUE! is returned.
19 - If the "database" range does not include column headings, then #VALUE! is returned.
20 - If the "database" range only includes column headings, then #VALUE! is returned.