# 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

s^{2} = Î£(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. DVARP Function Syntax

DVARP(*database*, *field*, *criteria*)

## 3. DVARP Function Arguments

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. DVARP Function Example

The following formula calculates the variance of the records that match condition Size "L". The corresponding numbers in column D are 690, 550 and 623.

Formula in cellÂ B15:

## 5. How is the variance calculated in detail?

The equation for DVARP is:

x is the true mean of the population.

n is the population size.

### 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 signsUse 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 OscarYou can contact me through this contact form