Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Functions > Database > DVARP
 

DVARP(database, field, criteria)

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

 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 an entire population.
     
  		• 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=DVARP(B1:C10,"Age",B12:B14) = 1NameAgeSalary
    2=DVARP(B1:C10,2,B12:B13) = 0John1918,000
    3=DVARP(B1:D10,"Salary",B12:C14) = 2,250,000Mark2024,500
    4=DVARP(B1:D3,"Salary",D12:D13) = 10,562,500David1716,500
    5=DVARP(B1:D3,2,D12:D14) = 0James2220,000
    6=DVARP(B1:D5,"Age",C12:C14) = 4John1819,500
    7=DVARP(B1:D6,"Age",D12:D13) = 3Nick2124,000
    8=DVARP(B1:D10,3,B12:C14) = 2,250,000Matthewtext26,500
    9=DVARP(B1:D10,"Age",C12:C14) = 6Jamie1718,500
    10=DVARP(B1:D4,"Age",D12:D14) = 2Mark2429,000
    11=DVARP(B1:D10,"Age",D12:D14) = 5   
    12=DVARP(B1:D10,2,D12:D13) = 3NameSalarySalary
    13=DVARP(B1:D1,"Name",B12:B14) = #VALUE!John>18000<28000
    14=DVARP(B1:D10,"Age",D14) = #VALUE!David<18000>24000
    15=DVARP(B2:D10,"Age",B12:B13) = #VALUE!   
    		 
     Functions - D | Index - D | Office Online 

     Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.Top