Database Functions in Calc

From NeoWiki

---

Revision as of 17:45, 25 May 2007

Database Functions in Calc

In NeoOffice Calc you can create a simple data base. Of course, you must not confuse this possibility with the creation of a database in NeoOffice Base. Nevertheless it allows you to easily have access to information on the data of a spreadsheet. Every record must be entered on a single line. The following example illustrates this feature.

Setting up the Spreadsheet

We calculate the notes averages of the candidates for an examination which consists of three tests. We note the age of the candidate in the last column.

• The range B10:H18 defines the database. Each line constitutes a record and the different headings are set up in columns.
• The range B3:H4 defines the search criteria range. It duplicates the columns of the data range. Perhaps you will have to add columns in order to use criteria connected by the logical conjunction AND, and lines to use the disjunction OR, as in the examples below.
  

The data and search criteria ranges can be placed on different sheets.

Functions Parameters

Each function depends on three parameters

1. The cell range defining the database, in this example B10:I18
2. The search field defining the area where the search is done. The 0 value means that the search is done in the whole data range. To reference a column by means of the column header name, place the header name between quotes.
3. The criteria range in which are placed the search criteria, here the range B3:I5

Examples of Functions

You can enter these functions by using the wizard: click on the function icon next to the formula bar and choose databases in the Database option in the Category drop down menu.

• The DCOUNT(database;field;search_criteria) function returns the number of records which match the specified search criteria.
  
  • Examples:

Cell B7 shows the number of candidates whose average is greater than or equal to 10, less than 13, AND age is equal to 19, that is DCOUNT(B10:I18;0;B3:I4)=1

Cell B7 shows the number of candidates whose average is greater than or equal to 10, OR age is less than or equal to 17, that is DCOUNT(B10:I18;0;B3:I5)=7

• The DAVERAGE(database;field;search_criteria) function returns the arithmetic mean of the values of cells in the whole database which match the specified search criteria.
  • Example :

Cell B7 shows the mean of the notes got in Test 3 by the candidates whose average (in column G) is greater than or equal to 10 OR age is less than or equal to 17, that is DAVERAGE(B10:I18;"Test3";B3:I5)=11,57

• The DMIN(database;field;search_criteria) function returns the minimum value of the cells of a field which match the specified search criteria. In the same manner the DMAX function returns the maximum value of those cells.
  • Example:

Cell B7 shows the minimum note got in Test 1 by the candidates whose age is equal to 17, DMIN(B10:I18;"Test1";B3:I4)=8.

• The DGET(database;field;search_criteria) function returns the contents of the cell which matches the specified search criteria.
  • Examples:

Cell B7 shows the name of the candidate who got 9 in Test 2, DGET(B10:I18;"Name";B3:I4)=FISHER.. If no value is found the function returns VALUE!, if several values are found the function returns Err :502.

You will find the list of the available functions as well as examples in NeoOffice Help, by going to the Help > NeoOffice Help and NeoOffice Calc menu. Type "functions" in the search field, then double-click on "databases".