Buzz
The DVARP function is also one of the basic functions in Excel. In this article, Tamienphi.vn will provide you with detailed information about the DVARP function in Excel.
DVARP Function and Examples
Overview of DVARP Function in Excel
The DVARP function in Excel returns the variance based on the entire dataset by using values in a column or field of a database with specified conditions. Use the DVARP function in Excel if the data represents a sample.
1. Purpose of using DVARP function in Excel
Calculate the variance based on the entire record set.
2. Returned Value
The DVARP function in Excel returns the value of the variance.
3. Syntax of DVARP Function in Excel
The syntax of the DVARP function in Excel is as follows:
DVARP(database, field, criteria)
4. Parameters in the Syntax of DVARP Function in Excel
In the syntax of the DVARP function in Excel above, the parameters have the following meanings:
- Database: Range of cells containing the database, with rows representing record information and columns representing fields, and the first row of the list must contain column headers.
- Field: The field (column) in the database for which to calculate the variance.
This scope may encompass one or multiple criteria, presented as the name of a field in a box and the condition for that field in the box below.
The criteria provided below each field can be:
- A numeric value (including integers, decimals, dates, times, or logical values). For example, 10, 01/01/2017, FALSE.
Or:
- A text string. For example, 'Name', 'Monday'.
Or:
- An expression. For example, '>8', '<>0'.
Note that Excel databases are case-insensitive. Therefore, if the criterion is = 'Name', it will satisfy cells containing text like 'Name' or 'name'.
5. Wildcards in the DVARP function in Excel
Database functions in Excel allow the following wildcards to be used in text-related criteria:
?: Matches a single character.
*: Matches a sequence of characters.
If you want to search for the characters ? or *, you enter the hyphen symbol - before them in the search box.
For example, the condition 'a*e' will match all cells containing a text string starting with 'a' and ending with 'e'.
6. Some examples of the DVARP function in Excel
The examples below are based on the database in the grade table of 4 students for 3 subjects:
Example 1
In this example, the DVARP function is used to find the variance of math scores for male students. Criteria are provided for cells G1 - H2, and the DVARP formula is displayed in cell G3.
The DVARP function calculates the variance of values in cells E5, E8, and E11, returning a value of 2.6%.
Example 2
In this example, the DVARP function is used to find the variance for the Science scores of students above 8 years old.
The DVARP function will calculate the variance of values in cells E4, E10, and E13, returning a value of 1.2%.
Note that in both examples, instead of entering Score for the field argument, you can use the number 5 (to represent the 5th column of the database).
7. Some notes on the DVARP function in Excel
The DVARP function in Excel calculates the variance of data representing the entire set, extracted from records that match specific criteria, where values are taken from a specific field.
With the database parameter as a range of cells containing header fields, field is the name or label of the field to retrieve values from, and criteria is a range of cells with headers matching the criteria in the database.
Refer to the example below to get the variance of the height for the Fox group using one of the two formulas below:
= DVARP(B7:C13,'Height',B4:C5): field by name.
= DVARP(B7:C13,2,B4:C5) : field by index.
The variance for the entire dataset displayed in column F5 is calculated using the VAR.P:
= VAR.P(C8:C13)
8. Criteria in the DVARP function in Excel
Criteria in the DVARP function in Excel can include a range of expressions, including wildcard characters. The table below lists some expressions in the criteria of the DVARP function in Excel:
Note:
The DVARP function in Excel supports wildcards, unlike other Excel functions such as COUNTIFS, SUMIFS, MATCH, etc. For example, the ??? character will match with an exact 3-character string in newer functions but not in database functions. If using wildcard characters, you need to check carefully.
9. Multiple Rows Criteria in the DVARP Function in Excel
The criteria range in the DVARP function in Excel can include more than one row beneath the header. If the criteria involve multiple rows, each row is linked by logic OR, and the expressions within the criteria rows are linked by logic AND.
Some important notes:
- The DVARP function in Excel calculates the variance of data representing the entire set. Use the DVAR function for some examples.
- DVARP supports some wildcard characters in criteria.
- Criteria can include more than one row.
- The field parameter can be provided as a name enclosed in double quotes ('') or as a number representing the field index.
- The database and criteria ranges must include the appropriate headers.
In this Mytour article, we've just introduced you to the DVARP function in Excel along with some examples. Additionally, readers can explore a comprehensive article on Excel functions exclusive to Office 2016, such as CONCAT, TEXTJOIN, IFS, to delve into CONCAT and TEXTJOIN functions in Excel.
