This tutorial describes how to use the DVARP function for calculating the conditional variance of a whole population in Google Sheets.

For the calculation, we can use the numbers in a field of multi-row records.

Usually, you may arrange your data in a database table-like range in Spreadsheets to use the DVARP function.

But that is not necessary in Google Sheets as we can virtually fulfill that data formatting requirement.

Thanks to the flexibility we get in Google Sheets in creating virtual arrays.

There are two Database functions in Google Sheets for Variance calculation – DVAR and DVARP.

I have already detailed the usage of the former one. It’s for use in a population sample, not in the whole population.

DVARP Function Syntax and Arguments Explained

Syntax: DVARP(database, field, criteria)

As per the above syntax, there are three arguments. We can learn them in detail below.

1. DATABASE

It’s the data to consider.

The first row of the data must contain the labels for each column’s values.

In the below table, the field labels are “Dog Breed,” “Height (mm),” and “Weight (lbs).”

If there are no field labels in your data as above, you should specify them virtually within the formula.

Note:- Later, we will use the above table to explain how to use the DVARP function in Google Sheets.

2. FIELD

Indicates which column in the database has to be used to return the variance. As per the above database, you can use columns 2 or 3.

You can specify it in two different ways as below (We are considering the second column for variance calculation).

Option 1 – Text Label Method.

=dvarp(database,"Height (mm)",criteria)

Option 2 – Numeric Index Method.

=dvarp(database,2,criteria)

3. CRITERIA

It is to specify the conditions to filter the database before operating.

You must use/specify at least one field label and at least one cell below the field label as the criteria range.

If your data is not structured and doesn’t contain the header row, you may not be able to specify the field labels.

I’ll explain what to do in that case in the example section below.

Formula Examples

Please find below examples of the use of the DVARP database function in Google Sheets.

Let’s understand the records in the database first.

The database (please see table#1 above) contains a list of 7 dog breeds in column A, and they are “Pug,” “Yorkshire Terrier,” “Papillon,” “French Bulldog,” “Labrador Retriever,” “Boxer,” and “Pomeranian.”

A few of them are in multiple rows. So the total number of rows in the database is 12, including the header row.

The next two columns contain their heights and weights.

Using DVARP Function in a Database Like Table in Google Sheets

The following formula extracts the records containing “Pomeranian” and calculates the variance of “Height in (mm)” as per the “Text Label Method” mentioned above.

=dvarp(A1:C12,"Height (mm)",E1:E2)

The same formula as per “Numeric Index Method.”

=dvarp(A1:C12,2,E1:E2)

What about the dog breeds “Pomeranian” and “Yorkshire Terrier in the criteria part?”

In cell E3, insert the text “Yorkshire Terrier” and modify the criteria range from E1:E2 to E1:E3.

=dvarp(A1:C12,2,E1:E3)

The above is the usual way of using the DAVARP function in Google Sheets.

Can I specify the criteria within the DVARP formula?

Yeap! We can replace the above DVARP formula as below.

=dvarp(A1:C12,2,{"Dog Breed";"Pomeranian";"Yorkshire Terrier"})

For additional information on this criteria part, please check my related tutorial here – The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.

Using DVARP Function in Google Sheets When Field Labels Are Missing

Assume you have the above table but without the filed labels in the first row.

The above formulas may return the error #VALUE!.

Here is the solution.

=dvarp({" "," "," ";A2:C12},2,{" ";E2:E3})

Criteria within the DVARP formula.

=dvarp({" "," "," ";A2:C12},2,{" ";"Pomeranian";"Yorkshire Terrier"})

We have virtually added space characters as the field labels.

Additional Notes

You may find the below points worthy while using the DAVARP function in Google Sheets.

1. You don’t want to specify criteria. In that case, use one of the column references from the database itself.

I mean, to include all the dog breeds, i.e., the total 12 records, specify criteria as A1:A12.

2. The DVARP function in Google Sheets will ignore the following records in the table that contain blank cells, text strings, TRUE, and FALSE in the evaluation (numeric) column.

But it will do include zero values.

You May Like: Exact Match in Database Functions in Google Sheets – How-To.

That’s all. Thanks for the stay. Enjoy!