The DVAR function in Excel is a powerful statistical tool used to determine the variance of a sample taken from a specified set of database entries. It is particularly useful when analyzing data sets to understand the variability within a sample, making it invaluable for data analysis and reporting tasks.

Syntax

DVAR(database, field, criteria)

• database: The range of cells that contains the data you want to evaluate.
• field: Indicates which column in the database to be used for the calculation. This can be specified as a column label or index number.
• criteria: Defines the conditions that must be met for a record to be included in the variance calculation, specified as a cell range.

Example #1

DVAR(A1:D100, "Sales", F1:F2)

This function calculates the variance of the ‘Sales’ column from the database range A1:D100, based on the criteria specified in F1:F2. Example Result: 150.25

Example #2

DVAR(B1:E50, 2, G1:G3)

Here, the function computes the variance for the second column within the range B1:E50, applying the conditions defined in G1:G3. Example Result: 75.00

Example #3

DVAR(A2:C30, "Profit", E1:E2)

This example determines the variance of the ‘Profit’ column in the range A2:C30, subject to criteria in E1:E2. Example Result: 95.50

Error handling

• N/A: This error occurs if the field specified does not exist in the database.
• DIV/0!: This error indicates that there are no valid entries that match the criteria for the calculation.
• VALUE!: This error is displayed if non-numeric values are entered in the field you’re analyzing.

Conclusion

In summary, the DVAR function serves as an essential tool for calculating variance within a subset of data in Excel. By enabling analysts to draw insights about data variability based on specific criteria, DVAR enriches data analysis capabilities, helping users make informed decisions based on quantitative evidence.