How to Use Excel Database Functions DGET, DAVERAGE, & DMAX

There are twelve database functions available in Excel: DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP.

All the database functions are structured in the same way in terms of input arguments:

The DSUM function has the following arguments

=DSUM (database, field, criteria)

The database is the entire range of cells that makes up the database.
The field is the column used in the function.
The criteria are the cells that specify the conditions one wants to apply.

Introduction

In our example, a hypothetical tennis coach running a tennis clinic franchise is evaluating the performance of the players, who come to the clinic franchises, on the different court surfaces. Database functions can query the data according to certain criteria. A picture of the source data is shown below.

How to Use Excel Database Functions

Example 1 – Use the DGET Excel Database Function

Make a copy of the original data since it’s always best to keep a version of the source data intact. We want to see the name of the player, whose PlayerID is #1077. We can use the DGET function to retrieve this value.
Copy the field names of the database to Row 2. It is very important that they are exactly the same since this will form part of our criteria.

Enter the value #1077 in Cell A3, as shown.

In Cell H5, enter the following formula:

=DGET ($A$6: $E$106, “Player Name”, $A$2: $E$3)

The database refers to the entire range of data from $A$6:$E$106, field refers to the column containing the value which one wants to retrieve data from, and criteria forms the basis of the retrieval. We want to retrieve the name of the player whose PlayerID is #1077, this follows a similar logic to a SQL query.

Press Ctrl + Enter, and Alyssa James is returned since she is the player whose PlayerID is #1077. This is also similar to the way one uses a VLOOKUP or HLOOKUP function to look up a value in another column, based on an input value in a different column.

Example 2 – Excel DAVERAGE Function

Make a copy of the original data since it’s always best to keep a version of the source data intact. We want to find out what the average age of the female players who attend the clinics, is.
On a new sheet, copy the field names of the database to Row 2, in order to ensure that they are exactly the same.

Enter the value Female in Cell C3, as shown.

In Cell H6, enter the following formula:

=DAVERAGE ($A$6: $E$106, “Age”, $A$2: $E$3)

Press Ctrl + Enter. You’ll get 21.55 as the average age.

Example 3 – DMAX Database Function

Make a copy of the original data. We want to see the oldest male playing on hard courts, attending the clinics. The database functions can accept multiple criteria, so this is not an issue to compute.
On the new sheet, copy the field names of the database to Row 2, in order to ensure that they are exactly the same.