List of functions

Use these functions to create formulas for spreadsheet calculations.

Grouped by category
These functions are sorted by category to make it easy to find related functions.
ABS function
ABS returns the absolute value of a number.
ACOS function
ACOS returns the inverse trigonometric cosine of a number.
ACOSH function
ACOSH returns the inverse hyperbolic cosine of a number.
ADDRESS function
ADDRESS returns a cell address (reference) as text, according to the specified row and column numbers. Optionally, you can determine how the address is interpreted. The address can be interpreted as an absolute address (for example, $A$1), a relative address (as A1), or as a mixed form (A$1 or $A1). You can also specify the name of the sheet.
AND function
AND returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns the FALSE value.
ASIN function
ASIN returns the inverse trigonometric sine of a number.
ASINH function
ASINH returns the inverse hyperbolic sine of a number.
ATAN function
ATAN returns the inverse trigonometric tangent of a number.
ATAN2 function
ATAN2 returns the inverse trigonometric tangent of the specified x- and y-coordinates.
ATANH function
ATANH returns the inverse hyperbolic tangent of a number.
AVERAGE function
AVERAGE returns the average of the arguments.
AVERAGEA function
AVERAGEA returns the average of the arguments. The value of a text is 0.
BASE function
BASE converts a positive integer to a specified base into a text from the numbering system. The digits 0-9 and the letters A-Z are used.
CEILING function
CEILING rounds a number up to the nearest multiple of significance.
CHOOSE function
CHOOSE uses an index to return a value from a list of up to 30 values.
CHAR function
CHAR finds and returns a character in Unicode character map by the number. The number is between 1 and 255.
CODE function
CODE returns a numeric code for the first character in a text string.
COLUMN function
COLUMN returns the column number of a cell reference.
COLUMNS function
COLUMNS returns the column number of a cell reference.
COMBIN function
COMBIN returns the number of combinations for a given number of items.
CONCATENATE function
CONCATENATE combines several text strings into one string.
CONVERT function
CONVERT converts a value from one unit of measurement to a different unit of measurement.
COS function
COS returns the cosine of the given angle.
COSH function
COSH returns the hyperbolic cosine of a number.
COUNT function
COUNT counts how many numbers are in the list of arguments. Text entries are ignored.
COUNTA function
COUNTA counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0. If an argument is an array or reference, empty cells within the array or reference are ignored.
COUNTBLANK function
COUNTBLANK returns the number of empty cells.
COUNTIF function
COUNTIF returns the number of cells that meet criteria within a cell range.
DATE function
DATE converts a date written as year, month, day to an internal serial number and displays it in the formatting of the cell.
DATEDIF function
Returns the number of years, months, or days between two dates.
DATEVALUE function
DATEVALUE returns the internal date number for text in double quotation marks when the text format indicates a date. The internal number is returned as a natural number, resulting from the date system used by IBM® Connections Docs to calculate dates.
DAY function
DAY returns the day of a given date value. The day is returned as a value 1 - 31. You can also enter a negative date or time value.
DAYS function
DAYS calculates the difference between two date values. The result is an integer and returns the number of days between the two days.
DAYS360 function
DAYS360 returns the difference between two dates based on the 360-day year that is used in interest calculations. The result is an integer.
DEGREES function
DEGREES converts radians into degrees.
DOLLAR function
DOLLAR converts a number to an amount in the currency format, rounded to a specified decimal place. You set the currency format in your system settings.
ERRORTYPE function
ERRORTYPE returns the number corresponding to an error value occurring in a different cell. With the aid of this number, you can create an error message. If an error occurs, the function returns a logical or numerical value.
EVEN function
EVEN rounds a positive number up to the next even integer and a negative number down to the next even integer.
EXP function
EXP returns e raised to the power of a number. The constant e has a value of approximately 2.71828182845904.
EXACT function
EXACT compares two text strings and returns TRUE if they are identical. This function is case-sensitive.
FACT function
FACT returns the factorial of a number. Factorials are calculated as 1*2*3*4* ... * .
FALSE function
FALSE returns the logical value FALSE. The FALSE() function does not require any arguments, and always returns the logical value FALSE.
FIND function
FIND locates a text string within another text string and returns the starting character position of the first text string from the second text string. (case-sensitive).
FIXED function
FIXED specifies that a number be displayed with a fixed number of decimal places, with or without a thousands separator. Use this function to apply a uniform format to a column of numbers.
FLOOR function
FLOOR rounds a number down to the nearest multiple of significance.
FORMULA function
FORMULA displays the formula of a formula cell at any position. The formula is returned as a string in the reference position. If no formula cell can be found, or if the presented argument is not a reference, the error value #N/A is set.
FREQUENCY function
FREQUENCY indicates the frequency distribution in a set of values.
HLOOKUP function
HLOOKUP searches for a value and reference to the cells under the selected area. This function verifies if the first row of an array contains a certain value. The function returns the value in a row of the array, which is named in the index, in the same column.
HOUR function
HOUR returns the hour for a given time value. The hour is returned as an integer 0 - 23.
HYPERLINK function
HYPERLINK opens a hyperlink when you click the cell that contains the hyperlink. If you use the optional celltext argument, the formula locates the URL, and then the text or number is displayed.
IF function
IF specifies a logical test to be performed.
INDEX function
INDEX returns the contents of a cell, specified by row and column number or an optional range name.
INDIRECT function
INDIRECT returns the reference specified by a text string. Use this function if you want a formula to always refer to a particular cell address, regardless of the contents of the cell after rows or cells have been moved or deleted. This function can also be used to return the area of a corresponding string.
INT function
Rounds a number down to the nearest integer. Negative numbers round down to the integer less than the number.
ISBLANK function
ISBLANK returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty. A cell with a formula inside is not empty. If an error occurs, the function returns a logical or numeric value.
ISERR function
ISERR returns TRUE if the value refers to any error value except #N/A. You can use this function to control error values in cells. If an error occurs, the function returns a logical or numeric value.
ISERROR function
ISERROR tests if the cells contain general error values. ISERROR recognizes the #N/A error value. If the cell being referenced contains any error such as #N/A, #DIV/0 or #Err 522, it returns TRUE.
ISEVEN function
ISEVEN returns TRUE if the value is an even integer, or FALSE if the value is an odd integer.
ISFORMULA function
ISFORMULA returns TRUE if a cell is a formula cell. If an error occurs, the function returns a logical or numeric value.
ISLOGICAL function
ISLOGICAL returns TRUE if the cell contains a logical number format. The function is used to check for both TRUE and FALSE values in cells. If an error occurs, the function returns a logical or numeric value.
ISNA function
ISNA returns TRUE if a cell contains the #N/A (value not available) error value. If an error occurs, the function returns a logical or numeric value.
ISNONTEXT function
ISNONTEXT tests if the cell contents are text or numbers, and returns FALSE if the contents are text. If an error occurs, the function returns a logical or numeric value.
ISNUMBER function
ISNUMBER returns TRUE if the value refers to a number. If an error occurs, the function returns a logical or numerical value.
ISODD function
ISODD returns TRUE if the value is an odd integer, or FALSE if the number is an even integer.
ISREF function
ISREF tests if the content of one or several cells is a reference. This function verifies the type of references in a cell or a range of cells. If an error occurs, the function returns a logical or numerical value.
ISTEXT function
ISTEXT returns TRUE if the cell contents refer to text. If an error occurs, the function returns a logical or numeric value.
LARGE function
LARGE returns the nth largest value from a set of values.
LEFT function
LEFT returns the first character or characters in a text string.
LEN function
LEN returns the length of a string including spaces.
LENB function
LENB returns the number of bytes used to represent the characters in a text string.
LN function
LN returns the natural logarithm based on the constant e of a number. The constant e has a value of approximately 2.71828182845904.
LOG function
LOG returns the logarithm of a number to the specified base.
LOG10 function
LOG10 returns the base-10 logarithm of a number.
LOOKUP function
LOOKUP returns the contents of a cell either from a one-row or one-column range or from an array.
LOWER function
LOWER converts all uppercase letters in a text string to lowercase.
MATCH function
MATCH returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.
MAX function
MAX returns the maximum value in a list of arguments.
MEDIAN function
MEDIAN returns the median (middle) value in the list.
MID function
Returns the specified portion of a string expression
MIN function
MIN returns the minimum value in a list of arguments.
MINUTE function
MINUTE calculates the minute for an internal time value. The minute is returned as a number 0 - 59.
MMULT function
MMULT calculates the array product of two arrays.
MOD function
MOD returns the remainder when one integer is divided by another.
MODE function
MODE returns 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 does not appear twice.
MONTH function
MONTH returns the month for the given date value. The month is returned as a number 1 - 12.
N function
N returns the number 1 if the parameter is TRUE. N returns the parameter if the parameter is a number. N returns the number 0 for other parameters. If an error occurs, the function returns a logical or numerical value.
NA function
NA returns the error value #N/A.
NETWORKDAYS function
NETWORKDAYS returns the number of workdays between the start date and the end date. Optionally, a list of holidays can be deducted.
NOT function
NOT inverts and returns the logical value.
NOW function
NOW returns the computer system date and time. The value is updated when you recalculate the document or each time that a cell value is modified.
ODD function
ODD rounds a positive number up to the nearest odd integer and a negative number down to the nearest odd integer.
OFFSET function
OFFSET returns the value of a cell the location of which is offset by a certain number of rows and columns.
OR function
OR returns TRUE if at least one argument is TRUE. This function returns the value FALSE only when all the arguments have the logical value FALSE.
PI function
PI returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places.
POWER function
POWER® returns a number raised to a power.
PRODUCT function
PRODUCT multiplies all the numbers given as arguments and returns the product.
PROPER function
PROPER capitalizes the first letter in all words of a text string.
RADIANS function
RADIANS converts degrees to radians.
RAND function
RAND returns a random number between 0 and 1.
RANDBETWEEN function
RANDBETWEEN returns a random integer number in a specified range.
RANK function
RANK returns the rank of a number in a sample.
REPLACE function
REPLACE replaces part of a text string with a different text string. This function can be used to replace both characters and numbers, which are automatically converted to text. The result of the function is always displayed as text.
REPT function
REPT repeats a character string by the given number of copies. The result can be a maximum of 255 characters.
RIGHT function
RIGHT defines the last character or characters in a text string.
RIGHTB function
In languages that use the double-byte character set, RIGHTB returns the specified number of bytes at the end of a text string.
ROMAN function
ROMAN Converts an arabic numeral to roman, as text.
ROUND function
ROUND rounds a number to a number of decimal places. This function rounds to the nearest number. See ROUNDDOWN and ROUNDUP for alternatives.
ROUNDDOWN function
ROUNDDOWN rounds a number down, toward zero, to a certain precision. See ROUNDUP and ROUND for alternatives.
ROUNDUP function
ROUNDUP rounds a number up, away from zero, to a certain precision. See ROUNDDOWN and ROUND for alternatives.
ROW function
ROW returns the row number of a cell reference.
ROWS function
ROWS returns the number of rows in a reference or array.
SEARCH function
SEARCH returns the position of a text segment within a character string. You can set the start of the search as an option. The search text can be a number or any sequence of characters. The search is not case-sensitive.
SECOND function
SECOND returns the second for the given time value. The second is returned as an integer 0 - 59.
SHEET function
SHEET returns the sheet number of a reference or a string representing a sheet name. If you do not enter any parameters, the result is the sheet number of the spreadsheet containing the formula.
SIN function
SIN returns the sine of the given angle.
SINH function
SINH returns the hyperbolic sine of a number.
SMALL function
SMALL returns the nth smallest value from a set of values.
SQRT function
SQRT returns the positive square root of a number.
STDEV function
STDEV estimates the standard deviation based on a sample.
STDEVP function
STDEVP calculates the standard deviation based on the entire population.
SUBSTITUTE function
SUBSTITUTE replaces old text with new text in a string.
SUBTOTAL function
SUBTOTAL calculates subtotals. If a range already contains subtotals, these are not used for further calculations.
SUM function
SUM adds all the numbers in a range of cells.
SUMIF function
SUMIF adds the cells specified by the given criteria. This function is used to browse a range when you search for a certain value.
SUMPRODUCT function
SUMPRODUCT multiplies corresponding elements in the given arrays, and returns the sum of those products.
T function
T converts a number to a blank text string.
TEXT function
TEXT converts a number into text according to a given format.
TIME function
TIME returns the current time value from values for hours, minutes, and seconds. This function can be used to convert a time based on these three elements to a decimal time value.
TIMEVALUE function
TIMEVALUE returns the internal time number from the text enclosed by double quotation marks, indicating a possible time entry format. The internal number indicated as a decimal is the result of the date system used in IBM Connections Docs to calculate date entries.
TODAY function
TODAY returns the current computer system date. The value is updated when you reopen the document or modify the values of the document.
TRIM function
TRIM removes spaces that are in front of a string, or aligns cell contents to the left.
TRUE function
TRUE returns the logical value TRUE. The TRUE() function does not require any arguments, and always returns the logical value TRUE.
TRUNC function
TRUNC truncates a number by removing decimal places.
TYPE function
TYPE returns the type of value. If an error occurs, the function returns a logical or numeric value.
UPPER function
UPPER converts the string specified in the text field to uppercase.
VALUE function
VALUE converts a text string into a number.
VLOOKUP function
VLOOKUP uses a vertical search with reference to adjacent cells.
VAR function
VAR estimates the variance based on a sample.
VARA function
VARA estimates the variance based on a sample. In addition to numbers, text and logical values such as TRUE and FALSE are included.
VARP function
VARP calculates the variance based on the entire population.
VARPA function
VARPA calculates the variance based on the entire population. In addition to numbers, text and logical values such as TRUE and FALSE are included.
WEEKDAY function
WEEKDAY returns the day of the week for the given date value. The day is returned as an integer between 1 and 7. The day of the week on which numbering begins depends on the type.
WEEKNUM function
WEEKNUM calculates the week number of the year for the internal date value.
WORKDAY function
WORKDAY returns a date number that can be formatted as a date. You then see the date of a day that is a certain number of workdays away from the start date.
YEAR function
YEAR returns the year as a number according to the internal calculation rules.

Parent topic: Using formulas in calculations

Related concepts:

Relative and absolute references

About formulas and functions