DVAR

DVAR(database, field, criteria)

Returns the variance of the values in a column that satisfies multiple conditions based on a sample.

databaseThe range of cells that make up the table or list.
fieldThe column name or number indicating which column to use.
criteriaThe 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 only one value match the criteria, then #DIV/0! is returned. See Example 1.
* If no rows match the criteria, then #DIV/0! is returned. See Example 15.
* You can use the DVARP function to return the variance of the values that satisfies multiple conditions based on an entire population.
* You can use the COVARIANCE.S function to return the covariance based on a sample.
* You can use the VAR.S function (which replaces the VAR function) to return the variance based on a sample.
* You can use the VARA function to return the variance based on a sample (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

 ABCD
1=DVAR(B1:D10, "Qty", B12:B13) = #DIV/0!DateQtyCost
2=DVAR(B1:D10, "QTY", B12:B13) = #DIV/0!Jan1918,000
3=DVAR(B1:D10, 2, B12:B13) = #DIV/0!Mar2024,000
4=DVAR(B1:D10, "Cost", B12:B14) = 2,000,000Feb1716,500
5=DVAR(C1:C10, "Qty", C12:C13) = 3Jun2220,000
6=DVAR(B1:D10, "Qty", C12:C14) = 6 1819,500
7=DVAR(B1:D10, "Cost", C12:D13) = 0Nov2124,000
8=DVAR(B1:D10, "Qty", D12:D13) = 4Maytext26,500
9=DVAR(B1:D10, "Cost", B12:D14) = #DIV/0!Apr1718,500
10=DVAR(B1:D10, "Cost", D16:D17) = 18,194,444Mar2429,000
11=DVAR(B1:D10, "Cost", C16:D17) = 18,000,000   
12=DVAR(B1:D10, "Cost", B19:B20) = 2,000,000DateQtyCost
13=DVAR(B1:D10, "Date", C12:C13) = #DIV/0!Jan<22>20000
14=DVAR(B1:D10, "Qty", B12:D13) = #DIV/0!Jun>18 
15=DVAR(B1:D10, "text", B12:B13) = #VALUE!   
16=DVAR(B1:D10, "Qty", C12) = #VALUE!DateQtyCost
17=DVAR(B1:D10, "Cost", C19) = #VALUE!Jun>18 
18=DVAR(B1:D10, "Qty", ) = #VALUE!   
19=DVAR(B2:D10, "Qty", B12:B13) = #VALUE!Date  
20=DVAR(B1:D1, "Qty", B12:B14) = #VALUE!J*  

1 - What is the variance of the "Qty" when Date="Jan", using a field label. (one value)
2 - What is the variance of the "QTY" when Date="Jan", with the field label in uppercase. (one value)
3 - What is the variance of the "Qty" when Date="Jan", using a field number. (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. (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.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top