Home > Articles > Home & Office Computing > Microsoft Applications

Excel 2000 Database Functions Overview

  • Print
  • + Share This
  • 💬 Discuss
This chapter is from the book
In Excel, database functions operate over ranges that can span more than one row or column. Learn here how to use the myriad functions at your disposal, using the general syntax for all DFUNCTIONS.

Database Functions Overview

Database functions operate over ranges that can span more than one row or column. As with all functions, you can reference the database with a range name or with the cell references, either way works.

This chapter covers the following functions.

DAVERAGE

DSTDEV

DCOUNT

DSTDEVP

DCOUNTA

DSUM

DGET

DVAR

DMAX

DVARP

DMIN

GETPIVOTDATA

DPRODUCT

 


General DFUNCTION Syntax

If you have large data stores or databases, DFUNCTIONS can be quite useful. The general syntax for the DFUNCTIONS is as follows:

=DFUNCTION(database,field,criteria)
  • The database argument refers to the range encompassing the entire list or database.

  • The field argument refers to a particular column in the list that contains the data that you want calculated. If you omit the field argument, the function operates on the entire list.

  • The criteria argument specifies the basis on which you want the function to select particular cells. Another way to describe it is: Criteria is the specific requirements you set for the return. If you omit the criteria argument, the function operates on the entire range specified in the field argument.

NOTE

The database range can be a cell reference (Example 1a) or a named range (Example 2a) on all database functions.

1a. =DFUNCTION(C3:F17,field,criteria)

2a. =DFUNCTION(database,field,criteria)

The field can be the number of the column such as 3, meaning the third column in the list (Example 1b). It can be a cell reference such as C3 that contains the column heading that you want calculated (Example 2b). The field can also be the column heading's text, however the text must be enclosed in quotes, though it is not case sensitive (Example 3b).

1b. =DFUNCTION(Database,3,Criteria)

2b. =DFUNCTION(Database,C3,Criteria)

3b. =DFUNCTION(Database,"Column Heading Name",Criteria)

The criteria is the range containing restrictions on which data should be included in the calculation. This means that you can specifically call out parameters, such as the sum of all numbers greater than 30, with the DSUM function. n

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus