You can employ the Excel database functions to help you when using an Excel dataset. Sometimes, you may need to find the variance due to some specific criteria. Then, we will need the DVAR function. This article will show you how to use the DVAR function in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
What Is Variance?
Variance is an assessment of a data set’s variability that shows how widely apart the different values are from one another. It is mathematically formulated as the average of the squared deviations from the average. Let’s have three numbers X1, X2, and X3, and the average of these three numbers is X̅. Therefore, the general formula of variance is given below.
Firstly, we will calculate the average of all the numbers by applying the following formula.
=AVERAGE($B$5:$B$10)
Therefore, we will find the differences between all the numbers from averages particularly by using this formula.
=B5-C5
After that, we will apply the variance formula to find the variance.
=((D5)^2+(D6)^2+(D7)^2+(D8)^2+(D9)^2+(D10)^2)/(6)
Finally, you will see the output.
Introduction to DVAR Function
Objective
The objective of the Excel DVAR function is to obtain sample variance for matching records.
Syntax
=DVAR(database,field,criteria)
Arguments Explanation
Argument
Required/Optional
Explanation
Database
Required
The range of cells that you want to apply the criteria against.
Field
Required
The column to be used in the calculation. You can either specify the numerical position of the column in the list or the column label in double quotation marks.
Criteria
Required
The range of cells that contains your criteria.
Version
The DVAR function is available from Microsoft Excel 2000.
Here, we will use Microsoft Excel 365.
2 Suitable Examples to Use DVAR Function in Excel
For only selected records, the Excel Dvar function determines the sample variance of a field in a database. Here, we will demonstrate two examples of the DVAR function using Named ranges and Index in Excel. So, let’s have a sample data set.
Example 1: Using DVAR Function for Named Ranges in Excel
Here, we will use the DVAR Function for named ranges (Weight) for the following data set with specified criteria. So, to know this, you can follow the below steps accordingly.
Steps:
Firstly, we have specified limits for age and height in columns G and C.
Secondly, choose cell I5.
Thirdly, write down the following formula with specific criteria.
=DVAR(B4:E10,”Weight”,G4:H5)
After that, press Enter.
Finally, you will get the following result of variance of weight.
Read More: How to Use DSTDEV Function in Excel (3 Practical Examples)
Example 2: Applying DVAR Function by Index in Excel
Here, we will apply the DVAR Function by the index (4) for the following data set with specified criteria. So, to know this, you can follow the below steps accordingly.
Steps:
Firstly, we have specified limits for age and height in columns G and C.
Then, choose cell I5.
After that, write down the following formula with specific criteria.
=DVAR(B4:E10,4,G4:H5)
So, press Enter.
Therefore, you will get the following output of variance of weight.
Read More: How to Determine If a Number Is Even in Excel (4 Suitable Ways)
Conclusion
In this article, we’ve covered two suitable examples of how to use the DVAR function in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
How to Use Macaulay Duration Formula in Excel (2 Easy Methods)
Excel DSUM vs SUMIF Functions (2 Suitable Examples)
How to Use DMIN Function in Excel (4 Suitable Examples)
Can Excel DGET Return Multiple Records [See 4 Solutions]
How to Use DOLLAR Function in Excel (5 Suitable Examples)
Use EDATE Formula for Days (3 Ideal Examples)
How to Use DCOUNT Function in Excel (5 Suitable Examples)
The post How to Use DVAR Function in Excel (2 Suitable Examples) appeared first on ExcelDemy.
