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”})
 
Search JabSto ::




Custom Search