Database Functions

Microsoft Excel includes 12 worksheet functions that analyze data stored in lists or databases. Each of these functions, referred to collectively as the Dfunctions, uses three arguments: database, field, and criteria. These arguments refer to the worksheet ranges that are used by the function.

Syntax

Dfunction(database,field,criteria)

Database is the range of cells that make up the list or database.

Field   indicates which column is used in the function. Columns of data in the list must have an identifying label in the first row. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield" in the example list below, or as a number that represents the position of the column in the list: 1 for the first column (Tree in the example below), 2 for the second (Height), and so on.

Criteria   is a reference to a range of cells that specify conditions for the function. The function returns information from the list that matches the conditions specified in the criteria range. The criteria range includes a copy of the column label in the list for the column you want the function to summarize. The criteria reference can be entered as a cell range, such as A1:F2 in the example database below, or as a name that represents the range, such as "Criteria."

Tips   

Examples

The following illustration shows a database for a small orchard. Each record contains information about one tree. The range A5:E11 is named Database, and the range A1:F3 is named Criteria.

A

B

C

D

E

F

1

Tree

Height

Age

Yield

Profit

Height

2

Apple

>10

<16

3

Pear

4

5

Tree

Height

Age

Yield

Profit

6

Apple

18

20

14

$105.00

7

Pear

12

12

10

$96.00

8

Cherry

13

14

10

$105.00

9

Apple

14

15

10

$75.00

10

Pear

9

8

8

$76.80

11

Apple

9

9

6

$45.00


DCOUNT(Database,"Age",A1:F2) equals 1. This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers.

DCOUNTA(Database,"Profit",A1:F2) equals 1. This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank.

DMAX(Database,"Profit",A1:A3) equals $105.00, the maximum profit of apple and pear trees.

DMIN(Database,"Profit",A1:B2) equals $75.00, the minimum profit of apple trees over 10.

DSUM(Database,"Profit",A1:A2) equals $225.00, the total profit from apple trees.

DSUM(Database,"Profit",A1:F2) equals $75.00, the total profit from apple trees with a height between 10 and 16.

DPRODUCT(Database,"Yield",A1:F2) equals 140, the product of the yields from apple trees with a height between 10 and 16.

DAVERAGE(Database,"Yield",A1:B2) equals 12, the average yield of apple trees over 10 feet in height.

DAVERAGE(Database,3,Database) equals 13, the average age of all trees in the database.

DSTDEV(Database,"Yield",A1:A3) equals 2.97, the estimated standard deviation in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population.

DSTDEVP(Database,"Yield",A1:A3) equals 2.65, the true standard deviation in the yield of apple and pear trees if the data in the database is the entire population.

DVAR(Database,"Yield",A1:A3) equals 8.8, the estimated variance in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population.

DVARP(Database,"Yield",A1:A3) equals 7.04, the true variance in the yield of apple and pear trees if the data in the database is the entire orchard population.

DGET(Database,"Yield",Criteria) returns the #NUM! error value because more than one record meets the criteria.