The DVARP function in Google Sheets is useful if you need the variance of a population, based on the entire population of numbers. The variance is obtained from a field (column) of records in a list or database that matches the conditions that you specify.
DVARP() function is a built-in function that is categorized as a ‘Database Function’. It can be used as a worksheet function and can be entered as part of a formula in a cell of a worksheet.
What is a variance?
For a given data, the variance, a measure of dispersion, measures variability from the average or mean. In simple terms, it is a measure of how much a given value (or data point) varies from the central tendency of the group.
Let’s take an example.
I want to conduct a poll to see how many people in my local community are ready to get vaccinated for the upcoming vaccination drive.
I floated a survey and recorded the responses from all the residents whether they are ready will be available for the drive. In this way, I could make a first approximation based on the responses recorded.
And if I conduct another survey a few days later, I can then compare the results of the polls, and use the difference to compute a margin for error. This is where the
DVARP() function will help me. From the variance thus calculated, I will get a deviation from the standard calculated solution that I can expect in reality, on the date of the vaccination.
That’s just one example. There are plenty of other use-cases for this function in real life. Great! Let’s dive right into such real-business use-cases, where we will deal with actual values and as well as learn how we can write our own
DVARP() function in Google Sheets to calculate variances in data.
The Anatomy of the DVARP Function
So the syntax (the way we write) of the
DVARP function is as follows:
=DVARP(database, field, criteria)
Let’s dissect this thing and understand what each of the terms means:
=the equal sign is just how we start any function in Google Sheets.
DVARP()is our DVARP function. DVARP will return the variance of an entire population selected from a database table-like array or range using a SQL-like query.
- database refers to the array or range having the data, including headers for each column’s values
- field refers to the column in the data which has the values that are to be extracted and worked on.
- field may either be a text label referring to the required column header or a numeric value indicating which column to consider, where the first column has a value = 1.
- criteria refers to an array or range containing the criteria to filter the database values before operating. This may be left blank.
A Real Example of Using DVARP Function
Take a look at the example below to see how DVARP functions are used in Google Sheets
The above figures represent the marks earned by students in a given class over the course of two years, 2019 and 2020, in Mathematics. The objective here is to find the dispersion in the marks from the average marks secured across all the students, for a given year.
Just below the captured data, I have given a provision to enter the criteria based on which the data will be filtered. This is not a required criterion, and the function will still function properly if we leave it blank. As you can see below, I have obtained the variance for scores obtained in the year 2019.
Note that I have shown a couple of different ways to input the field, all of which give the same result. You may try changing the criteria and see how the result changes. Go ahead and make a copy of the spreadsheet using the link I have attached below:
Awesome! Let’s begin our DVARP function in Google Sheets.
How to Use DVARP Function in Google Sheets
- Let’s see how to write your own
DVARP()function, step-by-step. First of all, I have pasted sales data from a single retail store across three years, from 2018 to 2020, for three apparel brands – Nike, Adidas, and Puma.
- Now, simply click on any cell to make it the active cell. For this guide, I will be selecting F14, where I want to show my results.
- Next, simply type the equal sign ‘=‘ to begin the function and then followed by the name of the function, which is our ‘dvarp‘ (or DVARP, whichever works).
- You should find that the auto-suggest box appears with our function of interest. Continue by entering the first opening bracket ‘(‘. If you get a huge box with text in it, simply hit the arrow in the top-right hand corner of the box to minimize it. You should now see it as follows:
- Now, the fun begins! Let’s give the required inputs to the function to get the variance in unit sales, per the filtration criteria we have given above the data:
- Take note of how I’ve specified conditions to limit the data to the Nike brand (selecting only the years 2019 and 2020). The criteria for the formula are input as A1:F3 to account for all the criteria mentioned, if any.
- Once you’ve entered the necessary database, field, and criterion values, or you’ve done what I did, make sure to close the brackets “, as shown below.
- Finally, just hit your Enter key. If you followed my instructions, you should have gotten 75,000 as the function’s output, which is nothing more than the variance of Nike and Puma combined unit sales (for the criteria specified above the data table).
That’s pretty much it. You have everything you need to get started with the
DVARP function on Google Sheets. I recommend experimenting with the DVARP function, combining it with the numerous Google Sheets formulas available, and seeing what you can come up with. 🙂