The DVAR function in Google Sheets is a powerful tool that allows users to compute the variance of a population based on a specified query from a database table-like array. This function is particularly useful for analyzing datasets where you need to understand the distribution of values, thereby aiding in statistical analysis and decision-making.

Syntax

DVAR(database, field, criteria)

• database: The range of cells that makes up the database. This must include the headers for identification.
• field: This specifies which column to evaluate, either by index number or column name.
• criteria: The range of cells that contains conditions to filter the database records.

Example #1

=DVAR(A1:C100, "Sales", E1:F2)

This function calculates the variance of the “Sales” column for entries that meet the conditions specified in the range E1:F2. For example, if there are five entries satisfying the conditions, and their values are 10, 15, 20, 25, and 30, the result would be 62.5.

Example #2

=DVAR(A1:B10, 2, C1:C3)

Here, the function determines the variance of the second column (field 2) of the database from A1 to B10, given filtering conditions from C1 to C3. If the relevant values are 8 and 12, the variance would yield 4.

Example #3

=DVAR(D1:D200, "Profit", E1:E5)

This function assesses the variance of the “Profit” column in the D1:D200 range, applying the criteria outlined in E1:E5. If the extracted values are 100, 150, and 200, the result might be 1666.67.

Error handling

• DIV/0!: This error indicates that there are no records meeting the criteria specified.
• VALUE!: This happens when the parameters provided are of the wrong type, such as trying to use a text string when a numeric value is expected.
• REF!: This error suggests that the specified range does not exist or is incorrectly referenced.

Conclusion

In summary, the DVAR function in Google Sheets is an essential analytical tool for quantifying the variance in your datasets based on specific criteria. By leveraging this function, users can gain deeper insights into data distributions, which are invaluable for statistical analysis and informed decision-making.