Appendix A. List of functions

Financial

IRR(cashflow_amounts, [rate_guess])

Calculates the internal rate of return on an investment based on regular cash flows which include both payments and incomes.

COUPPCD(settlement, maturity, frequency, [day_count_convention])

Returns the numeric date value of the last coupon date before the settlement date of a security.

PMT(rate, number_of_payments, present_value, [future_value], [end_or_beginning])

Returns the periodic payment for an annuity based on constant periodic payments and a constant interest rate.

PV(rate, number_of_periods, payment_per_period, [future_value], [end_or_beginning])

Returns the present value of an investment based on periodic payments and a constant interest rate.

DOLLARDE(fractional_price, unit)

Converts a dollar value expressed as a fraction into a decimal number.

DOLLARFR(decimal_price, unit)

Converts a dollar value expressed as a decimal number into a fractional number.

NPV(discount, cashflow1,
[cashflow2, ...])

Returns the numeric value which is the net present value of an investment depending on a series of periodic cash flows and a discount rate.

Date and time

YEAR(date)

Returns the year specified by a given date.

DATE(year, month, day)

Creates a date from the specified year, month, and day.

DATEVALUE(date_string)

Transforms the provided date string in a known format to a numeric date value.

EDATE(start_date; months)

Returns the numeric value of a date which falls a specified number of months before or after another date.

DAY(date)

Returns the day of the month that a specific date falls on, in numeric format.

EOMONTH(start_date, months)

Returns the date representing the last day of the month which falls a specified number of months before or after another date.

MONTH(date)

Returns the month of the year the specific date falls in, in numeric format.

MINUTE(time)

Extracts the minute component from a given time value.

ISOWEEKNUM(date)

Returns the ISO week number (from 1 to 54) for a given date.

TODAY()

Returns the current date in number format.

NOW()

Returns the current date and time as a timestamp value.

HOUR(time)

Extracts the hour component from a given time value.

Math and trigonometry

ABS(value)

Returns the absolute value of a number.

ACOS(value)

Calculates the arccosine, that is the inverse cosine of a given number, and returns an angle expressed in radians.

ACOSH(value)

Calculates the inverse hyperbolic cosine for a number.

ACOT(value)

Calculates the arccotangent, that is the inverse cotangent of a given number, and returns an angle, in radians, between 0 (zero) and π.

ASIN(value)

Calculates the arcsine, that is the inverse sine of a given number, and returns an angle expressed in radians.

ATAN(value)

Calculates the arctangent, that is the inverse tangent of a given number, and returns an angle, in radians, between -Pi/2 and Pi/2.

ATAN2(x, y)

Returns the arctangent of an angle between the x-axis and a segment starting from the origin (0,0) and ending at a specific point defined by the coordinate pair
(x,y). The resulted value is expressed in radians.

ATANH(value)

Calculates the inverse hyperbolic tangent for a number.

COSH(angle)

Calculates the hyperbolic cosine for a real number.

COTH(angle)

Calculates the hyperbolic cotangent of a number.

CSC(angle)

Calculates the cosecant of a number and returns the result in radians.

CSCH(angle)

Calculates the hyperbolic cosecant of a number and returns the result in radians.

EXP(exponent)

Returns the e number (~2.718) raised to a supplied power.

LN(value)

Returns the logarithm of a number to base e (~2.718) which is known as the natural logarithm.

LOG(value, [base])

Returns the logarithm of a number with respect to a given base.

LOG10(value)

Returns the base-10 logarithm of a given number.

SEC(angle)

Returns the secant of an angle, in radians.

SECH(angle)

Returns the hyperbolic secant of an angle.

SINH(value)

Returns the hyperbolic sine of a number.

TANH(value)

Returns the hyperbolic tangent of a number.

DEGREES(angle)

Converts an angle value in radians to the equivalent value in degrees.

FACTDOUBLE(value)

Returns the double factorial of a number.

SQRT(value)

Returns the positive square root of a number.

SQRTPI(value)

Returns the square root of the pi constant (3.14159265358979) multiplied by a given number.

ROUND(value, places)

Rounds a number to a certain number of digits according to standard rounding rules.

ROUNDUP(value, places)

Rounds a number up to a certain number of digits.

ROUNDDOWN(value, places)

Rounds a number down to a certain number of decimal places.

PI()

Returns the pi value rounded to 14 decimal places.

PRODUCT(factor1, [factor2, ...])

Returns the product of supplied arguments.

SUBTOTAL(function_code, range1, [range2, ...])

Uses another function (SUMM, PRODUCT, etc.) to calculate the subtotal for a set of numbers.

For more details, see the SUBTOTAL function section.

RAND()

Returns a random decimal number between 0 and 1, excluding the boundary values.

