11.4 Database Worksheet Functions

Excel recognizes a variety of so-called database functions. These are worksheet functions that enable complex database calculations, such as the calculation of the average employee salary among those employees who have worked in the company for more than five years ( assuming , of course, that the database contains a column with salary information and a second column with the date on which the employee joined the company). Most database functions begin with the letter D and feature three parameters: =Dfunction(database, column, criteria) .

With the parameter database the range of cells is specified, including the header cells . Instead of directly specifying the range, the range's name can be given.
column specifies for which column in the database the calculation is to be carried out (for example, creating an average). The column can be given either by specifying the header cell of this column or by the column name inside quotation marks (e.g., "LastName" ).
criteria refers to a range of cells with criteria. The database function calculates its result based only on those cells in the database that meet the criteria. (It is precisely this third parameter that distinguishes the database functions from garden-variety worksheet functions.) Criteria are formed as with advanced filters (a header row plus one or more rows with conditions; see the previous section).

CALCULATIONAL FUNCTIONS
DCOUNT	returns the number of records that satisfy the criteria
DCOUNTA	as above, but considers only those records whose data field is not empty in the specified column
DMIN / DMAX	returns the smallest/largest value in the specified column
DSUM	sums over the specified column
DPRODUCT	multiplies the data fields of the column

STATISTICAL FUNCTIONS
DAVERAGE	computes the average value of the column
DSTDEV	computes the estimated standard deviation for a sample (division by `n-1` )
DSTDEVP	computes the true standard deviation for the entire population (division by `n` )
DVAR	computes the variance for a sample
DVARP	computes the variance for the entire population

OTHER
DGET	returns the contents of a column's data that meets the criteria. The function is useful only if the criteria are so formulated that precisely one data record satisfies them. If several records satisfy the conditions, then the function returns the error value #VALUE! .
SUBTOTAL	computes the sum, average, minimum, maximum, etc. of a selected range. The function resembles a database function, but formally it is not one (different syntax, different functionality). It is instituted by the command DataSubtotals, but is essentially useless. Functions such as SUM and AVERAGE can be used much more easily than SUBTOTAL .

Even if it was repeated explicitly above, we repeat it here: All these functions with the exception of SUBTOTAL operate exclusively on those data records that meet the criteria specified by the third parameter.

Tip	Excel also recognizes some worksheet functions that are similar to a database function, but do not require the planned construction of a database (such as COUNTIF, SUMIF).

Example 1: Stock Management

In Figure 11-10 you see a simple stock management application that can be evaluated with certain database functions. The table is stored in the example file DB_Functions.xls .

Figure 11-10: The application of database functions

Most of the examples in Figure 11-10 speak for themselves and require no additional comment. But here are some exceptions: The criterion for the second database formula consists of the header "price" and an additional empty cell. This ensures that all data records are considered , since this pseudocondition is satisfied by all data records. The value 300 in the third criterion is taken from the result of the second database formula. Thus cell A20 contains the formula =F16 .

Please note that all the results in DB_Functions.xls are immediately recalculated as soon as the data on which they are based are altered . This is not at all unusual in Excel worksheets, but there are very few database programs that are capable of this.

Example 2: Frequency Distribution

Figure 11-11 demonstrates an additional application of database functions: From the reaction times of 69 experimental subjects a frequency distribution is determined with a class length of 0.1 seconds. The construction of this table is difficult, despite all of Excel's assistance ”seventeen criterion ranges and as many DCOUNT formulas have to be input. For example, the formula in E3 is

 =DCOUNT(A:B,"reaction time", G5:H6)

Figure 11-11: Frequency distribution with database functions

Nonetheless, this schema functions independently of the number of subjects (data items), since all of columns A and B are part of the database (regardless of how many rows actually contain data).