How to use DVAR function in Excel?

The `DVAR` function in Excel is used to calculate the variance of a population based on a specific set of criteria. This function is particularly useful when you have a database of information and you want to calculate the variance from a particular subset of that database.

Here’s how to use the `DVAR` function:

Syntax

DVAR(database, field, criteria)

database: The range of cells that contains the database. The first row of the range should contain the column headers.
field: This indicates which column in the database is used to calculate variance. It can either be a column number, or a header in quotation marks.
criteria: The range of cells that contains the criteria. This allows you to specify which records are included in the calculation.

Example

Suppose you have the following data in a range A1:D7:

| A | B | C | D |

|——–|———–|———|———|

| John | 25 | 5.8 | 160 |

| Jane | 30 | 5.5 | 140 |

| Dave | 22 | 5.9 | 155 |

| Mary | 32 | 5.4 | 130 |

| Anne | 26 | 5.7 | 150 |

| Mark | 28 | 5.6 | 145 |

Let’s say you want to calculate the variance of the `Weight` for people who are older than 25.

Define your criteria. For our example, let’s place the criteria in cells F1:F2:

| F |

|——–|

| Age |

| >25 |

=DVAR(A1:D7, "Weight", F1:F2)

Use the `DVAR` function. The formula would look like this:

Here’s what each part of the function refers to:

A1:D7: This is the database range.
“Weight”: This is the field for which you want to calculate the variance.
F1:F2: This is the criteria range that specifies that the `Age` should be greater than 25.

After entering the formula, Excel will calculate the variance of the weights for the people whose ages are greater than 25.

Notes

Make sure that the criteria range includes labels that match the column labels from your database.
The criteria can be more complex, allowing you to specify multiple conditions, by adding more columns.
Remember that `DVAR` assumes that the data you are evaluating represents an entire population, not a sample. If your data represents a sample, you might want to use `DVARP` for population variance.

facebooklinkedin