Database-specific Functions

Apache OpenOffice 4.1 Calc Guide

Chapter 13 Calc as a Simple Database

< Previous Section

Next Section >

• Introduction
  • Example Data
• Associating a Range with a Name
  • Named Range
  • Database Range
• Sorting
• Filters
  • Auto Filters
  • Standard Filters
  • Advanced Filters
  • Manipulating Filtered Data
• Calc Functions Similar to Database Functions
  • Count and Sum Cells that Match Conditions: COUNTIF, COUNTIFS, SUMIF, SUMIFS, and SUMPRODUCT
  • Ignore Filtered Cells Using SUBTOTAL
  • Using Formulas to Find Data
    • Search a Block of Data Using VLOOKUP
    • Search a Block of Data Using HLOOKUP
    • Search a Row or Column Using LOOKUP
    • Use MATCH to Find the Index of a Value in a Range
    • Examples
  • ADDRESS Returns a String with a Cell’s Address
  • INDIRECT Converts a String to a Cell or Range
  • INDEX Returns Cells Inside a Specified Range
• Database-specific Functions
• Macros for a Calc Database
  • Macros for Named Ranges
    • Naming Multiple Ranges
  • Macro for a Database Range
  • Macros for Filtering
    • Macros for Advanced Filters

< Previous Page

Next Page >

Database-specific Functions

Although every Calc function can be used for database manipulation, the functions in Table 27 are specifically designed for use as a database. The descriptions in Table 27 use the following terms interchangeably: row and record, cell and field, and database and all rows.

Table 27. Database functions in a Calc document.

Function	Description
DAVERAGE	Return the average of all fields that match the search criteria.
DCOUNT	Count the number of records containing numeric data that match the search criteria.
DCOUNTA	Count the number of records containing text data that match the search criteria.
DGET	Return the contents of a field that matches the search criteria.
DMAX	Return the maximum content of the fields that match the search criteria.
DMIN	Return the minimum content of the fields that match the search criteria.
DPRODUCT	Return the product of the fields that match the search criteria.
DSTDEV	Calculate the standard deviation using the fields that match the search criteria. The fields are treated as a sample.
DSTDEVP	Calculate the standard deviation using the fields that match the search criteria. The fields are treated as the entire population.
DSUM	Return the sum of all fields that match the search criteria.
DVAR	Calculate the variance using the fields that match the search criteria. The fields are treated as a sample.
DVARP	Calculate the variance using the fields that match the search criteria. The fields are treated as the entire population.

The syntax for the database functions is identical.

DCOUNT(database; database field; search criteria)

The database argument is the cell range that defines the database. The cell range should contain the column labels (see Listing 13). The following examples assume that the data from Table 13 is placed in Sheet 1 and the filter criteria in Table 14 is placed in Sheet 2.

Listing 13. The database argument includes the headers.

=DCOUNT(A1:G16; "Test 2"; Sheet2.A1:G3)

The database field specifies the column on which the function operates after the search criteria is applied and the data rows are selected. The database field can be specified using the column header name or as an integer. If the column is specified as an integer, 0 specifies the entire data range, 1 specifies the first column, 2 specifies the second column, and so on. Listing 14 calculates the average test score for the rows that match the search criteria.

Listing 14. “Test 2” is column 3.

=DAVERAGE(A1:G16; "Test 2"; Sheet2.A1:G3)
=DAVERAGE(A1:G16; 3; Sheet2.A1:G3)

The search criteria is the cell range containing search criteria. The search criteria are identical to the advanced filters; criteria in the same row are connected by AND, and criteria in different rows are connected by OR.