DVAR is a database function in Google Sheets for calculating the variance (a measure of variability) of a population sample.
Different database functions are there in Google Sheets for calculating variance depending on the data we have.
The Variance of Whole Population (σ2) – Use DVARP or the statistical functions VARP/VAR.P.
Sample Variance (s2) – Use DVAR or the statistical functions VAR/VAR.S.
Why should one consider DVAR over the VAR/VAR.S for the variance calculation?
We can use criteria in the DVAR function much easier than the other two in Google Sheets.
If we want to use criteria in VAR/VAR.S, we may additionally require to use the FILTER function. It will complicate things.
Please note that the variance is needed to calculate the standard deviation.
In a data set use;
- Sample Variance (s2) to determine how far the values are dispersed/spread from the average/mean.
- Sample Standard Deviation (s) to estimate the amount of dispersion.
Syntax Explained
Syntax:
DVAR(database, field, criteria)Arguments:
database – The array/range of cells to use. It should be structured like a database table in which usually the first row contains the labels (field labels) for each column’s values.
My data is not structured. What should I do?
Don’t worry! We can use the DVAR database function in unstructured data with a simple trick. Also, you can consider the combination of VAR + FILTER.
field – The relative position of the column in the database or a text label corresponding to a column header. The function extracts values from this column and operates.
criteria – The conditions to filter the array/range before calculation.
There is no optional argument in this function.
Examples to DVAR Database Function in Google Sheets
How to use the DVAR database function in Google Sheets?
Please see the below examples.
In column A I have descriptions A, B, and C with multiple occurrences and their heights in column B.
In rea-life use, as an example, you can replace them with cat breeds “Persian,” “Maine Coon,” and “Bengal” and their corresponding heights in column B.
E.g.
Filter A and B and Calculate the Variance.
There are variance calculation formulas in cells D3 and D4.
In the D3 formula, I have used relative position 2 to apply the variance calculation on the “Height” column.
=dvar(A1:B10,2,C1:C3)You can replace it with the field label “Height” itself.
=dvar(A1:B10,"Height",C1:C3)The following VAR formula alternative in cell D4 uses the Filter to filter out unwanted rows.
=var(filter(B2:B10,(A2:A10="A")+(A2:A10="B")))Can We Use the DVAR Function without Criteria in Google Sheets?
Yep! I’ll explain how.
As you may know, we require to use all the arguments in the function. So you can’t skip using criteria.
Then what’s the solution?
DVAR function in Google Sheets without criteria means, use all the values in the specified field column in the variance estimation.
In that scenario, we can use the following formula where the criteria are one of the whole column range in the database.
=dvar(A1:B10,2,A1:A10)Unstructured Data and Virtual Field Labels
If you want to use criteria in the variance calculation, the better solution is using the DVAR function in Google Sheets.
But as you may know, it works if you have structured data.
A structured data should have proper field labels (a header row) on the top.
But when you don’t have field labels, that means you have a range to use, not a database. Then you can follow my below simple workaround.
In the below example, as you can see, there is no header row in the range to calculate the variance.
We can’t change the rule. So, what I have done here is virtually added field labels/row headers.
=dvar({"","";A1:B10},2,{"";C2:C3})Instead of specifying any random names as row headers, used blanks depending on the number of columns in the ‘database’ (read range).
Related:- How to Use Curly Brackets to Create Arrays in Google Sheets.
Conclusion
Do you know how the DVAR database function may treat blank cells and zeros in the evaluation column?
In the above example, if any of the cells are blank in column B, the formula won’t consider that row. But will consider zeros and may affect the result.
You may find the below two tutorials worth reading to improve your skill in using database functions in Sheets.
- Exact Match in Database Functions in Google Sheets – How-To.
- The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.
That’s all. Thanks for the stay. Enjoy!
 of a population sample.){kind=link}