POWER(base, exponent)

Returns the result of raising a number to a power.

SUM(value 1, [value 2; ...])

Returns the result of adding series of numbers, and/or cells.

SUMIF(range, criterion, [sum_range])

Returns the sum of a range of values matching a certain criterion.

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Returns the sum of a range of values matching multiple criteria.

SUMSQ(value1, [value2, ...])

Returns the sum of the squares of numbers.

SUMPRODUCT(array1, [array2, ...])

Multiplies ranges or arrays together and returns the sum of products.

INT(value)

Rounds a number down to the nearest integer.

QUOTIENT(dividend, divisor)

Divides a number by another one and returns a value without a reminder.

Statistical

F.DIST(x, freedom_degrees1, freedom_degrees2, cumulative)

Calculates the left-tailed F probability distribution (degree of diversity) for two data sets with a given x.

F.DIST.RT(x, freedom_degrees1, freedom_degrees2)

Calculates the right-tailed F probability distribution (degree of diversity) for two data sets with a given x.

VAR(value1, [value2, ...])

Calculates the variance based on a given sample.

VARA(value1, [value2, ...])

Calculates the variance based on a sample, treating text values as 0 (zero).

VARPA(value1, [value2, ...])

Analyzes data in a range and returns the variance of that data range. Text and logical values are considered.

LINEST(known_data_y, [known_data_x], [calculate_b], [stats])

Calculates parameters of the linear trend based on the least-squares method.

MAX(value1, [value2, ...])

Retrieves the maximum number from a numeric dataset.

MIN(value1, [value2, ...])

Retrieves the minimum number from a numeric dataset.

LARGE(data, n)

Arranges a given numeric dataset in descending order and returns a value depending on its relative position where the largest value takes the first position.

SMALL(data, n)

Arranges a given numeric dataset in ascending order and returns a value based on its relative position where the smallest value takes the first position.

NORM.DIST(x, mean, standard_deviation, cumulative)

Returns the value of the normal distribution function (or normal integral distribution function) for a given value of x, as well as the mean and standard deviation

RANK.EQ(value, data, [is_ascending])

Arranges numeric values in ascending or descending order and determines the rank position of a specified value in a dataset. For duplicate values, a rank of the first value is assigned.

AVERAGE(value1, [value2, ...])

Returns the average of numbers in a dataset, ignoring text

STDEV.S(value1, [value2, ...])

Calculates the standard deviation based on a sample.

COUNT(value1, [value2, ...])

Returns the count of numeric values in a supplied dataset.

COUNTIF(range, criterion)

Returns the number of times cell values meet a certain criterion.

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Returns the number of cells that meet multiple criteria.

COUNTA(value1, [value2, ...])

Looks through a given dataset and returns the number of cells containing any type of information.

COUNTBLANK(range)

Returns the count of empty and blank cells in a given range or array.

FISHER(value)

Returns the Fisher transformation for a given value.

Lookup and reference

ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

Returns the cell location in a spreadsheet based on specified row and column numbers.

VLOOKUP(search_key, range, index, [match_type])

Searches down the first column of a range for the first matching key and returns the corresponding value from another column.

CHOOSE(index, choice1, [choice2, ...])

Retrieves the value from a dataset based on its position number.

HYPERLINK(url, [link_label])

Creates a link in a cell of a spreadsheet.

HLOOKUP(search_key, range, index, [match_type])

Evaluates the first row of a range for a search value and returns a value which is located in a different row but in the same column.

INDIRECT(cell_reference_as_string, [is_A1_notation])

Returns the content of the reference which can be a cell or a string.

INDEX(reference, [row], [column])

Returns the element of an array or range, specified by its row and column position.

AREAS(reference)

Returns the number of areas in a reference or a range. An area is considered as a separate cell reference or a range of cells.

MATCH(search_key, range, [search_type])

Allows finding a specified item in the defined range of cells by a desired search type. The function returns the item index (i.e.: a position number of the item in an array), not the item itself.

LOOKUP(search_key, search_range, [result_range]) (1)

LOOKUP(search_key, search_result_array) (2)

Looks through a row or a column for a key and returns the cell value in a result range located in the same position as a search row or column.

Looks through multiple rows or columns for a key and returns a value of the cell located in the correspondent position of the same search rows or columns. To search for a key across a single row or column, specify the result_range value as the third argument.

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

Returns a cell or a range of cells shifted
by a specified number of rows and columns from the initial cell reference.

COLUMN([cell_reference])

Returns the column number of a specified cell.

ROW([cell_reference])

Returns the number of the row where a cell is specified.

COLUMNS(range)

Returns the count of columns in a range or an array.

ROWS(range)

Returns the count of rows in a range or an array.

Database

