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 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.


OpenOffice Calc 3.x Guide
Previous Page Home Next Page

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