Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

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

Table 17. Database functions in a Calc document.

Function Description
DAVERAGE Return the average of all fields that matches 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 a field that matches the search criteria.
DMIN Return the minimum content of a field that matches the search criteria.
DPRODUCT Return the product of the fields that matches 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 matches 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 are 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 1 is placed in Sheet 1 and the filter criteria in Table 4 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 is identical to the advanced filters; criteria in the same row is connected by AND and criteria in different rows is connected by OR.


OpenOffice Calc 3.x Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Creative Commons License Design by Interspire