Calc functions similar to database functions

OpenOffice.org 3 Calc Guide

Chapter 13 Calc as a Simple Database

< Previous Section

Next Section >

• Associating a range to a name
• Sorting data
• Filters
• Advanced filters
• Calc functions similar to database functions
• Using formulas to find data
• Address, Indirect, Offset, Index
• Database-specific functions

< Previous Page

Next Page >

Although every Calc function can be used for database manipulation, the functions in Table 6 are more commonly used as such. Some functions' names differ only by the letter appended at the end; AVERAGE and AVERAGEA, for example. Functions that do not end with the letter A operate only on numeric values and cells that contain text or are empty are ignored. The corresponding function whose name ends with the letter A, treats text values as a number with the value of zero; blank cells are still ignored.

Table 6. Functions frequently used as database functions.

Function	Description
AVERAGE	Return the average. Ignore empty cells and cells that contain text.
AVERAGEA	Return the average. The value of text is 0 and empty cells are ignored.
COUNT	Count the number of numeric entries, text entries are ignored.
COUNTA	Count the number of non-empty entries.
COUNTBLANK	Return the number of empty cells.
COUNTIF	Return the number of cells that meet the search criteria.
HLOOKUP	Search for a specific value across the columns in the first row of an array. Return the value from a different row in the same column.
INDEX	Return the content of a cell, specified by row and column number or an optional range name.
INDIRECT	Return the reference specified by a text string.
LOOKUP	Return the contents of a cell either from a one-row or one-column range or from an array.
MATCH	Search an array and return the relative position of the found item.
MAX	Return the maximum numeric value in a list of arguments.
MAXA	Return the maximum numeric value in a list of arguments. The value of text is 0.
MIN	Return the minimum numeric value in a list of arguments.
MINA	Return the minimum numeric value in a list of arguments. The value of text is 0.
MEDIAN	Return the median of a set of numbers.
MODE	Return the most common value in a data set. If there are several values with the same frequency, it returns the smallest value. An error occurs when a value doesn’t appear twice.
OFFSET	Return the value of a cell offset by a certain number of rows and columns from a given reference point.
PRODUCT	Return the product of the cells.
STDEV	Estimate the standard deviation based on a sample.
STDEVA	Estimate the standard deviation based on a sample. The value of text is 0.
STDEVP	Calculate the standard deviation based on the entire population.
STDEVPA	Calculate the standard deviation based on the entire population.
SUBTOTAL	Calculate a specified function based on a subset created using AutoFilters.
SUM	Return the sum of the cells.
SUMIF	Calculate the sum for the cells that meet the search criteria.
VAR	Estimate the variance based on a sample.
VARA	Estimate the variance based on a sample. The value of text is 0.
VARP	Estimate the variance based on the entire population.
VARPA	Estimate the variance based on the entire population. The value of a text is 0.
VLOOKUP	Search for a specific value across the rows in the first column of an array. Return the value from a different column in the same row.

Most of the functions in Table 6 require no explanation, either because they are well understood (SUM, for example) or because if you need to use them then you know what they are (STDEV, for example). Unfortunately, some of the more useful functions are infrequently used because they are not well understood.

Count and sum cells that match conditions: COUNTIF and SUMIF

The COUNTIF and SUMIF functions calculate their values based on search criteria. The search criteria can be a number, expression, text string, or even a regular expression. The search criteria can be contained in a referenced cell or it can be included directly in the function call.

The COUNTIF function counts the number of cells in a range that match specified criteria. The first argument to COUNTIF specifies the range to search and second argument is the search criteria. Table 7 illustrates different search criteria using the COUNTIF function referencing the data shown in Table 1.

The first two arguments for SUMIF serve the same purpose as the arguments for COUNTIF; the range that contains the cells to search and the search criteria. The third and final argument for SUMIF specifies the range to sum. For each cell in the search range that matches the search criteria, the corresponding cell in the sum range is added into the sum.

Table 7. Examples of search criteria for the COUNTIF and SUMIF functions.

Criteria Type	Function	Result	Description
Number	=COUNTIF(B1:C16; 95)	3	Find numeric values of 95.
Text	=COUNTIF(B1:C16; "95")	3	Find numeric or text values of 95.
Expression	=COUNTIF(B1:C16; ">95")	6	Find numeric values greater than 95.
Expression	=COUNTIF(B1:C16; 2*45+5)	3	Find only numeric values of 95.
Regular expression	=COUNTIF(B1:C16; "9.*")	12	Find numbers or text that start with 9.
Reference a cell	=COUNTIF(B1:C16; B3)	3	Find number or number and text depending on the data type in cell B3.
Regular expression	=SUMIF(A1:A16; "B.*"; B1:B16)	227	Sum Column B for names in Col. A starting with the letter B.

Ignore filtered cells using SUBTOTAL

The SUBTOTAL function applies a function (see Table 8) to a range of data, but it ignores cells hidden by a filter and cells that already contain a SUBTOTAL. For example, =SUBTOTAL(2; B2:B16) counts the number of cells in B2:B16 that are not hidden by a filter.

Table 8. Function index for the SUBTOTAL function.

Function index	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

Do not forget that the SUBTOTAL function ignores cells that use the SUBTOTAL function. Say you have a spreadsheet that tracks investments. The retirement investments are grouped together with a subtotal. The same is true of regular investments. You can use a single subtotal that includes the entire range without worrying about the subtotal cells.