The variance derived from the total population can be extracted from a given data range and done by a very useful function of excel, DVARP. If the data does not represent a population but represents a sample, here DVAR function can be used.
Function
Firstly, the core function of this formula is to derive the variance from the population of the selected data range of the same record.
The value obtained by this formula is calculated variance.
Formula
The formula:
=DVARP(database,field,criteria)
Explanation of Formula
- Database: it includes the title
- Field: For counting, use the field name.
- Standard: The criteria used in this formula include the title.
Formula Usage
Secondly, the parameters involved in the formulas discussed in this article define a series having cells with various field titles. Using this example, you can consider the height of the group to get the variance.
=DVARP(B7:C13,"Height",B4:C5) // field by name
=DVARP(B7:C13,2,B4:C5) // field by index
=VAR.P(C8:C13)
It should be noted here that various expressions can be included as standards, which also involve wildcards. However, other functions such as MATCH, SUMIFS, COUNTIFS etc. are different from the wildcards that can be used in the formula in question. However, if you want to use wildcards, you must test them very carefully.
Figure1. Example of Excel DVARP Function
Leave a Comment