Function |
Description |
Database |
|
DSUM(database, field, criteria) |
Calculates the sum of database values matching the specified criteria. |
Time and date |
|
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. |
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. |
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. |
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. |
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. |
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 (that is 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. |
Math and trig |
|
ABS(value) |
Returns the absolute value of a number. The result of calculation is always a positive 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(value) |
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 to calculate a subtotal for a vertical range of cells. |
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 normal distribution function for the specified values of the arithmetic mean and the 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. |
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 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 starting position of a string within a 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 extra spaces between words, as well as spaces at the beginning and at the end of a line. |
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. |