The DVARP function in Excel calculates variance based on an entire population by using values in a column or field of a database with a user-specified condition, to better understand the DVARP function in Excel, please read the following article of Taimienphi.vn.
DVARP function is also one of the basic functions in Excel, the following article Tamienphi.vn will introduce you in detail about DVARP function in Excel.
Overview of the DVARP function in Excel
The DVARP function in Excel returns the calculation of variance based on an entire population by using values in a column or field of a database with a given condition.If the data represents a sample, use Use the DVARP function in Excel.
Purpose of using DVARP function in Excel
Calculates variance based on the entire set of records.
The DVARP function in Excel returns the value of a variance calculation.
Syntax function DVARP in Excel
The syntax of the DVARP function in Excel looks like this:
DVARP (database, field, criteria)
Arguments in the DVARP function syntax in Excel
In the DVARP function syntax in Excel above, the arguments make sense:
– Database: The range of cells that contains the database, with rows being records information, data columns being fields and the first row of the list must contain column headings.
– Field: The field (column) in the database allows the calculation of variance.
It may be the field number or it may be the field name (i.e. the title in the first row of the list containing the column’s title) enclosed in quotation marks, for example “Name”, etc.
– Criteria: Range of cells that contain criteria to specify conditions for calculating variances.
This range may include one or more criteria, represented as the name of a field in a cell and conditions for that field in the box below.
The criteria provided below each school may be:
– Numeric values (including integers, decimals, dates, times, or logical values). For example, January 10, 2017, FALSE.
– Maybe a text string. For example “Name”, “Monday”.
– Expression. For example “> 8”, “0”.
Note that Database in Excel is not case sensitive. So if the criteria is = “Name”, it will satisfy the cells containing the text “Name” or “name”.
Wildcard in DVARP function in Excel
Database functions in Excel allow the following wildcards to be used in text-related criteria:
?: Matches single characters.
*: Matches the character sequence.
If you want to search for characters? or *, you enter the – symbol before the characters in the search box.
Conditional example “a * e“will match all cells that contain the text string beginning with” a “and ending with” e “.
Some examples of DVARP function in Excel
The examples below are based on the database in the 3 subjects subject transcript of the following 4 students:
In this example, the DVARP function is used to find the variance of math scores for boys. The criteria provided for cells G1 – H2 and the DVARP formula are displayed in cell G3.
The DVARP function calculates the variance of values in cells E5, E8 and E11, and returns the value of 2.6%.
In this example, the DVARP function is used to find the variance for Science grades for students over 8 years old.
DVARP will calculate the variance of values in cells E4, E10 and E13 and return the value 1.2%.
Note that in both examples above, instead of entering Score for the field argument, you can use the number 5 (to denote the fifth column of the database).
Some notes with DVARP function in Excel
The DVARP function in Excel calculates the variance of data representing an entire population, extracted from records that match certain criteria, in which values are obtained from a given field.
The database argument is a series of cells that contain header fields, fields that are the field’s name or subject to get the maximum value, and criteria as a series of header cells that match the criteria in the database.
Refer to the example below to get the height variance of Fox group by 1 of the 2 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 by the function VAR.P:
= VAR.P (C8: C13)
Criteria in DVARP function in Excel
The criteria in the DVARP function in Excel can include a range of expressions, including a number of wildcards. The following table lists some expressions in criteria in the DVARP function in Excel:
The DVARP function in Excel supports wildcard unlike other Excel functions like COUNTIFS, SUMIFS, MATCH, etc. For example characters ??? will match the 3-character string exactly in more modern functions, but not in database functions. If using wildcards you need to check carefully.
Criteria for multiple rows in DVARP function in Excel
The criteria range in the DVARP function in Excel may include more than one row below the header. If the criteria consists of more than one row, each row is joined by OR logic, and the expressions in the given criteria row are joined by AND logic.
Some important notes:
– DVARP function in Excel calculates the variance of data representing the entire set. Use the DVAR function for some examples.
– DVARP supports a number of wildcards in criteria.
– Criteria may include more than one row.
– The field argument may be provided either as a name in double quotes (“”) or as a number representing the field index.
– Database and criteria ranges must include appropriate titles.
So the article on Taimienphi.vn has just introduced you to DVARP function in Excel and some examples of functions. Also readers can refer to the general article Excel function is only available in Office 2016, CONCAT, TEXTJOIN, IFS to learn the CONCAT, TEXTJOIN functions in Excel.