How to use the DVARP function
What is the DVARP function?
The DVARP function returns the variance of an entire population based on given condition or criteria in a list or database.
Table of Contents
1. Introduction
Does the DVARP function ignore error values?
No, it doesn't ignore error values.
What is DVARP an abbreviation of?
DVARP is an abbreviation of Database Variance Population.
What is variance?
Variance is a metric used in statistics, it shows how much a set of numbers are spread out from their average value, see below example.
Both Set1 and Set2 above have the same average 30, however, values in Set2 are much more spread out.
Set1 variance: 170 and Set2 variance: 8600.
What is variance based on a population vs variance based on a sample?
The main difference between population variance and sample variance is that the population variance is calculated from an entire population of data:
σ2 = Σ (x - μ)2 / N
N is the total number of data points in the population.
μ is the true population mean.
Whereas the sample variance is calculated from a sample drawn from a population
s2 = Σ(x - x̅)2 / (n - 1)
n is the number of data points in the sample.
x̅ is the sample mean.
What is the mean?
The arithmetic mean is the average in other words.
The average is a way to calculate central tendency which is the place of the center of a set of numbers in a statistical distribution. The most often used measures to calculate central tendency are:
- Average - arithmetic mean.
- Median - the middle number of a group of numbers.
- Mode - the most frequent item in a group
However, the arithmetic mean is used in the DVARP function.
What is arithmetic mean?
The arithmetic mean is calculated by dividing the sum of all values by the number of values.
For example, an array contains these numbers: 3,2,1
The sum is 3 + 2 + 1 equals 6
The number of values is 3.
6/3 equals 2. The average of 3, 2, 1 is 2
What is a database in this context?
Excel defines a database as a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Why use the DVARP function?
The DVARP function calculates the variance based on cells that match a condition or criteria in a list/database whereas the VAR.P function calculates variance without a condition/criteria.
Where can you place the criteria range?
You can place your criteria range wherever you want on your worksheet, however, it is not recommended below the list/database. The function needs a blank row below the list to work properly.
What criteria characters are allowed?
Allowed criteria range characters are less than and greater than signs <>, use them to specify a criteria range. Also, asterisks * can be used to match partial strings.
How to calculate the Variance for the entire list/database?
To include the entire list/database enter a blank line below the criteria range column labels.
2. Syntax
DVARP(database, field, criteria)
| database | Required. The cell reference to a list or database. |
| field | Required. The field argument lets you choose which column to use. You can use the column name enclosed with double quotation marks or the corresponding column number. |
| criteria | Required. A cell reference to the criteria range. The criteria range needs to have column labels and at least one condition below the column label. |
4. Example
What is the variance of the "Number" values for items with a "Size" of "L" in the given data set?
This demonstrates an Excel function that calculates the variance of a specific column (field) within a data range based on provided criteria. The formula in cell G15 is:
Arguments:
- B18:D26 - This is a range reference that contains the data for the calculation. It includes the Item, Size, and Number columns from rows 18 to 26.
- 3 - This is a constant value that specifies the data field for which the variance is to be calculated. In this case, 3 refers to the third column (Number) within the data range.
- B15:D16 - This is another range reference that contains the criteria for the calculation. In this case, it includes the value "L" in cell B16 which is the criteria for the Size column.
The data in the referenced ranges:
- B18:D26 contains a list of items with their sizes and associated numbers.
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| A412 | S | 190 |
| A341 | L | 550 |
| A340 | M | 730 |
| A202 | L | 623 |
- B15:D16 contains the criteria "L" in cell B16, which is the criteria for the Size column.
| Item | Size | Number |
| L |
The DVARP function calculates the variance of the Number values for those rows where the Size value matches the criteria "L".
The result 3268.67 is derived by calculating the variance of the numbers for items with size "L" in the data range. These items are A103, A341, and A202, with numbers 690, 550, and 623, respectively.
The image displays a graph showing the probability density function (PDF) of a normal distribution with a mean around 621 and a standard deviation around 57. The calculated variance (σ^2) of 3268.67, and the square root of the variance (σ) of 57.17, which is the standard deviation.
Follow these steps to calculate the variance from a population using the formula Σ(x - x̄)^2/n based on these observations 690, 550, and 623:
- Find the mean (x̄) of the observations:
x̄ = (690 + 550 + 623) / 3
x̄ = 1863 / 3
x̄ = 621 - Calculate the deviation of each observation from the mean (x - x̄):
690 - 621 = 69
550 - 621 = -71
623 - 621 = 2 - Square each deviation:
69^2 = 4761
(-71)^2 = 5041
2^2 = 4 - Sum the squared deviations:
4761 + 5041 + 4 = 9806 - Divide the sum of squared deviations by n, where n is the total number of observations:
9806 / 3 = 3268.67
The variance of the population observations 690, 550, and 623 is 3268.67 which matches the result shown in the image.
Σ(x - x̄)^2/n
Σ represents the summation of the squared deviations
x is each individual observation
x̄ is the mean of the observations
n is the total number of observations
This formula calculates the average squared deviation from the mean for the entire population, giving the variance.
Functions in 'Database' category
The DVARP function function is one of 11 functions in the 'Database' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form