Database Functions
Table of Contents
- How to use the DAVERAGE function
- How to use the DCOUNT function
- How to use the DCOUNTA function
- How to use the DGET function
- How to use the DMAX function
- How to use the DMIN function
- How to use the DPRODUCT function
- How to use the DSTDEV function
- How to use the DSTDEVP function
- How to use the DSUM function
- How to use the DVARP function
1. How to use the DAVERAGE function
What is the DAVERAGE function?
The DAVERAGE function calculates an average based on values in a list or database that meet specific conditions.
What is the average?
It is also known as the mean. It is calculated by adding up all the values in the data set and dividing by the number of values.
For example, if you have a data set of 5, 7, 9, 11, and 13, the mean is (5 + 7 + 9 + 11 + 13) / 5 = 9.
Does the DAVERAGE function count blank cells, boolean, and text values?
No, blank cells, boolean values and text values are not counted.
Does the DAVERAGE function ignore error values?
Yes, it ignores error values.
What is DAVERAGE an abbreviation of?
DAVERAGE is an abbreviation of Database Average.
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 DAVERAGE function?
The DAVERAGE function calculates the average of cells containing numbers that match a condition or criteria in a list/database whereas the AVERAGE function calculates an average 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 AVERAGE for the entire list/database?
To include the entire list/database enter a blank line below the criteria range column labels.
DAVERAGE Function Syntax
DAVERAGE(database, field, criteria)
DAVERAGE 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 a at least one condition below the column label. |
DAVERAGE Function Example 1
This example demonstrates how to use the DAVERAGE function using criteria with OR logic. The criteria are specified in cells B2:D4, the column header names must be specified as well in order to work properly.
The "Size" column has two conditions "M" and "L" and they are in a row each meaning OR logic is performed. This checks if either "M" or "L" is in the database B7:D12 in column "Size" and extracts numbers from column "Number" on the same row.
Formula in cell B15:
The formula calculates the average of the numbers in column "Number" if the "Size" is M or L. Cells D7, D8, D11, and D12 match and they contain 370, 690, 550, and 730.
The total is 370 + 690 + 550 + 730 equals 2340. To calculate the average we divide the total by the count: 2340 / 4 equals 585.
DAVERAGE Function Example 2
This example demonstrates how to combine four conditions and perform AND and OR logic. The criteria are specified in cells B2:D4, the column header names must be specified as well in order to work properly.
The "Item" column has a condition that checks if the values begin with an "A", the asterisk matches 1 to many characters of any kind. The "Size" column has a "M" specified on the same row as the "Item" condition which means that both conditions must be met, in other words, AND logic.
The next row (4) has "L" in "Size" column and "<650" in the "Number" column, both these conditions must be met. Since two conditions are entered in row 3 and two conditions are entered in row means that there is OR logic applied between these condition pairs.
So if the conditions in row 3 are met or the conditions in row 4 are met in order to include the corresponding number in the average calculation.
Formula in cell B15:
Row 7 meets both the conditions in row 3, number 370 is included in the calculation. Row 8 does not meet any of the condition pairs specified in rows 3 and 4, number 690 is not included.
The same thing applies to row 9 and 10, none of the condition pairs match. Number 310 and 190 are not included. Row 11 matches the condition pair specified in row 4, number 550 is included. Row 12 doesn't match any of the condition pairs.
The filtered numbers are 370 and 550, the total is 370 + 550 equals 920. 920 / 2 equals 460.
2. How to use the DCOUNT function
What is the DCOUNT function?
The DCOUNT function counts cells containing only numbers and that meet a condition or criteria.
Does the DCOUNT function count blank cells, boolean, and text values?
No, blank cells, boolean values and text values are not counted.
Does the DCOUNT function ignore error values?
Yes, it ignores error values.
What is DCOUNT an abbreviation of?
DCOUNT is an abbreviation of Database Count.
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 DCOUNT function?
The DCOUNT function counts cells containing only numbers that match a condition or criteria in a list/database whereas the COUNT function counts cells containing only numbers 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.
DCOUNT Function Syntax
DCOUNT(database, field, criteria)
DCOUNT 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 a at least one condition below the column label. |
DCOUNT Function Example 1
This example shows how to count numbers in cell range D7:D12 if they are larger than 250 and smaller than 700. The criteria is specified in cell B3 and C3, note that both has column header "Number" in order to filter correctly.
The first argument is the database which in this case is in cell range B6:D12, the second argument is a column number corresponding to columns in cell range B6:D12 from left to right. The third argument is the a cell reference to the condition or critera.
Formula in cell D15:
The formula counts cells in column 3 (D) that contains a number and is less than 700 and larger than 250 which are cells D7, D8, D9, and D11. They all contain numbers and the DCOUNT function returns 4. Cells D10 and D12 are not counted because they are larger than or equal to 750 or smaller than or equal to 250.
DCOUNT Function Example 2
This example demonstrates how to use the asterisk character to filter rows if the corresponding cell on the same row in B6:B11 contains 3. The asterisk matches any character(s) from 1 to many.
Cells B7,B10, and B11 contain number 3, the corresponding cells in D6:D11 all contain numbers. The DCOUNT function returns 3.
Formula in cell D14:
The first argument is a cell reference pointing to the database or the list, in this case, cell range B5:D11. The second argument is a number representing a given column in B5:D11 from left to right. For example, 3 is the third column in cell range B5:D11 which is D5:D11.
The third argument is the location of the criteria and this example has only one condition.
DCOUNT Function Example 3
This example demonstrates three different criteria:
- If values in column "Item" begin with A and then 1 or more characters.
- If values in column "Size" are not equal to "S". The smaller than and larger than characters combined results in "not equal to".
- If values in column "Number" are larger than 600.
All these criteria are specified on the same row which means that all criteria must match or in other words "AND" logic is performed.
Formula in cell D14:
The first column "Item" has four matching values in cells B6,B7,B8,B10, and B11. The second column named "Size" has four matching values in cells C6,C7,C10, and C11.
The last column "Number" has one value larger than 600 in cell D7. This means that only D7 is valid based on criteria, D7 is also a number meaning that the DCOUNT function returns 1.
3. How to use the DCOUNTA function
What is the DCOUNTA function?
The DCOUNTA function counts nonempty cells in a column you specify, in a database where records also meet a condition or criteria.
Table of Contents
1. Introduction
What is DCOUNTA an abbreviation of?
DCOUNTA is an abbreviation of Database Counta. COUNTA probably means count all, in other words, it counts all cells in a given range that contains data.
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.
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.
Does the DCOUNTA function ignore blank cells?
Yes, blank cells are ignored.
Does the DCOUNTA function ignore error values?
No, the DMAX function does not ignores error values. A cell containing an error value is counted.
What is the difference between the DCOUNTA function and the COUNTA function?
The DCOUNTA function counts non empty cells based on a condition or criteria in a list/database whereas the COUNTA function counts non empty cells without using a condition/criteria.
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels. In other words, don't specify any criteria.
2. DCOUNTA Function Syntax
DCOUNT(database, field, criteria)
3. DCOUNTA Function Arguments
| database | Required. The cell reference to a list or database. |
| field | Optional. 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. If not entered DCOUNTA counts all records in the database that match the condition. |
| criteria | Required. A cell reference to the criteria range. The criteria range needs to have column labels and a at least one condition below the column label. |
4. DCOUNTA Function Example
Formula in cell B15:
The formula counts records where the number is above 700 or below 250. Also, the cell must not be blank in column 3 (Number).
Only row 9 and row 12 match the criteria, the function returns 2 in cell B15.
4. How to use the DGET function
What is the DGET function?
The DGET function fetches a value from a column in a database whose records meet a condition or criteria.
Table of Contents
1 . Introduction
What is DGET an abbreviation of?
DGET is an abbreviation of Database Get.
What is GET?
There is no function named GET in Excel, however, VLOOKUP, HLOOKUP, LOOKUP, and XLOOKUP are somewhat similar in functionality.
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.
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 DGETÂ for the entire list/database?
To include the entire list/database enter a blank line below the criteria range column labels.
2. DGET Function Syntax
DGET(database, field, criteria)
3. DGET 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. DGET Function Example 1
This example uses one condition to get a single value from column "Number". The condition is specified in cell B3 and and is applied to column "Item".
The condition "A412" matches cell B10 and the value in column "Number" on the same row is "190".
Formula in cell B15:
The formula returns "190" in cell B15.
5. DGET Function Example 2
The image above shows how to use two criteria and both conditions must match on the same row. The conditions are:
- "M" in column "Size"
- ">700" in column "Number"
The conditions are entered on the same row which is 3, this means that both conditions must match, in other words "AND"-logic.
Formula in cell B15:
The formula extracts a value from column Item based on two conditions. The size must be M and number must be above 700.
Only one record match, the function returns A340 in cell B15.
6. DGET Function not working
The DGET function returns a #NUM error if there is more than one match. The image above demonstrates this error, the condition is specified in cell B3 which is "M" and is applied to column "Size" which is specified in cell B2.
Formula in cell B15:
The result in cell B15 is a #NUM error and this is because there are two cells that match the condition. They are cells C7 and C12, the DGET function can't return both matches, instead a #NUM error is displayed. A workaround is to use the new FILTER function available to Excel 365 subscribers.
Excel 365 dynamic array formula in cell B15:
This formula returns an array of values if necessary, it spills the values to cell B15 and cells below as far as needed. The example above returns values "370" and "730" in cells B15 and B16.
This example shows a #VALUE! error that is triggered if no values match at all. The first condition is specified in cell B3 and is "M" applied to column "Size". The second condition is "B*" which is applied to column "Item".
The asterisk lets you look for any number of characters, in this case, the condition means that any cell value that begins with "B" regardless of the remaining characters is a match. However, none of the cells in C7:C12 match this condition.
Formula in cell B15:
The result is a #VALUE error, there are no records that match both conditions.
5. How to use the DMAX function
What is the DMAX function?
The DMAX function extracts the largest (maximum) number from a column in a database whose records match a condition or criteria.
Table of Contents
1. Introduction
What is DMAX an abbreviation of?
DMAX is an abbreviation of Database Max.
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.
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.
Does the DMAX function ignore blank cells, boolean, and text values?
Yes, blank cells, boolean values and text values are ignored.
Does the DMAX function ignore error values?
No, the DMAX function does not ignores error values.
What is the difference between the DMAX function and the MAX function?
The DMAX function calculates the largest number based on a condition or criteria in a list/database whereas the MAX function calculates the largest number without using a condition/criteria.
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels. In other words, don't specify any criteria.
2. DMAX Function Syntax
DMAX(database, field, criteria)
3. DMAX 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. DMAX Function Example
Formula in cell B15:
The formula gets the largest number from column 3 (D) with the condition that the size is L. The maximum number of 690 and 550 is 690 which is returned in cell B15.
6. How to use the DMIN function
What is the DMIN function?
The DMIN function extracts the smallest number from a column in a database whose records match a condition or criteria.
Table of Contents
1. Introduction
How is the min number calculated?
The minimum number is simply the smallest number in a group of numbers.
What is DMIN an abbreviation of?
DMIN is an abbreviation of Database Minimum.
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.
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.
Does the DMIN function add blank cells, boolean, and text values?
No, blank cells, boolean values and text values are ignored.
Does the DMIN function ignore error values?
No, the DMIN function does not ignore error values.
What is the difference between the DMIN function and the MIN function?
The MIN function calculates the smallest number that match a condition or criteria in a list/database whereas the MIN function calculates the smallest number without a condition/criteria.
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels. In other words, don't specify any criteria.
2. DMIN Function Syntax
DMIN(database, field, criteria)
3. DMIN 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. DMIN Function Example
This example demonstrates the DMIN function using a single condition. The condition is specified above the database/list in cell range B2:C3 and contains the column header names. The DMIN function needs the column header names in order to apply the criteria to the correct column in the database/list.
Formula in cell B15:
The formula gets the minimum number from column 3 (D) with the condition that the size is L. The smallest number of 690 and 550 is 550 which is returned in cell B15.
7. How to use the DPRODUCT function
What is the DPRODUCT function?
The DPRODUCT function multiplies numbers that match a condition or criteria in a database.
What is DPRODUCT an abbreviation of?
DPRODUCT is an abbreviation of Database Product.
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 DPRODUCT function?
The DPRODUCT function multiplies numbers that match a condition or criteria in a list/database whereas the PRODUCT function performs a calculation 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.
What is a product?
A product is what you get when you multiply two numbers.
What is multiplication?
It is one of the four basic arithmetic operations. Multiplication means adding a number together multiple times. It is a faster way to add the same number over and over again.
For example:
5 x 3 means adding 5 together 3 times:
5 + 5 + 5 = 15
So multiplication is a shorthand for repeated addition.
What is repeated multiplication?
The PRODUCT faction returns the product of two or more numbers. If the numbers are repeated you can use exponentiation to calculate the result.
Repeated multiplication involves using multiplication to find the result of multiplying a number by itself multiple times.
For example:
- Repeatedly multiplying 5 by itself 3 times is written: 5 x 5 x 5
- The result is:
5 x 5 x 5 = 125
Repeated multiplication and exponentiation describe the same fundamental mathematical operation, 2 x 2 x 2 x 2 = 24
Exponentiation lets you shorten repeated multiplication considerably.
The POWER function lets you calculate a number multiplied x repeated times.
For example 5 * 5 * 5 = 53
=POWER(5,3) equals 125
you also have the alternative to use the ^character like this:
=5^3 equals 125
DPRODUCT Function Syntax
PRODUCT (database, field, criteria)
DPRODUCT 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. |
DPRODUCT Function Example
Formula in cell B15:
The formula gets numbers from column 3 (D) with the condition that the size is S.
310 * 190 = 58900
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels.
8. How to use the DSTDEV function
What is the DSTDEV function?
The DSTDEV function calculates an estimation of the standard deviation based on a sample of a population. The function allows you to specify criteria applied to a population/database.
What is DSTDEV an abbreviation of?
DSTDEV is an abbreviation of Database Standard Deviation.
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 DSTDEV function?
The DSTDEV function multiplies numbers that match a condition or criteria in a list/database whereas the STDEV.S function performs a calculation 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.
What is a STDEV?
STDEV stands for standard deviation.
Standard deviation tells you how far from the average values are spread out. Both charts above have numbers and an average plotted, they share the exact same average however, the numbers are not the same.
Chart A above shows that the values are more spread out than the values in chart B. Chart A has a standard deviation of 23.45256334, standard deviation for chart B is 5.207075606. Standard deviation is fundamental in statistics.
What is the difference between the DSTDEV function and the DSTDEVP function?
DSTDEVP function calculates the standard deviation for a population and the DSTDEV function calculates the standard deviation for a sample. DSTDEVP uses the count of all values (n) in the denominator.
DSTDEV uses (n-1) in the denominator (Bessel's correction). This accounts for the difference between sample variance and population variance in statistics. DSTDEV is better for sample inferential statistics.
DSTDEVP math formula:
DSTDEV math formula:
When to use the DSTDEVP function and the DSTDEV function?
Use DSTDEVP if you have the full population data. Use DSTDEV if you have a sample of limited data from a larger population. DSTDEVP will result in a lower standard deviation compared to DSTDEV on the same data.
Sample standard deviation is considered a better estimate for inferring population parameters.
What is inferring population parameters?
Population parameters refer to the actual values of statistics that describe an entire population, such as the population mean or standard deviation. However, the true population parameter values are often not known.
What is sample inferential statistics?
Sample inferential statistics are methods that allow using statistics calculated on a sample of data to infer the unknown population parameters.
For example:
- The sample mean can be used to estimate the population mean.
- The sample standard deviation can estimate the population standard deviation.
DSTDEV Function Syntax
DSTDEV(database, field, criteria)
DSTDEV 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. |
DSTDEV Function Example
Formula in cell B15:
9. How to use the DSTDEVP function
What is the DSTDEVP function?
The DSTDEVP function calculates the standard deviation based on a population. The function also allows you to specify criteria applied to your data.
What is DSTDEVP an abbreviation of?
DSTDEVP is an abbreviation of database standard deviation population.
What is Standard Deviation?
Standard deviation tells you how far from the average values are spread out. Both charts above have numbers and an average plotted, they share the exact same average however, the numbers are not the same.
Chart A above shows that the values are more spread out than the values in chart B. Chart A has a standard deviation of 23.45256334, standard deviation for chart B is 5.207075606. Standard deviation is fundamental in statistics.
Does the DSTDEVP function count blank cells, boolean, and text values?
No, blank cells, boolean values and text values are not counted.
Does the DSTDEVP function ignore error values?
Yes, it ignores error values.
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 DSTDEVP function?
The DSTDEVP function calculates the standard deviation of cells containing numbers that match a condition or criteria in a list/database whereas the STDEVP function calculates the standard deviation 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 standard deviation for the entire list/database?
To include the entire list/database enter a blank line below the criteria range column labels.
DSTDEVP Function Syntax
DSTDEVP(database, field, criteria)
DSTDEVP 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. |
DSTDEVP Function Example
Formula in cell B15:
10. How to use the DSUM function
What is the DSUM function?
The DSUM function adds numbers in a database that meets a given condition or criteria.
Table of Contents
1. Introduction
How is a sum calculated?
A sum is calculated by adding together two or more numbers. For example, a cell range contains these numbers: 3, 6, and 1. The sum is 3+6+1 which equals to 10.
What is DSUM an abbreviation of?
DSUM is an abbreviation of Database Sum.
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.
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.
Does the DSUM function add blank cells, boolean, and text values?
No, blank cells, boolean values and text values are ignored. They are not a part of the sum.
Does the DSUM function ignore error values?
No, the DSUM function does not ignores error values.
What is the difference between the DSUM function and the SUM function?
The SUM function calculates a total based on numbers that match a condition or criteria in a list/database whereas the SUM function calculates a total containing numbers without a condition/criteria.
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels. In other words, don't specify any criteria.
2. DSUM Function Syntax
DSUM(database, field, criteria)
3. DSUM 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. DSUM Function Example 1
This example shows a formula in cell B15 that sums numbers from column "Number" if corresponding values in column "Item" is not equal to a value that begins with B and values in column "Size" is equal to "L".
The criteria are specified in cell range B2:D3, the column header names must be specified as well in order to match a given condition to a specific column.
Formula in cell B15:
The formula adds numbers from column "Numbers" if the criteria match on the same rows. For example, row 7 and 12 matches the given criteria, 690 + 623 = 1313.
5. DSUM Function Example 2
The formula in cell B16 calculates a total based on the criteria specified in cell range B2:D4, this particular case shows how to apply OR logic.
The criteria is "L" or "M" shown in cells C3 and C4, since they are on a row each OR logic is applied.
Formula in cell B16:
The formula adds numbers from column "Number" if the corresponding cell on the same row in column "Size" is equal to "L" or "M". Cells C7, C8, C11, C12, and C13 match "L" or "M" and the corresponding cells in column "Number" are D7, D8, D11, D12, and D13.
6. DSUM Function Example 3
The formula in cell B16 calculates a total based on the criteria specified in cell range B2:D4, this particular case shows how to combine AND and OR logic to the criteria.
The criteria is "L" or "M" shown in cells C3 and C4, since they are on a row each OR logic is applied, however, both criteria on the same row must be met before the OR condition is applied. Next to condition "L" is <>*2 which means not equal to values that end with 2 applied to column "Item". Both these criteria L and <>*2 are on the same row meaning AND logic. Both these conditions must be met to include the number from column "number" to the total.
Condition M has a fourth condition next to it which is >500 meaning the number in column "Number" must be larger than 500. If both these conditions are met then the number in column "Number" is added to the total.
Formula in cell B16:
For example, cell B7 contains A102 which is ruled out by condition <>*2, the second row rules out 370 since condition >500 is not met.
Row 8 matches the first criteria pair specified in B3 and C3. 690 in cell D8 is included to the total.
Rows 9 and 10 match none of the criteria pairs, S is not equal to neither M or L.
Row 11 matches the first criteria pair specified in B3 and C3. 550 is included to the sum.
Row 12 matches on of the criteria pairs, 730 is added to the total. Row 13 is not a match.
690 + 550 + 730 equals 1970.
11. 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. 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.
More than 1300 Excel formulasExcel categories
Leave a Reply
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.
