How to Use Excel's DVAR Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's DVAR function is short for "database variance." It estimates the variance of a population based on values determined by a specified database, field and criteria rather than values taken directly from the arguments. The following steps will show how to use Excel's DVAR function.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of DVAR (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

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

Step3

Specify the field. It must be the column name enclosed in double quotation marks like "Tree" or it can be a number representing 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 the row must meet before the value in the specified field will be used. It 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 where 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 two, three and four);
Row 3: ="=Pear" (nothing in columns two, three and four);
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 =DVAR(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."

=DVAR(A4:D10,"Yield",A1:A3) will therefore be evaluated as VAR(14,10,10,8,6)=8.8.