| 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. |