Documentation/How Tos/Calc: Database functions

Introduction

In the OpenOffice.org spreadsheet 'Calc', a 'database' is simply a table of values, and has nothing to do with the more complex OpenOffice.org database 'Base'. The 'database' functions in Calc are quite simple to use.

A Calc 'database' table might look like this:

The first row of the 'database' table has headings (Name, Grade, Age ... ), and each subsequent row contains the data values.

The 'criteria', which are used to select rows from the 'database', are entered in another table:

The first row of this table has headings. Subsequent rows specify the criteria: for example, using the criteria table above will match those children whose distance to school is more than 600.

The Calc 'database' functions all have a similar form. As a simple first example, using the tables above:

DCOUNT(A1:E10; 0; A13:E14)

returns 5, the number of children whose “Distance to School” is more than 600.

A1:E10 is the database table and A13:E14 is the criteria table.

There are other 'database' functions to return a sum, an average, a standard deviation, and so on.

Criteria

The criteria table may be placed anywhere on the spreadsheet, but it is often wise to have it near the database table.

The criteria table headings must exactly match headings in the database table, but they can appear in any order, and can appear more than once:

All criteria on a row must be satisfied for the row to be satisfied, so in this example above we find rows which have:

“Distance to School” more than 600    AND

“Age” greater than 8    AND

“Age” less than or equal to 10

in other words '9 and 10 year olds who travel more than 600 to school'.

If the criteria table has more than one row of criteria, any one satisfied row means the overall criteria table is satisfied:

In this example the test is:

(“Distance to School” more than 600    AND

“Age” greater than 8    AND

“Age” less than or equal to 10)

OR

( “Age” less than or equal to 8)

In other words we find rows where the children are either 'aged 9 or 10 with 600 or more to travel to school', or 'aged 8 or under'.

The condition which is entered in a cell of a criteria table (eg >4 ) is simply text and has the following form:

comparator value where

comparator is one of >, <, >=, <=, =, <> (if comparator is omitted = is assumed );

value is the value (number or text) to be compared.

For example:

the condition “>4” tests if the contents of cells are greater than 4.

the condition “<lamp” tests if the contents of cells come alphabetically before lamp (so lady and ant meet the condition, but late and zebra do not).

the condition “lamp” tests if the contents of cells are lamp - but Calc has a number of settings which define the exact behaviour - please read the following section: