DVARP Function in Microsoft Excel

Part 1: Introduction

Definition

The DVARP function in Microsoft Excel is a database function that calculates the variance of a selected database or array of numbers. It’s a statistical function that helps you understand the variability of your data.

Purpose

The DVARP function is used when you want to find the variance based on the entire population. It’s helpful in business scenarios where you must analyze a complete dataset, such as sales, customers, or inventory.

Syntax & Arguments

The syntax for the DVARP function is as follows:

Arguments

1. Database: This is the range of cells that make up the database. It includes column headers.
2. Field: This indicates the column in the database that the function will be used on. It can be the column name (in quotes) or the column number.
3. Criteria: This is the cell range containing the conditions you specify. It includes at least one column header and at least one cell below the header specifying a column condition.

Return Value

The DVARP function returns the variance of a population based on selected database entries.

Remarks

The DVARP function considers cells with text as 0s, empty cells are ignored, and cells with logical values TRUE and FALSE are regarded as 1 and 0, respectively.

Part 2: Examples

Example 1

Purpose

To calculate the variance in sales data for a specific product category.

Data Tables and Formulas

Explanation

In this example, we want to calculate the sales variance for the ‘Electronics’ category. The formula DVARP(A1:C4, "Sales", A5:A6) will calculate the sales variance for all products in the ‘Electronics’ category. The result will give us an understanding of the sales variability within this category.

Example 2

Purpose

Calculate the variance in the number of units sold for a specific region.

Data Tables and Formulas

Explanation

In this example, we want to calculate the variance of units sold in the ‘West’ region. The formula DVARP(A1:C4, "Units", A5:A6) will calculate the variance of units sold for all sales in the ‘West’ region. The result will explain the variability of units sold within this region.

Example 3

Purpose

Calculate the variance in the cost of goods sold for a specific supplier.

Data Tables and Formulas

Explanation

In this example, we want to calculate the cost variance for goods from the ABC Ltd. Supplier. The formula DVARP(A1:C4, "Cost", A5:A6) will calculate the cost variance for all goods from the ABC Ltd. supplier. The result will provide an understanding of the variability of cost for goods from this supplier.

Part 3: Tips and Tricks

1. Ensure Correct Data Type: Ensure the field argument corresponds to the correct data type. The sales column should contain numerical values if you’re calculating variance for sales data.
2. Use Column Headers: Always include the column headers in the database and criteria range. This helps Excel understand which column to apply the function to.
3. Avoid Text in Numerical Fields: As DVARP treats text as zeros, avoid having text in numerical fields to get accurate results.
4. Use Absolute Cell References: If you plan to drag your formula down to apply it to a large dataset, use absolute cell references to keep your criteria range constant.
5. Check for Errors: If you get an error, check your database and criteria range for inconsistencies. Also, ensure that the field argument correctly corresponds to a column in the database.