DVARP function

The DVARP function in Excel is used to calculate the variance of a set of values in a specific field (column) of a database that meet certain criteria. However, unlike the DVAR function, which calculates the sample variance, DVARP calculates the population variance.

Difference between DVAR and DVARP:

• DVAR computes the sample variance (using $n – 1$ in the denominator, where $n$ is the number of data points in the sample).
• DVARP computes the population variance (using $n$ in the denominator, where $n$ is the total number of data points in the population).

Syntax

DVARP(database, field, criteria)

Parameters

• database: The range of cells that contains the database, including column headers. The database should have column headers in the first row, and the data should be below those headers.
• field: The column in the database for which you want to calculate the variance. You can specify the field by:
  • Column name (in quotes), e.g., "Sales".
  • Column number, e.g., 3 for the third column in the database.
• criteria: The range of cells that contains the condition(s) to filter the database. The criteria range should include at least one column header and the condition(s) below it.

How It Works

• DVARP calculates the population variance for the values in the specified field (column) for the records that meet the conditions defined in the criteria.
• If no records meet the criteria, DVARP will return 0.
• If the field is specified by column name, the function will calculate the variance for that column. If the field is specified by column number, it will calculate the variance for that specific column.

Example of Usage

Let’s say you have a database of product sales:

You want to calculate the population variance of Sales for products in the “East” region.

1. Database range: A1:D6 (including headers).
2. Field: "Sales" (or 3 for the third column).
3. Criteria range: To filter by “East” in the “Region” column, set up the criteria range in cells F1:F2 like this:

4. Formula:

=DVARP(A1:D6, "Sales", F1:F2)

Or

=DVARP(A1:D6, 3, F1:F2)

Result: The formula will calculate the population variance of the Sales values for products in the “East” region. In this example, the sales values are 1000, 2000, and 1800. The population variance of these values will be calculated.

Key Points

• Population Variance: DVARP calculates the population variance, which assumes that the data represents the entire population (using $n$ in the denominator).
• Criteria: The criteria range defines the condition(s) for filtering the data, such as sales greater than a certain amount or sales from specific products or regions.
• Field Reference: The field can be specified by either the column name (in quotes) or the column number.
• Error Handling:
  • 0: If no records meet the criteria.
  • Blank Criteria: If the criteria range is blank, all records in the database are included for the variance calculation.

Common Use Cases

1. Variance for a Specific Group: DVARP is useful when you want to calculate the variance for a specific group, such as the variance of sales for products in a specific region, assuming the data represents the entire population.
2. Population Variance: When you’re working with data that represents the entire population (rather than a sample), DVARP is the correct function to use to calculate the variance.
3. Variance for Financial Data: You can use DVARP to calculate the variance of financial data, such as expenses or revenue, for a complete set of data over a specific time period or category.

Example with Multiple Criteria

Suppose you want to calculate the population variance of Sales for “Tablets” in the “East” region. You would set up the criteria range like this:

Then, use the formula:

=DVARP(A1:D6, "Sales", F1:F2)

This will calculate the variance of sales for “Tablets” in the “East” region, which would include the sales values 2000 and 1800.

Tips

• Criteria Range: The criteria range should include the column headers from the database, and the condition(s) should be below them.
• Multiple Criteria: You can define multiple criteria in the criteria range to filter the data further. For example, you could calculate the variance for sales of “Laptops” in the “East” region.
• Named Ranges: Consider using named ranges for your database and criteria to make the formulas easier to read and manage.
• Error Checking: If you’re getting a result of 0, check the criteria range to ensure that it is correctly defined and that there are matching records in the database.

Common Errors

• #NAME?: This error occurs if the field name is invalid or misspelled.
• #VALUE!: This error occurs if the criteria range is not correctly defined or if there is an issue with the data format.
• 0: This is not an error, but it occurs if no records meet the specified criteria.

Summary

The DVARP function is used to calculate the population variance of a set of data that meets specific criteria. It is useful when working with data that represents an entire population (as opposed to a sample). DVARP is part of Excel’s database functions and allows you to perform statistical analysis by applying criteria to filter the data before calculating the variance. It uses $n$ in the denominator, which is appropriate for calculating variance for the entire population.