How to Use DVAR and DVARP Functions in Excel

By Stephen L. Nelson, E. C. Nelson

In Excel, the DVAR and DVARP functions calculate a variance, which is another measure of dispersion — and actually, the square of the standard deviation. DVAR calculates the variance for a sample. DVARP calculates the variance for a population. As with other database statistical functions, using DVAR and DVARP enable you to specify that you want only those list records that meet selection criteria included in your calculations.

If you want to calculate variances without first applying selection criteria, use one of the Excel non-database statistical functions such as VAR, VARA, VARP, or VARPA.

The DVAR and DVARP functions use the same syntax:

=DVAR(database,field,criteria)
=DVARP(database,field,criteria)

where database is a range reference to the Excel table that holds the values for which you want to calculate a variance, field tells Excel which column in the database to use in the calculations, and criteria is a range reference that identifies the fields and values used to define your selection criteria.

The field argument can be a cell reference holding the field name, the field name enclosed in quotation marks, or a number that identifies the column (1 for the first column, 2 for the second column, and so on).

As an example of how the DVAR function works, suppose you’ve constructed this worksheet. The worksheet range holds a small list with row 1 storing field names and rows 2–11 storing individual records.

Rows 14–17 store the criteria, which stipulate that you want to include golfing buddies in the variance calculation if their favorite courses are Snohomish, Snoqualmie, or Carnation. The first row, row 14, duplicates the row of field names. The other rows provide the labels or values or Boolean logic expressions — in this case, just labels — that the DVAR and DVARP functions use to select records from the list.

image0.jpg

The DVAR function, which appears in cell F3, is

=DVAR(A1:C11,"Golf Score",A14:C17)

and it returns the sample variance of the golf scores shown in the database list for golfers who golf at Snohomish, Snoqualmie, or Carnation. The actual function result is 161.26786.

The DVARP function, which appears in cell F5, is

=DVARP(A1:C11,"Golf Score",A14:C17)

and it returns the population variance of the golf scores shown in the database list for golfers who golf at Snohomish, Snoqualmie, and Carnation. The actual function result is 141.10938.

As when making standard deviation calculations, you don’t simply pick one of the two database variances based on a whim, the weather outside, or how you’re feeling. If you’re calculating a variance using a sample, or subset of items, from the entire data set, or population, you use the DVAR function. To calculate a variance using all the items in the population, you use the DVARP function.