Click here to go back to  Computer Education
TrainingDownloads Home page

If you would like to download the PDF version of Excel Functions ebook (instead of reading it on-line), you may do so via ClickBank's on-line order system. Payment is made with your credit or debit card. After payment you can then immediately download the ebook.
Click here to order and download Excel Functions ebook - price $9.95 (USD)

Excel Tutorial: Functions

This section is a reference of the more commonly used Excel functions from the following function categories: Database, Date and Time, Financial, Information, Logical, Lookup and Reference, Math and Trigonometry, Statistical, and Text.

Each of the following subsections begins with a brief description of the function category, followed by information on specific functions in that category. Information supplied on these functions includes the syntax, a description of the function, what you need to supply for the function to work, what the resulting information will be, and examples (for selected functions), as well as occasional tips, notes, cautions, and troubleshooting items.

Because Microsoft does not provide paper documentation of Excel's functions, we have included the most common and useful functions in this convenient reference. For information on some of the less used functions, or additional information on the more complex aspects of these functions, choose Help, Contents and Index (in Excel). Then click the Index tab and search on "functions."

NOTE: Function arguments appear in italic throughout this section. In syntax lines, all required arguments are indicated in bold italic; optional arguments appear in italic only.

The database functions provide summary statistics on a database or list in a worksheet. They enable you to analyse whether values in the list meet a specified condition, or criteria. Excel's database functions all use the same arguments: database, field, and criteria. These arguments can represent any range you specify in the worksheet.

For the database argument in any database function, you can specify a cell range (such as A5:G20) or a range name (such as DATABASE). Be sure to include the headers row in the database range or range name.

The field argument indicates the column you want to average. You can specify the field by its field name in quotation marks (such as "City"), by a reference to the cell containing the field name (such as E5), or by a number--1 always represents the first field (or column) in the database, 2 is the second, and so on.

The criteria argument can be a cell range (such as A1:G3), or a range name (such as CRITERIA). The criteria you enter in a worksheet must not overlap the database. Also, do not place the criteria range below the database, or it may be overwritten as you add records to the database.

Database Average

Syntax: =DAVERAGE (database,field,criteria) The DAVERAGE function averages the numbers in the field of the database for those records that satisfy the criteria.

You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.

Result is: the average of the numbers in the column of the list that match the criteria you specify.

Example: In the following formula, the database being analysed resides in B10:G75, the field being averaged has the heading Items, and the criteria is in a range with the name CritSales. Remember to use quotation marks when you reference a specific field name.

=DAVERAGE(B10:G75,"Items",CritSales)

Database Count (Nonblank Cells)

Syntax: =DCOUNTA (database,field,criteria) The DCOUNTA function counts the number of nonblank cells in the field of the database for those records that satisfy the criteria. If the field argument is omitted, DCOUNTA counts all nonblank records in the database that satisfy the criteria.

You Supply: database as the cell range or range name of the entire list; (optional) field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.

Result is: the total count of the nonblank cells in the column of the list that match the criteria you specify.