The DVARP function in Excel is used to calculate the population variance of a field (column) in a database that matches specified conditions. Here’s how to use the DVARP function:
Syntax
DVARP(database, field, criteria)
- database: The range of cells that makes up the database. The first row of the database should contain the column headers.
- field: Indicates which column to calculate the population variance. You can use the column header name in quotes (e.g., “Age”) or the index number (e.g., 2 for the second column).
- criteria: The range of cells that contains the conditions you want to apply. This range must include at least one column header and one cell below the header for the condition.
Example
Suppose you have a database in Excel with the sales data like this:
| Name | Region | Sales |
|——–|———|——-|
| John | North | 5000 |
| Mike | South | 7000 |
| Sara | East | 8000 |
| Bob | North | 4500 |
| Alice | East | 9100 |
You want to find the population variance of sales for the “North” region.
- Database Range: A1:C6
- Field: Use the column “Sales” by name.
- Criteria Range: F1:F2
- In F1, enter the column header “Region” (make sure it matches exactly the column header in the database).
- In F2, enter the value “North”.
DVARP Function Implementation
In cell G1, type the DVARP formula:
=DVARP(A1:C6, "Sales", F1:F2)
This formula will calculate the population variance of the sales figures for entries in the “North” region.
Important Considerations
- Field Argument: You can specify the field either by using the header name in quotes or by using the column number. Using the header name ensures that the formula remains understandable and less error-prone if the column structure changes.
- Criteria Range: Make sure there are column headers in your criteria range that match one of the columns in your database range. You can have more complex criteria by including more columns in your criteria range.
- Headers Matching: Ensure that the criteria headers exactly match the database headers, including any spacing or capitalization.
Using DVARP can be very powerful for statistical analysis on a subset of your data filtered by specific criteria.
Unlock Your Potential
Excel
Access
Power BI
