Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use DVAR Function in Excel (2 Suitable Examples)

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 . 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.

Suitable Examples to Use DVAR Function in Excel


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.

Using DVAR Function for Named Ranges in Excel

  • 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.

Applying DVAR Function by Index in Excel

  • 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

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo