How to Use Excel's DVARP Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's DVARP function is short for "database variance population." It calculates a population's variance based on the entire population using values specified by a database, field and criteria rather than taking values directly from the arguments.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of DVARP. It is DVARP (database, field, criteria). Database is a range of cells that comprises the database; field is a column in the database from which the values will be taken; and criteria is a range of cells that contains the criteria that will be used to select the values.

Step2

Enter the database. The first cell in the range establishes the upper left corner of the database array and the second cell establishes the lower right corner of the array. The first row of the array will contain the column names.

Step3

Specify the field. It may be the column name enclosed in double quotation marks or it may be a number that represents the column's position in the database where 1 is the first column, 2 is the second column and so on.

Step4

Supply the criteria. This cell range is a list of criteria that a row must meet before its value in the specified field may be considered. The criteria must contain at least one column label and one condition for that column. The format for the criteria values is quite extensive but will generally be of the form ="condition." Thus, a value of ="=Apple" would select rows in which the value in the specified column is equal to "Apple."

Step5

Enter the following in the first four columns of an Excel spreadsheet:
Row 1: Tree, Height, Age and Yield;
Row 2: ="=Apple", (nothing in columns 2, 3 and 4);
Row 3: ="=Pear", (nothing in columns 2, 3 and 4);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18, 20 and 14;
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13, 14 and 9;
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8;
Row 10: Apple, 8, 9 and 6.

Step6

Interpret the formula = DVARP(A4:D10,"Yield",A1:A3) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. "Yield" is the column name from which the values will be taken for all rows where the column named Tree contains "Apple" or "Pear." = DVARP(A4:D10,"Yield",A1:A3) will therefore be evaluated as VARP(14,10,10,8,6)=7.04.