DSUM(database, field, criteria)

Calculates the sum of database values matching the specified criteria.

Text

ASC(string)

Converts a text encoded in a double byte character set (DBCS) to a text in a single byte character set (SBCS). In the spreadsheet editor, the function is only used for the backward compatibility with documents containing text encoded in DBCS.

DBCS(string)

Converts a text from ASCII to DBCS and backwards. In the spreadsheet editor, the function is only used for the backward compatibility with documents containing text encoded in either ASCII or DBCS.

JIS(string)

Converts a text from ASCII to DBCS and backwards. In the spreadsheet editor, the function is only used for the backward compatibility with documents containing text encoded in either ASCII or DBCS.

UNICODE(string)

Returns the Unicode numeric value of the first character in a given string.

LENB(text)

Returns the count of characters in the supplied string.

LEN(text)

Returns the length of the specified string.

CODE(string)

Returns the Unicode numeric value of the first character in a given string.

LEFTB(string, [number_of_characters])

Returns a substring from the beginning of a specified string based on the supplied number of characters.

LEFT(string, [number_of_characters])

Returns a substring from the beginning of a specified string.

FIND(search_for, text_to_search, [starting_at])

Returns the position at which a string is first found within text (case sensitive).

FINDB(search_for, text_to_search, [starting_at])

Returns the position at which a string is first found within text counting double letters as two characters.

CLEAN(text)

Removes non-printable characters from a given string.

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Replaces a specific string with another one in a text.

SEARCH(search_for, text_to_search, [starting_at])

Returns the starting position of a string within a text.

SEARCHB(search_for, text_to_search, [starting_at])

Returns the position at which a string is first found within text counting double letters as two characters.

RIGHTB(string, [number_of_characters])

Returns the rightmost character or characters of a given string based on the specified number of bites.

RIGHT(string, [number_of_characters])

Returns the rightmost character or characters of a given string.

UPPER(text)

Converts all lowercase letters in a specified string to uppercase.

PROPER(text_to_capitalize)

Capitalizes the first letter of every word in a specified string.

MID(string, starting_at, extract_length)

Returns a segment of a string.

MIDB(string, starting_at, extract_length)

Returns a segment of a string based on the specified number of bites.

TRIM(text)

Removes all spaces in the specified string, except the single spaces between words.

EXACT(string1, string2)

Checks whether two strings are identical and, if so, returns TRUE.

LOWER(text)

Finds uppercase letters in a given string to convert them to lowercase.

CONCATENATE(string1, [string2, ...])

Joins separate entries into a single string.

TEXT(value, format)

Converts a numeric value into a text value according to a specified format.

Logic

IF(logical_expression, value_if_true, [value_if_false])

Returns one value if the result of a logical test is TRUE and another if it is FALSE.

IFERROR(value, value_if_error)

Returns the first argument if it is not an error value, and the second argument if a given value is an error.

IFNA(value, value_if_na)

Returns the first argument if it is not #N/A, otherwise, returns the second argument.

AND(logical_expression1, [logical_expression2, ...])

Returns TRUE if all of the supplied tests are successful, and FALSE if at least one fails.

OR(logical_expression1, [logical_expression2, ...])

Returns TRUE if any of the specified logical tests is successful, and FALSE if all logical tests fail.

TRUE()

Returns the TRUE logical value.

FALSE()

Returns the FALSE logical value.

SWITCH(expression, value1, result1, [value2, result2, ...], [default])

Evaluates a list of expressions and returns the value corresponding to the first expression in the list.

Information

ISLOGICAL(value)

Checks whether a value is either FALSE or TRUE and returns TRUE on any of them.

ISNA(value)

Checks whether a value is the #N/A error and, if so, returns TRUE.

ISODD(value)

Checks whether a value is an odd number and, if so, returns TRUE.

ISERR(value)

Returns TRUE if a given value is an error other than #N/A.

ISERROR(value)

Returns TRUE if a given value is an error.

ISBLANK(value)

Checks whether a referenced cell is empty, and if so, returns the TRUE logical value.

ISREF(value)

Returns TRUE if a supplied value is a valid cell reference.

ISTEXT(value)

Checks whether a given value is textual.

ISEVEN(value)

Checks whether a value is an even number and, if so, returns TRUE.

ISNUMBER(value)

Checks whether a given value is a number.

INFO(info_type)

Returns the requested information relating to the user’s operating system and the data processing environment.

NA()

Returns the #N/A error which stands for "value is not available".

CELL(info_type, [reference])

Returns the requested data about a particular cell.

Engineering

COMPLEX(real_number, imaginary_number, [suffix])

Creates a complex number based on specified real and imaginary components.

IMABS(complex_number)

Returns the absolute value of a complex number.