How can we help you?

Product:

Application:

Function |
Description |
---|---|

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

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

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 |

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

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

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 |

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

DSUM(database, field, criteria) |
Calculates the sum of database values matching the specified criteria. |

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

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

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

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

Syntax SUBTOTAL(function_code, range1, [range2, ...]) Description Uses another function (SUMM, PRODUCT, etc.) to calculate the subtotal for a set of numbers. The numeric code of the function to be used to calculate the subtotal. Use codes 1-11 to include manually hidden cells in the count, or 101‑111 to exclude them. Cells hidden using a filter are always excluded.
The first range or array over which to calculate a subtotal. Additional ranges or arrays over which to calculate subtotals. This parameter is optional. Example Calculate the sum of values in cells B2:B5, taking into account the values in the cells hidden manually. Calculate the sum of values in cells B2:B5, without taking into account the values in the cells hidden manually. |

Was this helpful?

Yes

No