Bespoke Microsoft Office Development 
 Consultancy|

Excel

|Word|PowerPoint|Outlook|VBA|Tools|Newsletter 
 Excel > Functions > Database > DVAR

 

DVAR(database, field, criteria)

 
 Returns the variance of all the values in a database column satisfying certain conditions.

 databaseThe range of cells that make up the database or list.
 fieldThe column name or number indicating which column to use.
 criteriaThe range of cells that contain the conditions.

 REMARKS
 
  • This population is based on a sample.
  •  
  • The "database" can be a cell reference or a named range.
  •  
  • The "database" range of cells must contain column headings in the first row. See Example ??
  •  
  • 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 ??
  •  
  • If "field" is left blank, then #VALUE! is returned. See Example ??
  •  
  • If "field" refers to a column containing text and not numerical values, then 0 is returned.
  •  
  • The "criteria" must include at least one column label and at least one cell below the column label for specifying the condition.
  •  
  • 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.
  •  
  • The "criteria" must be in adjacent cells.
  •  
  • If "criteria" is left blank, then #VALUE! is returned. See Example ??
  •  
  • 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 line below the column labels in the "criteria" range.
  •  
  • If more than one row meets the criteria, then #NUM! is returned.
  •  
  • If no rows meet the criteria, then #VALUE! is returned. See Example ??
  •  
  • Any cells containing text, logical values or errors are not included.
  •  
  • Notice the difference between the two results obtained from rows 8 and 9.
  •  
  • Example 1 -
  •  
  • Example 2 -
  •  
  • Example 3 -
  •  
  • Example 4 -
  •  
  • Example 5 -
  •  
  • Example 6 -
  •  
  • Example 7 -
  •  
  • Example 8 -
  •  
  • Example 9 -
  •  
  • Example 10 -
  •  
  • Example 11 -
  •  
  • Example 12 -
  •  
  • Example 13 -
  •  
  • Example 14 -
  •  
  • For more examples of how to specify your database criteria, please refer to the Specifying Database Criteria page.
  •  
  • It is possible to use the data from a pivot table. When the "database" range refers to a cell inside a pivot table, the calculation is only performed on the data currently displayed in the pivot table.
  •  
  • The accuracy of this function was improved in Excel 2003. For more information please refer to this Knowledge Base Article (828125).

  •  EXAMPLES
     
     ABCD
    1=DVAR(B1:C10,"Age",B12:B14) = 1NameAgeSalary
    2=DVAR(B1:C10,2,B12:B13) = 1John1918,000
    3=DVAR(B1:D10,"Salary",B12:C14) = 4,500,000Mark2024,500
    4=DVAR(B1:D3,"Salary",D12:D13) = 21,125,000David1716,500
    5=DVAR(B1:D3,2,D12:D14) = 1James2220,000
    6=DVAR(B1:D5,"Age",C12:C14) = 6John1819,500
    7=DVAR(B1:D6,"Age",D12:D13) = 4Nick2124,000
    8=DVAR(B1:D10,3,B12:C14) = 4,500,000Matthewtext26,500
    9=DVAR(B1:D10,"Age",C12:C14) = 7Jamie1718,500
    10=DVAR(B1:D4,"Age",D12:D14) = 2Mark2429,000
    11=DVAR(B1:D10,"Age",D12:D14) = 6   
    12=DVAR(B1:D10,2,D12:D13) = 4NameSalarySalary
    13=DVAR(B1:D1,"Name",B12:B14) = #VALUE!John>18000<28000
    14=DVAR(B1:D10,"Age",D14) = #VALUE!David<18000>24000
    15=DVAR(B2:D10,"Age",B12:B13) = #VALUE!   
     

     Functions - D | Index - D | Office Online 2013 | 2010 | 2007 | 2003 

     © Better Solutions Limited 17-Jul-2014Top