Excel Dfunction Tables

Each of the Excel Dfunctions has three key user-specified parameters:

Data Table
Column Identifier
Selection Criteria

Example
Function List

Data Table

The table must be organized as columns in a contiguous rectangle. Each column should have a unique column heading in the first row. These headings are used by the criteria parameter to control the selection of data.

Example
The table in columns B through G and rows 2 through 9 includes six unique column headings in its first row. The table can be specified as B2:G9.

Notes
Nine ot the 48 cells in the table are blank. This does not cause any problem with the Dfunctions. Like all Excel functions, they simply ignore blank cells.

It is often handy to specify the data table by an absolute address ($B$2:$G$9) instead of a simple relative address (B2:G9). This greatly simplifies copying Dfunction formulas to other cells in your spreadsheet.

Column Identifier

There are three different ways to specify the column indentification parameter:

an integer between one and the number of columns in the table
the unique name of the desired column
a cell address containing the desired column number or name.

Selection Criteria

The Dfunctions allow the user to specify selection criteria that are very simple (e.g., Person=Adam) as well as very complex (e.g., Person=Adam or Bill or Jill, Feb>=270 and Apr>=270). The beauty of the Dfunctions is that all selection criteria are handled exactly the same.

A selection criteria is simply a table describing acceptable rows. The criteria table must include column headings in the first row that exactly match the corresponding headings in the basic data table. Each row under the heading in the criteria table is treated as an OR option and each column is treated as an AND option.

Example

Simple Criteria
Cells B12:B13 specify the simple selection criteria of Person=Adam. That is, only the information in the Adam row of the table (i.e., row 4) will be processed by the Dfunction.

Cells D12:D13 specify the simple selection criteria of Feb>=250. Four rows (3, 4, 5, and 7) that meet this selection criteria.

OR Criteria
Cells F12:F15 specify the selection criteria of Person=Adam or Person=Bill or Person=Jill. Only those three rows of data will be processed by the Dfunction.
AND Criteria
Cells B17:C18 specify the selection criteria of Feb>=270 and Apr>=270. Two rows (5 and 7) meet this selection criteria.
Combined Criteria
ANDs and ORs can be combined in a single selection criteria. Cells E17:F19 specify the selection criteria of (Feb=out and Apr=anything) or (Feb=anything and Apr=out). A blank cell in a selection criteria is treated as an automatic match with anything in the data table. Only two rows (4 and 6) meet this selection criteria. The wildcard symbol * can also be used to match any string value. The wildcard symbol ? can be used to match any single character in a string value.
Note
It is often handy to specify the criteria table by an absolute address ($B$17:$C$18) instead of a simple relative address (B17:C18). This greatly simplifies copying Dfunction formulas to other cells in your spreadsheet.

Examples

Cell B22 formula: DGET( B2:G9 , "Jan" , B12:B13 )
This gets the January value for Adam.
Cell B23 formula: DSUM( B2:G9 , "Jan" , D12:D13 )
This sums the January values for Jack, Adam, Mary and Jill.
Cell B24 formula: DMAX( B2:G9 , "Jan" , F12:F15 )
This determines the largest of Adam, Bill and Jill's January values.
Cell B25 formula: DCOUNTA( B2:G9 , "Person" , B17:C18 )
This counts the number of people who exceeded 270 in February or April.
Cell B26 formula: DAVERAGE( B2:G9 , "Jan" , E17:F19 )
This determines the mean January value for the people who were "out" in either February or April.

Dfunction List

DAVERAGE
DCOUNT
DCOUNTA
DGET
DMAX
DMIN
DSTDEV
DSTDEVP
DSUM
DVAR
DVARP