Calc functions similar to database functions
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.
|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.
|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.
|Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).|