Microsoft Office Tutorials and References

In Depth Information

**Using Database Functions**

Function

Description

DSTDEVP

Calculates the standard deviation of the selected database entries, based on the entire

population of selected database entries

DSUM

Adds the numbers in the field column of records in the database that match the criteria

DVAR

Estimates the variance from selected database entries (assumes that the data is a sample

from a population)

DVARP

Calculates the variance, based on the entire population of selected database entries

The database functions all require a separate criteria range, which is specified as the last

argument for the function. The database functions use exactly the same type of criteria range as

discussed earlier in the “Specifying Advanced Filter Criteria” section (see Figure 9-25).

The formula in cell B24, which follows, uses the DSUM function to calculate the sum of values in a

table that meet certain criteria. Specifically, the formula returns the sum of the Sales column for

records in which the Month is Feb and the Region is North.

=DSUM(B6:G21,F6,Criteria)

In this case, B6:G21 is the entire table, F6 is the column heading for Sales, and
Criteria
is the name

for B1:C2 (the criteria range).

Following is an alternative version of this formula that uses structured table references:

=DSUM(Table1[#All],Table1[[#Headers],[Sales]],Criteria)

This workbook is available on the companion CD-ROM. The filename is
database

formulas.xlsx
.

You may find it cumbersome to set up a criteria range every time you need to use a

database function. Fortunately, Excel provides some alternative ways to perform

conditional sums and counts. Refer to Chapter 7 for examples that use SUMIF, COUNTIF, and

various other techniques.

If you’re an array formula aficionado, you might be tempted to use a literal array in place of the

criteria range. In theory, the following array formula
should
work (and would eliminate the need

for a separate criteria range). Unfortunately, the database functions do not support arrays, and

this formula simply returns a #VALUE! error.

=DSUM(B6:G21,F6, {“Month”,”Region”;”Feb”,”North”})