List of functions

Financial

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

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

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.

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.

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.

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.

Date and time

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.

DAY(date)

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

EDATE(start_date, months)

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

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.

HOUR(time)

Extracts the hour component from a given time value.

ISOWEEKNUM(date)

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

MINUTE(time)

Extracts the minute component from a given time value.

MONTH(date)

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

NOW()

Returns the current date and time as a timestamp value.

TODAY()

Returns the current date in number format.

YEAR(date)

Returns the year specified by a given date.

YEARFRAC(start_date, end_date, [calculation_method])

Calculates the number of years, including fractional years, between the dates as a decimal fraction.

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.

DEGREES(angle)

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

EXP(exponent)

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

FACTDOUBLE(value)

Returns the double factorial of a number.

INT(value)

Rounds a number down to the nearest integer.

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.

PI()

Returns the pi value rounded to 14 decimal places.

POWER(base, exponent)

Returns the result of raising a number to a power.

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

Returns the product of supplied arguments.

QUOTIENT(dividend, divisor)

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

RAND()

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

ROUND(value, places)

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

ROUNDDOWN(value, places)

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

ROUNDUP(value, places)

Rounds a number up to a certain number of digits.

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.

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.

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.

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.

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

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

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

Returns the sum of the squares of numbers.

TANH(value)

Returns the hyperbolic tangent of a number.

Statistical

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

Returns the average of numbers in a dataset, ignoring text.

AVERAGEIF(range, criteria, [average_range])

Returns the arithmetic mean of the values that meet the given criteria.

CORREL(data_X, data_Y)

Returns the Pearson correlation coefficient between two data sets. It shows how how much a change in the values of set X affects the values of set Y.

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

Returns the count of numeric values in a supplied dataset.

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.

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.

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.

FISHER(value)

Returns the Fisher transformation for a given value.

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.

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.

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

Returns the middle of a set of numbers.

For an odd number of elements, the median is the central number in terms of value, and for an even number, it is the arithmetic mean of two central ones. For example, for the set 2, 10, 1, the median is 2, and for the set 2, 10, 1, 15, it is 6 (arithmetic mean of 2 and 10).

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

Retrieves the minimum number from a numeric dataset.

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

Returns the most frequent value in a group of numbers.

For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

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.

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.

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

Calculates the standard deviation based on a sample.

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.

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.

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.

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

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

COLUMN([cell_reference])

Returns the column number of a specified cell.

COLUMNS(range)

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

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.

HYPERLINK(url, [link_label])

Creates a link in a cell of a spreadsheet.

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

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

INDIRECT(cell_reference_as_string, [is_A1_notation])

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

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.

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 (that is, a position number of the item in an array), not the item itself.

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.

ROW([cell_reference])

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

ROWS(range)

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

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.

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.

CLEAN(text)

Removes non-printable characters from a given string.

CODE(string)

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

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

Joins separate entries into a single string.

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.

EXACT(string1, string2)

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

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.

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.

LEFT(string, [number_of_characters])

Returns a substring from the beginning of a specified string.

LEFTB(string, [number_of_characters])

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

LEN(text)

Returns the length of the specified string.

LENB(text)

Returns the count of characters in the supplied string.

LOWER(text)

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

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.

PROPER(text_to_capitalize)

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

RIGHT(string, [number_of_characters])

Returns the rightmost character or characters of a given string.

RIGHTB(string, [number_of_characters])

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

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.

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

Replaces a specific string with another one in a text.

TEXT(value, format)

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

TRIM(text)

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

UNICODE(string)

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

UPPER(text)

Converts all lowercase letters in a specified string to uppercase.

VALUE(text)

Converts a value entered in text format into a number.

Logic

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

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

FALSE()

Returns the FALSE logical value.

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.

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

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

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

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

TRUE()

Returns the TRUE logical value.

Information

CELL(info_type, [reference])

Returns the requested data about a particular cell.

INFO(info_type)

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

ISBLANK(value)

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

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.

ISEVEN(value)

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

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.

ISNUMBER(value)

Checks whether a given value is a number.

ISODD(value)

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

ISREF(value)

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

ISTEXT(value)

Checks whether a given value is textual.

NA()

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

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.