Date Functions
Function | Description | Syntax | Example |
DATE | Converts a year, month, and day into a date. | DATE(year, month, day) | DATE(1969, 7, 20) |
Calculates the number of days, months, or years between two dates. | DATEDIF(start_date, end_date, unit) | DATEDIF("1969-7-16", "1969-7-24", "Y") | |
Converts a provided date string in a known format to a date value. | DATEVALUE(date_string) | DATEVALUE("1969-7-20") | |
DAY | Returns the day of the month that a specific date falls on, in numeric format. | DAY(date) | DAY("1969-7-20") |
DAYS | Returns the number of days between two dates. | DAYS(end_date, start_date) | DAYS("1969-7-24", "1969-7-16") |
Returns the difference between two days based on the 360 day year used in some financial interest calculations. | DAYS360(start_date, end_date, [method]) | DAYS360("1969-7-16", "1969-7-24", 1) | |
Returns a date a specified number of months before or after another date. | EDATE(start_date, months) | EDATE("7/20/1969", 1) | |
Returns a date on the last day of a month that falls a specified number of months before or after another date. | EOMONTH(start_date, months) | EOMONTH("7/20/1969", 1) | |
EPOCHTODATE | Converts a Unix epoch timestamp in seconds, milliseconds or microseconds to a datetime in UTC. | EPOCHTODATE(timestamp, [time_unit]) | EPOCHTODATE(1655906568893, 2) |
HOUR | Returns the hour component of a specific time, in numeric format. | HOUR(time) | HOUR("11:40:59") |
ISOWEEKNUM | Returns a number representing the ISO week of the year where the provided date falls. | ISOWEEKNUM(date) | ISOWEEKNUM("1969-7-20") |
Returns the minute component of a specific time, in numeric format. | MINUTE(time) | MINUTE("11:40:59") | |
Returns the month of the year a specific date falls in, in numeric format. | MONTH(date) | MONTH("1969-7-20") | |
Returns the number of net working days between two provided days. | NETWORKDAYS(start_date, end_date, [holidays]) | NETWORKDAYS("1969-7-16", "1969-7-24", A1:A10) | |
Returns the number of net working days between two provided days excluding specified weekend days and holidays. | NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) | NETWORKDAYS.INTL(DATE(1969, 7, 16), DATE(1969, 7, 24), 1, A1:A10) | |
NOW | Returns the current date and time as a date value. | NOW() | NOW() |
Returns the second component of a specific time, in numeric format. | SECOND(time) | SECOND("11:40:59") | |
TIME | Converts a provided hour, minute, and second into a time. | TIME(hour, minute, second) | TIME(11, 40, 59) |
Returns the fraction of a 24-hour day the time represents. | TIMEVALUE(time_string) | TIMEVALUE("8:20:00 PM") | |
Returns the current date as a date value. | TODAY() | TODAY() | |
Returns a number representing the day of the week of the date provided. | WEEKDAY(date, [type]) | WEEKDAY("1969-7-20", 1) | |
Returns a number representing the week of the year where the provided date falls. | WEEKNUM(date, [type]) | WEEKNUM("1969-7-20", 1) | |
Calculates the end date after a specified number of working days. | WORKDAY(start_date, num_days, [holidays]) | WORKDAY(DATE(1969,7,20), 4, A1:A10) | |
Calculates the date after a specified number of workdays excluding specified weekend days and holidays. | WORKDAY.INTL(start_date, num_days, [weekend], [holidays]) | WORKDAY.INTL(DATE(1969, 7, 21), 4, 1, A1:A10) | |
YEAR | Returns the year specified by a given date. | YEAR(date) | YEAR("1969-7-20") |
Returns the number of years, including fractional years, between two dates using a specified day count convention. | YEARFRAC(start_date, end_date, [day_count_convention]) | YEARFRAC(DATE(1969,7,16),DATE(1969,7,24),1) |
Database Functions
Function | Description | Syntax | Example |
DAVERAGE | Returns the average of a set of values selected from a database table-like array or range using an SQL-like query. | DAVERAGE(database, field, criteria) | DAVERAGE(A1:F20, D1, A22:D23) |
DCOUNT | Counts numerical values selected from a database table-like array or range using an SQL-like query. | DCOUNT(database, field, criteria) | DCOUNT(A1:F20, D1, A22:D23) |
DCOUNTA | Counts values, including text, selected from a database table-like array or range using a SQL-like query. | DCOUNTA(database, field, criteria) | DCOUNTA(A1:F20, D1, A22:D23) |
DGET | Returns a single value from a database table-like array or range using an SQL-like query. | DGET(database, field, criteria) | DGET(A1:F20, D1, A22:D23) |
DMAX | Returns the maximum value selected from a database table-like array or range using an SQL-like query. | DMAX(database, field, criteria) | DMAX(A1:F20, D1, A22:D23) |
DMIN | Returns the minimum value selected from a database table-like array or range using an SQL-like query. | DMIN(database, field, criteria) | DMIN(A1:F20, D1, A22:D23) |
DPRODUCT | Counts numerical values selected from a database table-like array or range using an SQL-like query. | DPRODUCT(database, field, criteria) | DPRODUCT(A1:F20, D1, A22:D23) |
DSTDEV | Returns the standard deviation of a population sample selected from a database table-like array or range using an SQL-like query. | DSTDEV(database, field, criteria) | DSTDEV(A1:F20, D1, A22:D23) |
DSTDEVP | Returns the standard deviation of an entire population selected from a database table-like array or range using an SQL-like query. | DSTDEVP(database, field, criteria) | DSTDEVP(A1:F20, D1, A22:D23) |
DSUM | Returns the sum of values selected from a database table-like array or range using an SQL-like query. | DSUM(database, field, criteria) | DSUM(A1:F20, D1, A22:D23) |
DVAR | Returns the variance of a population sample selected from a database table-like array or range using an SQL-like query. | DVAR(database, field, criteria) | DVAR(A1:F20, D1, A22:D23) |
DVARP | Returns the variance of an entire population selected from a database table-like array or range using an SQL-like query. | DVARP(database, field, criteria) | DVARP(A1:F20, D1, A22:D23) |
Info Functions
Function | Description | Syntax | Example |
CELL | Retrieves information about a cell | CELL(info_type, [reference]) | CELL("address", C2) |
Returns a number corresponding to the error value in a different cell. | ERROR.TYPE(reference) | ERROR.TYPE(A3) | |
Checks whether the referenced cell is empty. | ISBLANK(value) | ISBLANK(A2) | |
Returns whether a value is a date. | ISDATE(value) | ISDATE("1969-7-20") | |
Checks whether a value is a valid email address. | ISEMAIL(value) | ISEMAIL("simple@example.com") | |
Checks whether a value is an error other than #N/A. | ISERR(value) | ISERR(A2) | |
Checks whether a value is an error. | ISERROR(value) | ISERROR(A2) | |
Checks whether a value is a formula. | ISFORMULA(value) | ISFORMULA(A2) | |
Checks whether a value is TRUE or FALSE. | ISLOGICAL(value) | ISLOGICAL(A2) | |
ISNA | Checks whether a value is the error #N/A. | ISNA(value) | ISNA(A2) |
Checks whether a value is non-textual. | ISNONTEXT(value) | ISNONTEXT(A2) | |
Checks whether a value is a number. | ISNUMBER(value) | ISNUMBER(A2) | |
Checks whether the value in a LAMBDA is missing and returns TRUE or FALSE | ISOMITTED(argument) | =LAMBDA(x,y, IF(ISOMITTED(y),"missing argument",x+y))(1,) | |
Checks whether a value is a valid cell reference. | ISREF(value) | ISREF(A2) | |
Checks whether a value is text. | ISTEXT(value) | ISTEXT(A2) | |
Checks whether a value is a valid URL. | ISURL(value) | ISURL("http://www.example.com") | |
N | Returns the argument provided as a number. | N(value) | N(A2) |
NA | return #N/A error | NA() | NA() |
SHEET | Returns the sheet number of the reference sheet. | SHEET([value]) | =SHEET("Sheet1") |
SHEETS | Returns the number of sheets in the Sheets. | SHEETS() | =SHEETS() |
TYPE | Returns a number associated with the type of data passed into the function. | TYPE(value) | TYPE(C4) |
Logical Functions
Function | Description | Syntax | Example |
AND | Returns TRUE if all of the provided arguments are logically true, and FALSE if any of the provided arguments are logically false. | AND(logical_expression1, [logical_expression2, ...]) | AND(A1=1, A2=2) |
BYCOL | Groups a range by columns | BYCOL(array_or_range, lambda) | BYCOL(A1:C3, LAMBDA(column, SUM(column))) |
BYROW | Groups a range by rows | BYROW(array_or_range, lambda) | BYROW(A1:C3, LAMBDA(column, SUM(column))) |
Returns the logical value `FALSE`. | FALSE() | FALSE() | |
IF | Returns one value if a logical expression is TRUE and another if it is FALSE. | IF(logical_expression, value_if_true, [value_if_false]) | IF(A2 = "foo", "A2 is foo", "A2 is not foo") |
Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent. | IFERROR(value, value_if_error) | IFERROR("No error", "Error") | |
IFNA | Returns the value you specify if the formula returns the #N/A error value; otherwise returns the result of the formula. | IFNA(value, value_if_na) | IFNA(VLOOKUP("Seattle",$A$5:$B$10,0),"Not found") |
IFS | Evaluates multiple conditions and returns a value that corresponds to the first true condition. | IFS(condition1, value1, [condition2, ...], [value2, ...]) | IFS(A1>90, "A", A1>80, "B") |
LAMBDA | Creates and returns a custom function, given a set of names and a formula_expression which uses them. The formula_expression can be calculated by calling the returned function with as many values as the names declared. | LAMBDA(name, [formula_expression, ...]) | LAMBDA(x, x+1) |
LET | Assigns name with the value_expression results and returns the result of the formula_expression. The formula_expression can use the names defined in the scope of the LET function. The value_expressions are evaluated only once in the LET function even if the following value_expressions or the formula_expression use them multiple times. | LET(name, [value_expression], [The formula to be calculated. It uses names declared in the LET function., ...]) | LET(x, SUM(A1:A5), y, AVERAGE(A1:A5), x*x + y*y) |
MAKEARRAY | Creates a calculated array | MAKEARRAY(rows, columns, lambda) | MAKEARRAY(3, 4, LAMBDA(row_index, column_index, row_index*column_index)) |
MAP | Maps each value in the given arrays to a new value | MAP(array1, [array2], [lambda, ...]) | MAP(A1:A3, B1:B3, LAMBDA(cell1, cell2, MAX(cell1, cell2))) |
NOT | Reverses the logic of its argument | NOT(logical) | NOT(TRUE) |
OR | Returns TRUE if any of the provided arguments are logically true, and FALSE if all of the provided arguments are logically false. | OR(logical_expression1, [logical_expression2, ...]) | OR(A1=1, A2=2) |
REDUCE | Reduces an array to an accumulated result | REDUCE(initial_value, array_or_range, lambda) | REDUCE(0, A1:C3, LAMBDA(total, value, total + value)) |
SCAN | Scans an array and produces intermediate values | SCAN(initial_value, array_or_range, lambda) | SCAN(0, A1:C3, LAMBDA(cumulative, value, cumulative + value)) |
Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met. | SWITCH(expression, case1, value1, [value1, ...]) | SWITCH(A1, 0, "0", 1, "1") | |
TRUE | Returns the logical value `TRUE`. | TRUE() | TRUE() |
XOR | The XOR function returns TRUE if an odd number of the provided arguments are logically true, and FALSE otherwise. | XOR(logical_expression1, [logical_expression2, ...]) | XOR(TRUE, FALSE, TRUE) |
Search Functions
Function | Description | Syntax | Example |
Returns a cell reference as a string. | ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) | ADDRESS(1, 2, 4, FALSE, "Sheet1") | |
Returns the number of areas in a reference. | AREAS(reference) | AREAS(B2:D4) | |
Returns an element from a list of choices based on index. | CHOOSE(index, choice1, [choice2, ...]) | CHOOSE(2, "A", "B") | |
Creates an array of the specified columns | CHOOSECOLS(array, col_num1, [col_num2, ...]) | CHOOSECOLS(A2:B9, 2) | |
Creates an array of the specified rows | CHOOSEROWS(array, row_num1, [row_num2, ...]) | CHOOSEROWS(A2:B9, 2) | |
Returns the column number of a specified cell, with A=1. | COLUMN([cell_reference]) | COLUMN(C9) | |
Returns the number of columns in a specified array or range. | COLUMNS(range) | COLUMNS(A2:J10) | |
DROP | Excludes a specified number of rows or columns from the start or end of an array. You might find this function useful to remove headers and footers in a Sheet report to return only the data. | DROP(array, rows, [columns]) | =DROP(A2:C4,2,2) |
Expands or pads an array to specified row and column dimensions. | EXPAND(array, rows, [columns], [pad_with]) | =EXPAND(A2:B3,3,3) | |
Returns a filtered version of the source range, returning only rows or columns that meet the specified conditions. | FILTER(range, condition1, [condition2, ...]) | FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) | |
Returns a formula as a string. | FORMULATEXT(cell_reference) | FORMULATEXT(A1) | |
Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings. | GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, ...], [pivot_item, ...]) | GETPIVOTDATA("Avg Price (AVERAGE)", A1, "District", "East") | |
HLOOKUP | Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found. | HLOOKUP(The value to search for. For example, 42, "Cats", or I24., range, index, [is_sorted]) | HLOOKUP(10003, A2:Z6, 2, FALSE) |
Appends ranges horizontally | HSTACK(range1, [range2, ...]) | HSTACK(A2:F9, F10:F15) | |
Imports a range of cells from a specified spreadsheet. | IMPORTRANGE(spreadsheet_url, range_string) | IMPORTRANGE("Explore example ", "A1:D21") | |
Returns the content of a cell, within a specific range, as specified by row and column numbers. When multiple ranges are included, you can indicate within which range to find the content. | INDEX(reference, row, [column], [area]) | INDEX(A1:C20, 5, 1) | |
Returns a cell reference specified by a string. | INDIRECT(cell_reference_as_string, [is_A1_notation]) | INDIRECT("B"&"10", TRUE) | |
Looks through a sorted row or column for a key and returns the value of the cell in a result range located in the same position as the search row or column. | LOOKUP(search_key, search_range|search_result_array, [result_range]) | LOOKUP(10003, A1:A100, B1:B100) | |
Returns the relative position of an item in a range that matches a specified value. | MATCH(search_key, range, [search_type]) | MATCH("Sunday", A2:A9, 0) | |
Returns a range reference shifted a specified number of rows and columns from a starting cell reference. | OFFSET(cell_reference, offset_rows, offset_cols, [height], [width]) | OFFSET(A2, 3, 4, 2, 2) | |
ROW | Returns the row number of a specified cell. | ROW([reference]) | ROW(A9) |
ROWS | Returns the number of rows in a specified array or range. | ROWS(range) | ROWS(A9:A62) |
SORT | Sorts the rows of a given array or range by the values in one or more columns. | SORT(array, [[sort_index]], [[sort_order]], [[by_col]]) | SORT(A2:A17) |
Sorts rows of range by specified column | SORTBY(array, by_array1, [sort_order1], [by_array2, ...], [sort_order2, ...]) | SORTBY(A2:C20, A2:A20, 1, B2:B20, -1) | |
Returns the first n items in a data set after performing a sort | SORTN(range, [n], [display_ties_mode], [sort_column, ...], [is_ascending, ...]) | SORTN(A2:C20, 2, 0, 1, TRUE, 2, FALSE) | |
TAKE | Returns a specified number of contiguous rows or columns from the start or end of an array. | TAKE(array, rows, [columns]) | =TAKE(A2:C4,2,2) |
Transforms a range into a single column | TOCOL(array_or_range, [Ignore], [scan_by_column ]) | TOCOL(A2:F9, 1, TRUE) | |
Transforms an array into a single row | TOROW(array_or_range, [IGNORE - [optional]], [scan_by_column]) | TOROW(A2:F9, 1, TRUE) | |
Transposes the rows and columns of an array or range of cells. | TRANSPOSE(array_or_range) | TRANSPOSE({1,2;3,4;5,6}) | |
Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found. | VLOOKUP(search_key, range, index, [is_sorted]) | VLOOKUP(10003, A2:B26, 2, FALSE) | |
Appends ranges vertically | VSTACK(range1, [range2, ...]) | VSTACK(A2:F9, F10:F15) | |
Wraps the provided range by columns of the specified size | WRAPCOLS(range, wrap_count, [pad_with]) | WRAPCOLS(A2:A9, 3, "Pad") | |
Wraps the provided range by rows of the specified size | WRAPROWS(range, wrap_count, [pad_with]) | WRAPROWS(A2:A9, 3, "Pad") | |
searches for a specified item in an array or range of cells, and then returns the item's relative position. | XMATCH(lookup_value, lookup_array, [[match_mode]], [[search_mode]]) | XMATCH(F2, C3:C9, 1) |
Math Functions
Function | Description | Syntax | Example |
ABS | Returns the absolute value of a number., i.e. the distance the number is from 0, without regard to its sign. | ABS(number) | ABS(-2) |
ACOS | Returns the inverse cosine of a value, in radians. | ACOS(number) | ACOS(0) |
ACOSH | Returns the inverse hyperbolic cosine of a number. | ACOSH(number) | ACOSH(2) |
ACOT | Returns the inverse cotangent of a value, in radians. | ACOT(value) | ACOT(0) |
ACOTH | Returns the inverse hyperbolic cotangent of a value, in radians. | ACOTH(value) | ACOTH(2) |
AGGREGATE | Returns totals in a list or database. | AGGREGATE(function_num, options, ref1, [ref2, ...]) | =AGGREGATE(4, 6, A1:A11) |
ARABIC | Computes the value of a Roman numeral. | ARABIC(roman_numeral) | ARABIC("XIV") |
ASIN | Returns the inverse sine of a value, in radians. | ASIN(value) | ASIN(0) |
ASINH | Returns the inverse hyperbolic sine of a number. | ASINH(value) | ASINH(0.9) |
ATAN | Returns the inverse tangent of a value, in radians. | ATAN(value) | ATAN(0) |
ATAN2 | Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. | ATAN2(x, y) | ATAN2(1, 1) |
ATANH | Returns the inverse hyperbolic tangent of a number. | ATANH(value) | ATANH(0.9) |
BASE | Converts a number into a text representation in another base, for example, base 2 for binary. | BASE(value, base, [min_length]) | BASE(19, 2, 6) |
CEILING | Rounds a number up to the nearest integer multiple of specified significance factor. | CEILING(value, factor) | CEILING(23.25, 0.1) |
CEILING.MATH | Rounds a number up to the nearest integer multiple of specified significance, with negative numbers rounded toward or away from 0 depending on the mode. | CEILING.MATH(number, [significance], [mode]) | CEILING.MATH(-26.2, 10, 1) |
CEILING.PRECISE | Rounds a number up to the nearest integer or multiple of specified significance. If the number is positive or negative, it's rounded up. | CEILING.PRECISE(number, [significance]) | CEILING.PRECISE(-23.25, 0.1) |
Returns the number of ways to choose some number of objects from a pool of a given size of objects. | COMBIN(n, k) | COMBIN(4, 2) | |
COMBINA | Returns the number of ways to choose some number of objects from a pool of a given set of objects, including ways that choose the same object multiple times. | COMBINA(n, k) | COMBINA(5, 3) |
COS | Returns the cosine of an angle provided in radians. | COS(angle) | COS(3.14) |
COSH | Returns the hyperbolic cosine of any real number. | COSH(value) | COSH(0.48) |
COT | Returns the cotangent of an angle provided in radians. | COT(angle) | COT(3.14) |
COTH | Returns the hyperbolic cotangent of any real number. | COTH(value) | COTH(2) |
Returns the number of empty values in a list of values and ranges. | COUNTBLANK(value1) | COUNTBLANK(A2:C100, F2:G100) | |
COUNTUNIQUE | Counts number of unique values in a range. | COUNTUNIQUE(value1, [value2, ...]) | COUNTUNIQUE(A1:C100, 100) |
CSC | Returns the cosecant of an angle provided in radians. | CSC(angle) | CSC(1.57) |
CSCH | Returns the hyperbolic cosecant of any real number. | CSCH(value) | CSCH(2) |
DECIMAL | Converts the text representation of a number in another base, to base 10 (decimal). | DECIMAL(value, base) | DECIMAL(101,2) |
DEGREES | Converts an angle value in radians to degrees. | DEGREES(angle) | DEGREES(3.14) |
EVEN | Rounds a number up to the nearest even integer. | EVEN(value) | EVEN(3) |
EXP | Returns Euler's number, e (~2.718) raised to a power. | EXP(exponent) | EXP(2) |
FACT | Returns the factorial of a number | FACT(number) | FACT(3) |
FACTDOUBLE | Returns the double factorial of a number | FACTDOUBLE(number) | FACTDOUBLE(3) |
FLATTEN | Flattens values into a single column | FLATTEN(range, [range2, ...]) | FLATTEN(A1:B2, B3:B4) |
FLOOR | Rounds a number down to the nearest integer multiple of specified significance. | FLOOR(value, factor) | FLOOR(23.25, 0.1) |
FLOOR.MATH | Rounds a number down to the nearest integer multiple of specified significance, with negative numbers rounding toward or away from 0 depending on the mode. | FLOOR.MATH(number, [significance], [mode]) | FLOOR.MATH(-26.2, 10, 1) |
FLOOR.PRECISE | Rounds a number down to the nearest integer multiple of specified significance. If the number is positive or negative, it is rounded down. | FLOOR.PRECISE(number, [significance]) | FLOOR.PRECISE(-23.25, 0.1) |
GCD | Returns the greatest common divisor of one or more integers. | GCD(value1, [value2, ...]) | GCD(A2:A5, 6) |
INT | Rounds a number down to the nearest integer that is less than or equal to it. | INT(number) | INT(99.44) |
ISEVEN | Checks whether the provided value is even. | ISEVEN(number) | ISEVEN(4) |
ISO.CEILING | Rounds number up to nearest multiple of a factor. | ISO.CEILING(number, [significance]) | ISO.CEILING(-23.25, 0.1) |
ISODD | Checks whether the provided value is odd. | ISODD(number) | ISODD(4) |
LCM | Returns the least common multiple of one or more integers. | LCM(value1, [value2, ...]) | LCM(A2:A5, 6) |
LN | Returns the logarithm of a number, base e (Euler's number). | LN(number) | LN(100) |
LOG | Returns the the logarithm of a number given a base. | LOG(number, [base]) | LOG(128, 2) |
Returns the base-10 logarithm of a number. | LOG10(number) | LOG10(10) | |
MDETERM | Matrix determinant of a square matrix | MDETERM(square_matrix) | MDETERM(A1:D4) |
MINVERSE | Multiplicative inverse of square matrix | MINVERSE(square_matrix) | MINVERSE(A1:D4) |
MMULT | Calculates the matrix product of two matrices specified as arrays or ranges. | MMULT(matrix1, matrix2) | MMULT(A1:B3, C1:F2) |
MOD | Returns the result of the modulo operator, the remainder after a division operation. | MOD(dividend, divisor) | MOD(10, 4) |
MROUND | Rounds one number to the nearest integer multiple of another. | MROUND(value, factor) | MROUND(21, 14) |
MULTINOMIAL | Multinomial distribution function. | MULTINOMIAL(value1, [value2, ...]) | MULTINOMIAL(1, 2) |
MUNIT | Returns a unit matrix. | MUNIT(dimension) | MUNIT(2) |
ODD | Rounds a number up to the nearest odd integer. | ODD(value) | ODD(2) |
PI | Returns the value of Pi to 15 decimal places. | PI() | PI() |
POWER | Returns a number raised to a power. | POWER(base, exponent) | POWER(4, 0.5) |
PRODUCT | Returns the result of multiplying a series of numbers together. | PRODUCT(factor1, [factor2, ...]) | PRODUCT(A2:A100, 2) |
QUOTIENT | Returns the integer portion of a division. | QUOTIENT(dividend, divisor) | QUOTIENT(4, 2) |
RADIANS | Converts degrees to radians. | RADIANS(angle) | RADIANS(270) |
RAND | Returns a random number between 0 inclusive and 1 exclusive. | RAND() | RAND() |
RANDARRAY | Returns an array of random numbers | RANDARRAY([rows], [columns], [min], [max], [integer]) | RANDARRAY(5, 3, 1, 100) |
RANDBETWEEN | Returns a uniformly random integer between two values, inclusive. | RANDBETWEEN(low, high) | RANDBETWEEN(1, 10) |
ROMAN | Formats a number in Roman numerals. | ROMAN(number, [rule_relaxation]) | ROMAN(499,0) |
ROUND | Rounds a number to a certain number of decimal places according to standard rules. | ROUND(number, places) | ROUND(99.44, 1) |
ROUNDDOWN | Rounds a number to a certain number of decimal places, always rounding down to the next valid increment. | ROUNDDOWN(number, places) | ROUNDDOWN(99.44, 1) |
ROUNDUP | Rounds a number to a certain number of decimal places, always rounding up to the next valid increment. | ROUNDUP(number, places) | ROUNDUP(99.44, 1) |
SEC | Returns the secant of an angle provided in radians. | SEC(angle) | SEC(3.14) |
SECH | Returns the hyperbolic secant of any real number. | SECH(value) | SECH(2) |
SEQUENCE | Returns a grid of sequential numbers starting at a specified start value and increasing by a specified step size. By default, the sequence starts at and increases by 1. | SEQUENCE(rows, [columns], [start], [step]) | SEQUENCE(2, 2, 1, 1) |
SERIESSUM | Sum of a power series. | SERIESSUM(x, n, m, a) | SERIESSUM(3, 0, 2, B2:B10) |
SIGN | Given an input number, returns -1 if it is negative, 1 if positive, and 0 if it is zero. | SIGN(value) | SIGN(-42) |
SIN | Returns the sine of an angle provided in radians. | SIN(angle) | SIN(3.14) |
SINH | Returns the hyperbolic sine of any real number. | SINH(value) | SINH(2) |
SQRT | Returns the positive square root of a positive number. | SQRT(number) | SQRT(9) |
SQRTPI | Returns the positive square root of the product of Pi and the given positive number. | SQRTPI(value) | SQRTPI(9) |
SUBTOTAL | Returns a subtotal for a vertical range of cells using a specified aggregation function. | SUBTOTAL(function_code, range1, [range2, ...]) | SUBTOTAL(1, A2:A5, B2:B8) |
SUM | Returns the sum of a series of numbers and/or cells. | SUM(number1, [number2, ...]) | SUM(A2:A100, 101) |
Returns a conditional sum across a range. | SUMIF(range, criteria, [sum_range]) | SUMIF(A1:A10, ">20", B1:B10) | |
Returns the sum of a range depending on multiple criteria. | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, ...], [criteria2, ...]) | SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<30") | |
SUMPRODUCT | Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges. | SUMPRODUCT(array1, [array2, ...]) | SUMPRODUCT(A2:C5, D2:F5) |
SUMSQ | Sum of squares. | SUMSQ(value1, [value2, ...]) | SUMSQ(A2:A100, 10) |
SUMX2MY2 | Sum of the differences of squares | SUMX2MY2(array_x, array_y) | SUMX2MY2(A2:A9, B2:B9) |
SUMX2PY2 | Sum of the sums of squares | SUMX2PY2(array_x, array_y) | SUMX2PY2(A2:A9, B2:B9) |
SUMXMY2 | Sum of the squares of differences | SUMXMY2(array_x, array_y) | SUMXMY2(A2:A9, B2:B9) |
TAN | Returns the tangent of an angle provided in radians. | TAN(angle) | TAN(3.14) |
TANH | Returns the hyperbolic tangent of any real number. | TANH(value) | TANH(1) |
TRUNC | Truncates a number to a certain number of significant digits by omitting less significant digits. | TRUNC(value, [places]) | TRUNC(3.141592654, 2) |
Operator Functions
Function | Description | Syntax | Example |
ADD | Sum of two numbers | ADD(value1, value2) | ADD(2, 3) |
Returns the concatenation of two values. Equivalent to the `&` operator. | CONCAT(value1, [value2, ...]) | CONCAT("Hello", "Goodbye") | |
Appends strings to one another. | CONCATENATE(string1, [string2, ...]) | CONCATENATE("Hello", "Goodbye") | |
DIVIDE | One number divided by another | DIVIDE(dividend, divisor) | DIVIDE(4, 2) |
EQ | Equal | EQ(value1, value2) | EQ(1, 2) |
GT | Strictly greater than | GT(value1, value2) | GT(5, 2) |
GTE | Greater than or equal to | GTE(value1, value2) | GTE(5, 3) |
ISBETWEEN | Compares if a value is between two other values | ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive]) | ISBETWEEN(5, 4.5, 7.9, TRUE, TRUE) |
LT | Less than | LT(value1, value2) | LT(3, 6) |
LTE | Less than or equal to | LTE(value1, value2) | LTE(3, 6) |
MINUS | Difference of two numbers | MINUS(value1, value2) | MINUS(8, 3) |
MULTIPLY | Product of two numbers | MULTIPLY(factor1, factor2) | MULTIPLY(6, 7) |
NE | Not equal | NE(value1, value2) | NE(6, 7) |
POW | A number raised to a power | POW(base, exponent) | POW(4, 0.5) |
UMINUS | A number with the sign reversed | UMINUS(value) | UMINUS(-4) |
UNARY_PERCENT | Value interpreted as a percentage | UNARY_PERCENT(percentage) | UNARY_PERCENT(0.5) |
UPLUS | A specified number, unchanged | UPLUS(value) | UPLUS(-4) |
Statistical Functions
Function | Description | Syntax | Example |
Calculates the average of the magnitudes of deviations of data from a dataset's mean. | AVEDEV(value1, [value2, ...]) | AVEDEV(1, 2) | |
Returns the numerical average value in a dataset, ignoring text. | AVERAGE(value1, [value2, ...]) | AVERAGE(A2:A100, B2:B100) | |
AVERAGE.WEIGHTED | Returns the weighted average of a set of values. | AVERAGE.WEIGHTED(values, weights, [additional_values, ...], [additional_weights, ...]) | AVERAGE.WEIGHTED(A1:A10, B1:B10, 5, 0.5) |
AVERAGEA | Returns the numerical average value in a dataset, ignoring text. | AVERAGEA(value1, [value2, ...]) | AVERAGEA(A2:A100, B2:B100) |
Returns the average of a range depending on criteria. | AVERAGEIF(criteria_range, criteria, [average_range]) | AVERAGEIF(A1:A10, ">20", B1:B10) | |
Returns the average of a range depending on multiple criteria. | AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...]) | AVERAGEIFS(A1:A10, B1:B10, ">20", C1:C10, "<30") | |
Returns the probability of a given value as defined by the beta distribution function. | BETA.DIST(value, alpha, beta, cumulative, [lower_bound], [upper_bound]) | BETA.DIST(0.65, 1.234, 7, TRUE, 0.5, 3) | |
Returns the value of the inverse beta distribution function for a given probability. | BETA.INV(probability, alpha, beta, [lower_bound], [upper_bound]) | BETA.INV(0.65, 1.234, 7, 1, 3) | |
BETADIST | Returns the probability of a given value as defined by the beta distribution function. | BETADIST(value, alpha, beta, [lower_bound], [upper_bound]) | BETADIST(0.65, 1.234, 7, 0.5, 3) |
BETAINV | Returns the value of the inverse beta distribution function for a given probability. | BETAINV(probability, alpha, beta, [lower_bound], [upper_bound]) | BETAINV(0.65, 1.234, 7, 1, 3) |
BINOM.DIST | Calculates the probability of drawing a certain number of successes (or a maximum number of successes) in a certain number of tries given a population of a certain size containing a certain number of successes, with replacement of draws. | BINOM.DIST(num_successes, num_trials, prob_success, cumulative) | BINOM.DIST(3, 7, 0.4, FALSE) |
BINOM.DIST.RANGE | Returns the probability of drawing a specific number of successes or range of successes given a probability and number of tries. | BINOM.DIST.RANGE(num_trials, prob_success, num_successes, [max_num_successes]) | BINOM.DIST.RANGE(100, 0.5, 45) |
BINOM.INV | Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria. | BINOM.INV(num_trials, prob_success, target_prob) | BINOM.INV(100,0.005,0.8) |
BINOMDIST | Calculates the probability of drawing a certain number of successes (or a maximum number of successes) in a certain number of tries given a population of a certain size containing a certain number of successes, with replacement of draws. | BINOMDIST(num_successes, num_trials, prob_success, cumulative) | BINOMDIST(3, 7, 0.4, FALSE) |
CHIDIST | Calculates the right-tailed chi-squared distribution, often used in hypothesis testing. | CHIDIST(x, degrees_freedom) | CHIDIST(3.45, 2) |
CHIINV | Calculates the inverse of the right-tailed chi-squared distribution. | CHIINV(probability, degrees_freedom) | CHIINV(0.42, 2) |
CHISQ.DIST | Calculates the left-tailed chi-squared distribution, often used in hypothesis testing. | CHISQ.DIST(x, degrees_freedom, cumulative) | CHISQ.DIST(3.45, 2, TRUE) |
CHISQ.DIST.RT | Calculates the right-tailed chi-squared distribution, which is commonly used in hypothesis testing. | CHISQ.DIST.RT(x, degrees_freedom) | CHISQ.DIST.RT(3.45, 2) |
CHISQ.INV | Calculates the inverse of the left-tailed chi-squared distribution. | CHISQ.INV(probability, degrees_freedom) | CHISQ.INV(0.42, 2) |
CHISQ.INV.RT | Calculates the inverse of the right-tailed chi-squared distribution. | CHISQ.INV.RT(probability, degrees_freedom) | CHISQ.INV.RT(0.42, 2) |
CHISQ.TEST | Returns the probability associated with a Pearson's chi-squared test on the two ranges of data. Determines the likelihood that the observed categorical data is drawn from an expected distribution. | CHISQ.TEST(observed_range, expected_range) | CHISQ.TEST(A1:A5, B1:B5) |
CHITEST | Returns the probability associated with a Pearson's chi-squared test on the two ranges of data. Determines the likelihood that the observed categorical data is drawn from an expected distribution. | CHITEST(observed_range, expected_range) | CHITEST(A1:A5, B1:B5) |
CONFIDENCE | Confidence interval for a normal distribution. | CONFIDENCE(alpha, standard_deviation, pop_size) | CONFIDENCE(0.05, 1.6, 250) |
CONFIDENCE.NORM | Confidence interval for a normal distribution. | CONFIDENCE.NORM(alpha, standard_deviation, pop_size) | CONFIDENCE.NORM(0.05, 1.6, 250) |
CONFIDENCE.T | Confidence interval for a student t's distribution. | CONFIDENCE.T(alpha, standard_deviation, size) | CONFIDENCE.T(0.05, 1.6, 250) |
Converts a numeric value to a different unit of measure. | CONVERT(value, start_unit, end_unit) | CONVERT(5.1, "g", "kg") | |
CORREL | Pearson Product-Moment Correlation Coefficient. | CORREL(data_y, data_x) | CORREL(A2:A100, B2:B100) |
Returns the number of numeric values in a dataset. | COUNT(value1, [value2, ...]) | COUNT(A2:A100, B2:B100) | |
Returns the number of values in a dataset. | COUNTA(value1, [value2, ...]) | COUNTA(A2:A100, B2:B100) | |
Returns a conditional count across a range. | COUNTIF(range, criterion) | COUNTIF(A1:A10, ">20") | |
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met. | COUNTIFS(criteria_range1, criteria1, [criteria_range2, ...], [criterion2, ...]) | COUNTIFS(A1:A10, ">20", B1:B10, "<30") | |
COVAR | Calculates the covariance of a dataset. | COVAR(data_y, data_x) | COVAR(A2:A100, B2:B100) |
COVARIANCE.P | The covariance of a dataset. | COVARIANCE.P(data_y, data_x) | COVARIANCE.P(A2:A100, B2:B100) |
COVARIANCE.S | The sample covariance of a dataset. | COVARIANCE.S(data_y, data_x) | COVARIANCE.S(A2:A100, B2:B100) |
CRITBINOM | Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria. | CRITBINOM(num_trials, prob_success, target_prob) | CRITBINOM(100,0.005,0.8) |
DEVSQ | The sum of squares of deviations based on a sample. | DEVSQ(value1, [value2, ...]) | DEVSQ(1, 2) |
EXPON.DIST | Exponential distribution function. | EXPON.DIST(x, lambda, cumulative) | EXPON.DIST(4, 0.5, FALSE) |
EXPONDIST | Exponential distribution function. | EXPONDIST(x, lambda, cumulative) | EXPONDIST(4, 0.5, FALSE) |
F.DIST | F probability distribution (left-tailed). | F.DIST(x, degrees_freedom1, degrees_freedom2, cumulative) | F.DIST(15.35, 7, 6, TRUE) |
F.DIST.RT | F probability distribution . | F.DIST.RT(x, degrees_freedom1, degrees_freedom2) | F.DIST.RT(15.35, 7, 6) |
F.INV | Calculates the inverse of the left-tailed F probability distribution. | F.INV(probability, degrees_freedom1, degrees_freedom2) | F.INV(0.42, 2, 3) |
F.INV.RT | Calculates the inverse of the right-tailed F probability distribution. | F.INV.RT(probability, degrees_freedom1, degrees_freedom2) | F.INV.RT(0.42, 2, 3) |
F.TEST | F-test for equality of variances. | F.TEST(range1, range2) | F.TEST(A1:A5, B1:B5) |
FDIST | F probability distribution . | FDIST(x, degrees_freedom1, degrees_freedom2) | FDIST(15.35, 7, 6) |
FINV | Calculates the inverse of the right-tailed F probability distribution. | FINV(probability, degrees_freedom1, degrees_freedom2) | FINV(0.42, 2, 3) |
FISHER | Fisher transformation of a specified value. | FISHER(value) | FISHER(0.962) |
FISHERINV | Inverse fisher transformation of a specified value. | FISHERINV(value) | FISHERINV(0.962) |
FORECAST | Expected y-value using linear regression. | FORECAST(x, data_y, data_x) | FORECAST(5, A1:A10, B1:B10) |
FORECAST.LINEAR | Expected y-value using linear regression. | FORECAST.LINEAR(x, data_y, data_x) | FORECAST.LINEAR(5, A1:A10, B1:B10) |
FREQUENCY | calculates how often values occur within a range of values, and then returns a vertical array of numbers. | FREQUENCY(data_array, bins_array) | FREQUENCY(A2:A10, B2:B4) |
FTEST | F-test for equality of variances. | FTEST(range1, range2) | FTEST(A1:A5, B1:B5) |
GAMMA | Returns the Gamma function result for the specified number. | GAMMA(number) | GAMMA(4) |
Calculates the gamma distribution, a two-parameter continuous probability distribution. | GAMMA.DIST(x, alpha, beta, cumulative) | GAMMA.DIST(4.79, 1.234, 7, TRUE) | |
Returns the value of the inverse gamma cumulative distribution function for the specified probability, alpha, and beta parameters. | GAMMA.INV(probability, alpha, beta) | GAMMA.INV(0.65, 4, 2) | |
GAMMADIST | Calculates the gamma distribution, a two-parameter continuous probability distribution. | GAMMADIST(x, alpha, beta, cumulative) | GAMMADIST(4.79, 1.234, 7, TRUE) |
GAMMAINV | Returns the value of the inverse gamma cumulative distribution function for the specified probability, alpha, and beta parameters. | GAMMAINV(probability, alpha, beta) | GAMMAINV(0.65, 4, 2) |
Returns the logarithm of a specified Gamma function, base e (Euler's number). | GAMMALN(value) | GAMMALN(4) | |
Returns the logarithm of a specified Gamma function, base e (Euler's number). | GAMMALN.PRECISE(value) | GAMMALN.PRECISE(4) | |
GAUSS | Probability of a standard normal variable falling within z standard deviations of the mean. | GAUSS(z) | GAUSS(1) |
GEOMEAN | Calculates the geometric mean of a dataset. | GEOMEAN(value1, [value2, ...]) | GEOMEAN(1, 2) |
GROWTH | Fits points to exponential growth trend | GROWTH(known_data_y, [known_data_x], [new_data_x], [b]) | GROWTH(B2:B10, A2:A10, A11:A13, TRUE) |
HARMEAN | Calculates the harmonic mean of a dataset. | HARMEAN(value1, [value2, ...]) | HARMEAN(1, 2) |
HYPGEOM.DIST | Hypergeometric distribution probability. | HYPGEOM.DIST(num_successes, num_draws, successes_in_pop, pop_size, cumulative) | HYPGEOM.DIST(4, 12, 20, 40, TRUE) |
HYPGEOMDIST | Hypergeometric distribution probability. | HYPGEOMDIST(num_successes, num_draws, successes_in_pop, pop_size) | HYPGEOMDIST(4, 12, 20, 40) |
Calculates the y-value at which the line resulting from linear regression of a dataset will intersect the y-axis (x=0). | INTERCEPT(data_y, data_x) | INTERCEPT(A2:A100, B2:B100) | |
KURT | Calculates the kurtosis of a dataset, which describes the shape, and in particular the "peakedness" of that dataset. | KURT(value1, [value2, ...]) | KURT(1,2,3,4) |
Returns the nth largest element from a data set, where n is user-defined. | LARGE(data, n) | LARGE(A2:B100, 4) | |
LINEST | Best-fit linear trend via least squares | LINEST(known_data_y, [known_data_x], [calculate_b], [verbose]) | LINEST(B2:B10, A2:A10, FALSE, TRUE) |
LOGEST | Best-fit exponential growth curve | LOGEST(known_data_y, [known_data_x], [b], [verbose]) | LOGEST(B2:B10, A2:A10, TRUE, TRUE) |
LOGINV | Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value. | LOGINV(x, mean, standard_deviation) | LOGINV(0.4, 4, 6) |
LOGNORM.DIST | Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value. | LOGNORM.DIST(x, mean, standard_deviation) | LOGNORM.DIST(4, 4, 6) |
LOGNORM.INV | Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value. | LOGNORM.INV(x, mean, standard_deviation) | LOGNORM.INV(0.4, 4, 6) |
LOGNORMDIST | Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value. | LOGNORMDIST(x, mean, standard_deviation) | LOGNORMDIST(4, 4, 6) |
MARGINOFERROR | Calculates the margin of error given values and confidence. | MARGINOFERROR(range, confidence) | MARGINOFERROR(A1:A6, 0.95) |
MAX | Returns the maximum value in a numeric dataset. | MAX(value1, [value2, ...]) | MAX(A2:A100, 42) |
MAXA | Returns the maximum value in a dataset, including numbers, text and logical values. | MAXA(value1, [value2, ...]) | MAXA(A2:A100, 42) |
MAXIFS | Returns the maximum value in a range of cells, filtered by a set of criteria. | MAXIFS(range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...]) | MAXIFS(A1:A3, B1:B3, 1, C1:C3, “A”) |
Returns the median value in a numeric dataset. | MEDIAN(value1, [value2, ...]) | MEDIAN(A2:A100, B2:B100) | |
MIN | Returns the minimum value in a numeric dataset. | MIN(value1, [value2, ...]) | MIN(A2:A100, 5) |
MINA | Returns the minimum value in a dataset, including numbers, text, and logical values. | MINA(value1, [value2, ...]) | MINA(A2:A100, 5) |
MINIFS | Returns the minimum value in a range of cells, filtered by a set of criteria. | MINIFS(range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...]) | MINIFS(A2:A7, B2:B7, "b", D2:D7, ">100") |
MODE | Returns the most commonly occurring value in a dataset | MODE(value1, [value2, ...]) | MODE(A2:A100, 5) |
Returns the most commonly occurring value in a dataset, contain multiple-value in a array. | MODE.MULT(value1, [value2, ...]) | MODE.MULT(A2:A100, 5) | |
Returns the most commonly occurring value in a dataset | MODE.SNGL(value1, [value2, ...]) | MODE.SNGL(A2:A100, 5) | |
NEGBINOM.DIST | Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials. | NEGBINOM.DIST(num_failures, num_successes, prob_success, Cumulative) | NEGBINOM.DIST(4, 2, 0.1, FALSE) |
NEGBINOMDIST | Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials. | NEGBINOMDIST(num_failures, num_successes, prob_success) | NEGBINOMDIST(4, 2, 0.1) |
NORM.DIST | Returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation. | NORM.DIST(x, mean, standard_deviation, cumulative) | NORM.DIST(1, 0, 1, TRUE) |
NORM.INV | Returns the value of the inverse normal distribution function for a specified value, mean and standard deviation. | NORM.INV(x, mean, standard_deviation) | NORM.INV(0.75, 1, 4) |
Returns the value of the standard normal distribution function (or standard normal cumulative distribution function) for a specified value. | NORM.S.DIST(x, cumulative) | NORM.S.DIST(2.4 ,TRUE) | |
Returns the value of the inverse standard normal distribution function for a specified value. | NORM.S.INV(x) | NORM.S.INV(0.75) | |
NORMDIST | Returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation. | NORMDIST(x, mean, standard_deviation, cumulative) | NORMDIST(1, 0, 1, TRUE) |
NORMINV | Returns the value of the inverse normal distribution function for a specified value, mean and standard deviation. | NORMINV(x, mean, standard_deviation) | NORMINV(0.75, 1, 4) |
NORMSDIST | Returns the value of the standard normal cumulative distribution function for a specified value. | NORMSDIST(x) | NORMSDIST(2.4) |
NORMSINV | Returns the value of the inverse standard normal distribution function for a specified value. | NORMSINV(x) | NORMSINV(0.75) |
PEARSON | Pearson Product-Moment Correlation Coefficient. | PEARSON(data_y, data_x) | PEARSON(A2:A100, B2:B100) |
Returns the value at a given percentile of a dataset. | PERCENTILE(data, percentile) | PERCENTILE(A2:A100, 0.95) | |
PERCENTILE.EXC | Returns the value at a given percentile of a data set exclusive of 0 and 1. | PERCENTILE.EXC(data, percentile) | PERCENTILE.EXC(A2:A100, 0.25) |
PERCENTILE.INC | Returns the value at a given percentile of a dataset. | PERCENTILE.INC(data, percentile) | PERCENTILE.INC(A2:A100, 0.95) |
Returns the percentage rank (percentile) of a specified value in a dataset. | PERCENTRANK(data, value, [siginificant_digits]) | PERCENTRANK(A2:A100, 42, 4) | |
PERCENTRANK.EXC | Returns the percentage rank (percentile) from 0 to 1 exclusive of a specified value in a dataset. | PERCENTRANK.EXC(data, value, [significant_digits]) | PERCENTRANK.EXC(A2:A100, 42, 4) |
PERCENTRANK.INC | Returns the percentage rank (percentile) from 0 to 1 inclusive of a specified value in a dataset. | PERCENTRANK.INC(data, value, [significant_digits]) | PERCENTRANK.INC(A2:A100, 42, 4) |
PERMUT | Number of permutations from a number of objects. | PERMUT(n, k) | PERMUT(4, 2) |
PERMUTATIONA | Number of permutations with repetition from a number of objects. | PERMUTATIONA(n, k) | PERMUTATIONA(4, 2) |
PHI | Returns the value of the normal distribution with mean 0 and standard deviation 1. | PHI(x) | PHI(0.25) |
POISSON | Returns the value of the Poisson distribution function (or Poisson cumulative distribution function) for a specified value and mean. | POISSON(x, mean, cumulative) | POISSON(1, 2, TRUE) |
POISSON.DIST | Returns the value of the Poisson distribution function (or Poisson cumulative distribution function) for a specified value and mean. | POISSON.DIST(x, mean, cumulative) | POISSON.DIST(1, 2, TRUE) |
PROB | Given a set of values and corresponding probabilities, calculates the probability that a value chosen at random falls between two limits. | PROB(data, probabilities, low_limit, [high_limit ]) | PROB(A2:A100, B2:B100, 42, 50) |
Returns a value nearest to a specified quartile of a dataset. | QUARTILE(data, quartile_number) | QUARTILE(A2:A100, 3) | |
QUARTILE.EXC | Returns a value nearest to a specified quartile of a data set exclusive of 0 and 4. | QUARTILE.EXC(data, quartile_number) | QUARTILE.EXC(A2:A100, 3) |
QUARTILE.INC | Returns a value nearest to a specified quartile of a dataset. | QUARTILE.INC(data, quartile_number) | QUARTILE.INC(A2:A100, 3) |
RANK | Returns the rank of a specified value in a dataset. | RANK(value, data, [is_ascending]) | RANK(42, A2:A100, 1) |
Average rank of a specified value in a dataset | RANK.AVG(value, data, [is_ascending]) | RANK.AVG(42, A1:A100, TRUE) | |
Top rank of a specified value in a dataset | RANK.EQ(value, data, [is_ascending]) | RANK.EQ(42, A1:A100, TRUE) | |
RSQ | Square of the correlation coefficient. | RSQ(data_y, data_x) | RSQ(A2:A100, B2:B100) |
SKEW | Calculates the skewness of a dataset, which describes the symmetry of that dataset about the mean. | SKEW(value1, [value2, ...]) | SKEW(1, 2, 3) |
SKEW.P | Calculates the skewness of a dataset, which describes the symmetry of that dataset about the mean. This assumes the dataset is for the population. | SKEW.P(value1, [value2, ...]) | SKEW.P(1, 2, 3) |
SLOPE | Slope of line from linear regression of data. | SLOPE(data_y, data_x) | SLOPE(A2:A100, B2:B100) |
Returns the nth smallest element from a dataset, where n is user-defined. | SMALL(data, n) | SMALL(A2:B100, 4) | |
STANDARDIZE | Calculates the normalised equivalent of a random variable, given the mean and standard deviation of the distribution. | STANDARDIZE(value, mean, standard_deviation) | STANDARDIZE(96, 80, 6.7) |
STDEV | Calculates the standard deviation based on a sample. | STDEV(value1, [value2, ...]) | STDEV(1, 2) |
STDEV.P | Standard deviation of an entire population. | STDEV.P(value1, [value2, ...]) | STDEV.P(1, 2) |
STDEV.S | Standard deviation. | STDEV.S(value1, [value2, ...]) | STDEV.S(1, 2) |
STDEVA | Standard deviation of sample (text as 0). | STDEVA(value1, [value2, ...]) | STDEVA(1, 2) |
STDEVP | Calculates the standard deviation based on an entire population. | STDEVP(value1, [value2, ...]) | STDEVP(1, 2) |
STDEVPA | Standard deviation of entire population (text as 0). | STDEVPA(value1, [value2, ...]) | STDEVPA(1, 2) |
STEYX | Standard error of predicted y-values in regression. | STEYX(data_y, data_x) | STEYX(A2:A100, B2:B100) |
T.DIST | The left-tailed student's t-distribution | T.DIST(x, degrees_freedom, cumulative) | T.DIST(1, 30, TRUE) |
T.DIST.2T | The two-tailed student's t-distribution | T.DIST.2T(x, degrees_freedom) | T.DIST.2T(1, 30) |
T.DIST.RT | The right-tailed student's t-distribution | T.DIST.RT(x, degrees_freedom) | T.DIST.RT(1, 30) |
T.INV | Estimates the negative inverse of the one-tailed TDIST function. | T.INV(probability, degrees_freedom) | T.INV(0.35, 1) |
T.INV.2T | Calculates the inverse of the two-tailed TDIST function. | T.INV.2T(probability, degrees_freedom) | T.INV.2T(0.35, 1) |
T.TEST | Returns the probability associated with t-test. | T.TEST(range1, range2, tails, type) | T.TEST(A1:A4, B1:B4, 2, 1) |
TDIST | Student's t-distribution | TDIST(x, degrees_freedom, tails) | TDIST(1, 30, 1) |
TINV | Inverse t-distribution | TINV(probability, degrees_freedom) | TINV(0.35, 1) |
TREND | Fits points to linear trend derived via least squares | TREND(known_data_y, [known_data_x], [new_data_x], [b]) | TREND(B2:B10, A2:A10, A11:A13, TRUE) |
Calculates the mean of a dataset excluding some proportion of data from the high and low ends of the dataset. | TRIMMEAN(data, exclude_proportion) | TRIMMEAN(A2:A100, 0.1) | |
TTEST | Returns the probability associated with t-test. | TTEST(range1, range2, tails, type) | TTEST(A1:A4, B1:B4, 2, 1) |
VAR | Calculates the variance based on a sample. | VAR(value1, [value2, ...]) | VAR(1, 2) |
VAR.P | Calculates the variance based on an entire population. | VAR.P(value1, [value2, ...]) | VAR.P(1, 2) |
VAR.S | Calculates the variance based on a sample. | VAR.S(value1, [value2, ...]) | VAR.S(1, 2) |
VARA | Variance of sample (text as 0). | VARA(value1, [value2, ...]) | VARA(1, 2) |
VARP | Calculates the variance based on an entire population. | VARP(value1, [value2, ...]) | VARP(1, 2) |
VARPA | Variance of entire population (text as 0). | VARPA(value1, [value2, ...]) | VARPA(1, 2) |
WEIBULL | Weibull distribution function. | WEIBULL(x, shape, scale, cumulative) | WEIBULL(2.4, 2, 3, TRUE) |
WEIBULL.DIST | Weibull distribution function. | WEIBULL.DIST(x, shape, scale, cumulative) | WEIBULL.DIST(2.4, 2, 3, TRUE) |
Z.TEST | One-tailed p-value of a Z-test. | Z.TEST(data, value, [standard_deviation ]) | Z.TEST(A2:A100, 95, 1.2) |
ZTEST | One-tailed p-value of a Z-test. | ZTEST(data, value, [standard_deviation ]) | ZTEST(A2:A100, 95, 1.2) |
Text Functions
Function | Description | Syntax | Example |
ARRAYTOTEXT | The ARRAYTOTEXT function returns an array of text values from any specified range. It passes text values unchanged, and converts non-text values to text. | ARRAYTOTEXT(array, [format]) | ARRAYTOTEXT(A2:B4, 0) |
ASC | Converts full-width ASCII to their half-width counterparts. All standard-width characters will remain unchanged. | ASC(text) | ASC("カタカナ") |
BAHTTEXT | Converts a number to Thai text and adds a suffix of "Baht" | BAHTTEXT(Number) | BAHTTEXT (10) |
CHAR | Converts a number into a character according to the current Unicode table. | CHAR(number) | CHAR(97) |
Returns the text with the non-printable ASCII characters removed. | CLEAN(text) | CLEAN("AF"&CHAR(31)) | |
CODE | Returns the numerical Unicode map value of the first character in the string provided. | CODE(string) | CODE("a") |
DBCS | The function described in this Help topic converts half-width (single-byte) letters within a character string to full-width (double-byte) characters | DBCS(text) | DBCS("Sheet") |
DOLLAR | Formats a number into the currency specific to your spreadsheet locale. | DOLLAR(number, [number_of_places]) | DOLLAR(1.2351, 4) |
ENCODEURL | Returns a URL-encoded string based on the given text. Replacing certain non-alphanumeric characters with percentage signs (%) and hexadecimal digits. | ENCODEURL(text) | |
Tests whether two strings are identical. | EXACT(string1, string2) | EXACT("Hello", "Goodbye") | |
FIND | Returns the position at which a string is first found within text, case-sensitive. Returns #VALUE! if the string is not found. | FIND(search_for, text_to_search, [starting_at]) | FIND("def", "abcdefg", 2) |
FINDB | Returns the position at which a string is first found within text counting each double-character as 2. | FINDB(search_for, text_to_search, [starting_at]) | FINDB("Sheets", "online Sheets", 2) |
Formats a number with a fixed number of decimal places. | FIXED(number, [number_of_places], [suppress_separator]) | FIXED(966364281, 4, TRUE) | |
HYPERLINK | Creates a hyperlink inside a cell. | HYPERLINK(url, [link_label]) | HYPERLINK("https://www.example.com/", "Example") |
JIS | The function described in this Help topic converts half-width (single-byte) letters within a character string to full-width (double-byte) characters. | JIS(text) | JIS("Sheet") |
JOIN | Concatenates the elements of one or more one-dimensional arrays using a specified delimiter. | JOIN(delimiter, value_or_array1, [value_or_array2, ...]) | JOIN(" and-a ",{1,2,"1 2 3 4"}) |
LEFT | Returns a substring from the beginning of a specified string. | LEFT(string, [number_of_characters]) | LEFT("Sheets", 2) |
LEFTB | The LEFTB function returns the left portion of a string up to a certain number of bytes. | LEFTB(string, [num_of_bytes]) | LEFTB("Sheets", 2) |
LEN | Returns the length of a string. | LEN(text) | LEN("Sheets") |
LENB | Returns the number of characters of the given text. | LENB(text) | LENB("Hello") |
Converts a specified string to lowercase. | LOWER(text) | LOWER("LOREM IPSUM") | |
MID | Returns a segment of a string. | MID(string, starting_at, extract_length) | MID("Sheets", 1, 2) |
MIDB | The MIDB function returns a section of a string starting at a given character and up to a specified number of bytes. | MIDB(string, starting_at, extract_length_bytes) | MIDB("Sheets", 1, 2) |
NUMBERVALUE | Converts text to a number, in a locale-independent way | NUMBERVALUE(Text, [Decimal_separator], [Group_separator]) | NUMBERVALUE("3.5%") |
Capitalises each word in a specified string. | PROPER(text_to_capitalize) | PROPER("sheets") | |
To determine whether a piece of text matches regular expression. | REGEXMATCH(Text, regular_expression) | REGEXMATCH("Sheets", "S.e") | |
Replace text using regular expressions. | REGEXREPLACE(Text, regular_expression, replacement) | REGEXREPLACE("abcedfg", "a.*d", "xyz") | |
Replaces part of a text string with a different text string. | REPLACE(text, position, length, new_text) | REPLACE("abcdefg", 1, 6, "xyz") | |
REPLACEB | The REPLACEB function replaces part of a text string, based on a number of bytes, with a different text string. | REPLACEB(text, position, num_bytes, new_text) | REPLACEB("online Sheets", 1, 2, "Smart") |
REPT | Returns specified text repeated a number of times. | REPT(text_to_repeat, number_of_repetitions) | REPT("ha",4) |
Returns a substring from the end of a specified string. | RIGHT(string, [number_of_characters]) | RIGHT("Sheets", 2) | |
RIGHTB | The RIGHTB function returns the right portion of a string up to a certain number of bytes. | RIGHTB(string, [num_of_bytes]) | RIGHTB("online Sheets", 2) |
Returns the position at which a string is first found within text, case-insensitive. Returns #VALUE! if the string is not found. | SEARCH(search_for, text_to_search, [starting_at]) | SEARCH("def", "abcdefg", 2) | |
SEARCHB | Returns the position at which a string is first found within text counting each double-character as 2. | SEARCHB(search_for, text_to_search, [starting_at]) | SEARCHB("Sheets", "online Sheets", 2) |
Divides text around a specified character or string, and puts each fragment into a separate cell in the row. | SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) | SPLIT("1,2,3", ",") | |
Replaces existing text with new text in a string. | SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) | SUBSTITUTE("abcdefg", "cde", "xyz", 1) | |
T | Returns string arguments as text. | T(value) | T(A2) |
TEXT | Converts a value into text according to a specified format. | TEXT(value, format) | TEXT(43663, "MM/DD/YYYY") |
TEXTAFTER | Returns text that occurs after a given character or string | TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | TEXTAFTER("powerful Sheets","powerful") |
TEXTBEFORE | Returns text that occurs before a given character or string | TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | TEXTBEFORE("powerful Sheets","Sheets") |
Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts. | TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...]) | TEXTJOIN(" ", TRUE, "Hello", "Goodbye") | |
TEXTSPLIT | Splits text strings by using column and row delimiters | TEXTSPLIT(text , col_delimiter, [row_delimiter ], [ignore_empty], [match_mode], [pad_with]) | TEXTSPLIT("Apple, Banana",",") |
TO_DATE | Converts a provided number to a date | TO_DATE(value) | TO_DATE(25405) |
TO_DOLLARS | Converts a provided number to a dollar value | TO_DOLLARS(value) | TO_DOLLARS(10,000) |
TO_PERCENT | Converts a provided number to a percentage | TO_PERCENT(value) | TO_PERCENT(0.40826) |
TO_PURE_NUMBER | Converts a provided date/time, percentage, currency or other formatted numerical value to a pure number without formatting. | TO_PURE_NUMBER(value) | TO_PURE_NUMBER(50%) |
TO_TEXT | Converts a provided numerical value to a text value | TO_TEXT(value) | TO_TEXT(24) |
TRIM | Removes leading, trailing, and repeated spaces in text. | TRIM(text) | TRIM(" lorem ipsum") |
UNICHAR | Returns the character value for a decimal Unicode number. | UNICHAR(The Unicode number to convert to a character) | UNICHAR(68) |
UNICODE | Returns the decimal Unicode value of the first character of an input string. | UNICODE(The string containing the character to be evaluated) | UNICODE("A") |
Converts a specified string to uppercase. | UPPER(text) | UPPER("lorem ipsum") | |
Converts a string in any of the date, time or number formats that Sheets understands into a number. | VALUE(text) | VALUE("123") | |
VALUETOTEXT | Returns text from any specified value | VALUETOTEXT(value, [format]) | VALUETOTEXT(A2, 1) |
Financial Functions
Function | Description | Syntax | Example |
ACCRINT | Calculates the accrued interest of a security that has periodic payments. | ACCRINT(issue, first_payment, settlement, rate, redemption, frequency, [day_count_convention], []) | ACCRINT("2010-1-1", "2010-2-1", "2012-12-31", 5%, 100, 4, 0) |
Calculates the accrued interest of a security that pays interest at maturity. | ACCRINTM(issue, settlement, rate, par, [basis]) | ACCRINTM("12/31/1969", "12/31/1999", 5%, 100, 0) | |
AMORLINC | Depreciation for an accounting period. | AMORLINC(cost, purchase_date, first_period_end, salvage, period, rate, [day_count_convention]) | AMORLINC(1000, "1969-7-20", "1969-8-20", 100, 6, 15%, 0) |
Calculates the number of days from the first coupon, or interest payment, until settlement. | COUPDAYBS(settlement, maturity, frequency, [day_count_convention]) | COUPDAYBS("2010-2-1", "2019-12-31", 4, 0) | |
Calculates the number of days in the coupon, or interest payment, period that contains the specified settlement date. | COUPDAYS(settlement, maturity, frequency, [day_count_convention]) | COUPDAYS("2010-2-1", "2019-12-31", 4, 0) | |
Calculates the number of days from the settlement date until the next coupon, or interest payment. | COUPDAYSNC(settlement, maturity, frequency, [day_count_convention]) | COUPDAYSNC("2010-2-1", "2019-12-31", 4, 0) | |
Calculates next coupon, or interest payment, date after the settlement date. | COUPNCD(settlement, maturity, frequency, [day_count_convention]) | COUPNCD("2010-2-1", "2019-12-31", 4, 0) | |
Calculates the number of coupons, or interest payments, between the settlement date and the maturity date of the investment. | COUPNUM(settlement, maturity, frequency, [day_count_convention]) | COUPNUM("2010-2-1", "2019-12-31", 4, 0) | |
Calculates last coupon, or interest payment, date before the settlement date. | COUPPCD(settlement, maturity, frequency, [day_count_convention]) | COUPPCD("2010-2-1", "2019-12-31", 4, 0) | |
CUMIPMT | Calculates the cumulative interest over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. | CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning) | CUMIPMT(1.2%, 12, 100, 1, 5, 0) |
Calculates the cumulative principal paid over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. | CUMPRINC(rate, number_of_period, present_value, first_period, last_period, end_or_beginning) | CUMPRINC(12%, 12, 100, 1, 5, 0) | |
DB | Depreciation via declining balance method. | DB(cost, salvage, life, period, [month]) | DB(100, 50, 10, 2, 10) |
DDB | Depreciation via double-declining balance method. | DDB(cost, salvage, life, period, [factor]) | DDB(100, 50, 10, 2, 2.25) |
DISC | Calculates the discount rate of a security based on price. | DISC(settlement, maturity, price, redemption, [day_count_convention]) | DISC("2010-1-2", "2039-12-31", 90, 100, 0) |
DOLLARDE | Converts a price quotation given as a decimal fraction into a decimal value. | DOLLARDE(fractional_price, unit) | DOLLARDE(100.1, 32) |
DOLLARFR | Converts a price quotation given as a decimal value into a decimal fraction. | DOLLARFR(decimal_price, unit) | DOLLARFR(100.125,32) |
DURATION | Calculates the number of compounding periods required for an investment of a specified present value appreciating at a given rate to reach a target value. | DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) | DURATION("1/2/2010", "12/31/2039", 3%, 1.2, 2, 0) |
Calculates the annual effective interest rate given the nominal rate and number of compounding periods per year. | EFFECT(nominal_rate, periods_per_year) | EFFECT(3%, 12) | |
FV | Returns the future value of an investment | FV(rate, nper, pmt, [pv], [type]) | FV(2%, 12, 100, 400, 0) |
Calculates the future value of some principal based on a specified series of potentially varying interest rates. | FVSCHEDULE(principal, rate_schedule) | FVSCHEDULE(10000, A2:A100) | |
INTRATE | Calculates the effective interest rate generated when an investment is purchased at one price and sold at another with no interest or dividends generated by the investment itself. | INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention]) | INTRATE(DATE(2010,01,02),DATE(2019,12,31),90,140,2) |
IPMT | Calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate. | IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) | IPMT(1%, 1, 360, 100000, 30000, 0) |
IRR | Calculates the internal rate of return on an investment based on a series of periodic cash flows. | IRR(cashflow_amounts, [rate_guess]) | IRR(A2:A25, 1%) |
ISPMT | Returns the interest paid at a particular period of an investment. | ISPMT(rate, period, number_of_periods, present_value) | ISPMT(15%, 2, 5, 1000) |
MDURATION | Calculates the modified Macaulay duration of a security paying periodic interest based on expected yield. | MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) | MDURATION("1/2/2010", "12/31/2039", 3%, 1.2%, 2, 0) |
MIRR | Calculates the modified internal rate of return on an investment based on a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income. | MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate) | MIRR(A2:A25, 8%, 11%) |
Calculates the annual nominal interest rate given the effective rate and number of compounding periods per year. | NOMINAL(effective_rate, periods_per_year) | NOMINAL(4%, 12) | |
NPER | Calculates the number of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. | NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning]) | NPER(2%, -50, 1000, 2000, 0) |
NPV | Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate. | NPV(discount, cashflow1, [cashflow2, ...]) | NPV(8%, 200, 250) |
ODDFPRICE | Returns the yield of a security that has an odd (short or long) first period. | ODDFPRICE(Settlement, maturity, Issue, First_coupon, rate, yield, redemption, frequency, [day_count_convention]) | ODDFPRICE("2008/11/11", "2021/3/1", "2008/10/15", "2009/3/1", 5.75%, 6%, 100, 2, 1) |
ODDFYIELD | Returns the yield of a security that has an odd (short or long) first period | ODDFYIELD(Settlement, maturity, Issue, First_coupon, rate, price, redemption, frequency, [day_count_convention]) | ODDFYIELD("2008/11/11", "2021/3/1", "2008/10/15", "2009/3/1", 5.75%, 84.5, 100, 2, 1) |
ODDLPRICE | Returns the price per $100 face value of a security having an odd (short or long) last coupon period | ODDLPRICE(Settlement, maturity, Last_interest, rate, yield, redemption, frequency, [day_count_convention]) | ODDLPRICE("2008-2-7", "2008-6-15", "2007-12-24", 3.75%, 4.05%, 100, 2, 0) |
ODDLYIELD | Returns the yield of a security that has an odd (short or long) last period | ODDLYIELD(Settlement, maturity, Last_interest, rate, price, redemption, frequency, [day_count_convention]) | ODDLYIELD("2008-4-20", "2008-6-15", "2007-12-24", 3.75%, 998, 100, 2, 0) |
PDURATION | Computes the number of periods needed for an investment to reach a specific value | PDURATION(rate, present_value, future_value) | PDURATION(0.25, 100, 200) |
PMT | Returns the periodic payment for an annuity | PMT(rate, nper, pv, [fv], [type]) | PMT(1%, 360, 100000, 0, 0) |
PPMT | Calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate. | PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) | PPMT(1%, 1, 360, 100000, 0, 0) |
PRICE | Calculates the price of a security paying periodic interest based on expected yield. | PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention]) | PRICE("2010-1-2", "2039-12-31", 3%, 1.2, 100, 2, 0) |
PRICEDISC | Calculates the price of a discount (non-interest-bearing) security, based on expected yield. | PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention - [optional]]) | PRICEDISC("2010-1-2", "2039-12-31", 3%, 100, 0) |
PRICEMAT | Calculates the price of a security paying interest at maturity, based on expected yield. | PRICEMAT(settlement, maturity, issue, rate, yield, [day_count_convention]) | PRICEMAT("2010-1-2", "2039-12-31", "2010-1-1", 3, 1.2, 0) |
PV | Returns the present value of an investment | PV(rate, nper, pmt, [fv], [type]) | PV(2%, 12, 100, 0, 0) |
RATE | Calculates the interest rate of an annuity investment based on constant-amount periodic payments and the assumption of a constant interest rate. | RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess]) | RATE(12, -100, 400, 0, 0, 1%) |
RECEIVED | Calculates the amount received at maturity for an investment in fixed-income securities purchased on a given date. | RECEIVED(settlement, maturity, investment, discount, [day_count_convention]) | RECEIVED("2010-2-1", "2019-12-31", 1000, 0.05, 0) |
RRI | Computes the rate needed for an investment to reach a specific value within a specific number of periods. | RRI(number_of_periods, present_value, future_value) | RRI(3.25, 100, 200) |
SLN | Calculates the depreciation of an asset for one period using the straight-line method. | SLN(cost, salvage, life) | SLN(100, 50, 10) |
SYD | Depreciation via sum of years digits method. | SYD(cost, salvage, life, period) | SYD(100, 50, 10, 2) |
TBILLEQ | Calculates the equivalent annualized rate of return of a US Treasury Bill based on discount rate. | TBILLEQ(settlement, maturity, discount) | TBILLEQ("2010-1-2", "2010-12-31", 9%) |
TBILLPRICE | Calculates the price of a US Treasury Bill based on discount rate. | TBILLPRICE(settlement, maturity, discount) | TBILLPRICE("2010-1-2", "2010-12-31", 2.5%) |
TBILLYIELD | Calculates the yield of a US Treasury Bill based on price. | TBILLYIELD(settlement, maturity, price) | TBILLYIELD("2010-1-2", "2010-12-31", 98.45) |
VDB | Variable declining balance. | VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) | VDB(100, 10, 20, 10, 11, 2, TRUE) |
XIRR | Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows. | XIRR(cashflow_amounts, cashflow_dates, [rate_guess]) | XIRR(B2:B25, C2:C25, 1%) |
XNPV | Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate. | XNPV(discount, cashflow_amounts, cashflow_dates) | XNPV(8%, B2:B25, C2:C25) |
YIELD | Calculates the annual yield of a security paying periodic interest, such as a US Treasury bond, based on price. | YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention]) | YIELD("2010-1-2", "2039-12-31", 3%, 93.45, 100, 2, 0) |
YIELDDISC | Calculates the annual yield of a discount (non-interest bearing) security, based on price. | YIELDDISC(settlement, maturity, price, redemption, [day_count_convention]) | YIELDDISC("2010-1-2", "2010-12-31", 98.45, 100, 0) |
YIELDMAT | Calculates the annual yield of a security paying interest at maturity, based on price. | YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention ]) | YIELDMAT("2010-3-1", "2010-12-31", "2010-1-2", 3%, 98.45, 0) |
Engineering Functions
Function | Description | Syntax | Example |
BESSELI | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments. | BESSELI(X, N) | BESSELI(1.5, 1) |
BESSELJ | Returns the Bessel function | BESSELJ(X, N) | BESSELJ(1.9, 2) |
BESSELK | Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments. | BESSELK(X, N) | BESSELK(1.5, 1) |
BESSELY | Returns the Bessel function, which is also called the Weber function or the Neumann function. | BESSELY(X, N) | BESSELY(2.5, 1) |
Converts a signed binary number to decimal format. | BIN2DEC(signed_binary_number) | BIN2DEC(101) | |
Converts a signed binary number to signed hexadecimal format. | BIN2HEX(signed_binary_number, [significant_digits]) | BIN2HEX(101, 8) | |
Converts a signed binary number to signed octal format. | BIN2OCT(signed_binary_number, [significant_digits]) | BIN2OCT(101, 8) | |
BITAND | Bitwise boolean AND of two numbers. | BITAND(value1, value2) | BITAND(9, 5) |
BITLSHIFT | Shifts the bits of the input a certain number of places to the left. | BITLSHIFT(value, shift_amount) | BITLSHIFT(9, 2) |
BITOR | Bitwise boolean OR of two numbers. | BITOR(value1, value2) | BITOR(9, 5) |
BITRSHIFT | Shifts the bits of the input a certain number of places to the right. | BITRSHIFT(value, shift_amount) | BITRSHIFT(18, 2) |
BITXOR | Bitwise boolean XOR (exclusive or) of two numbers. | BITXOR(value1, value2) | BITXOR(9, 5) |
Creates a complex number, given real and imaginary coefficients. | COMPLEX(real_part, imaginary_part, [suffix]) | COMPLEX(1, 2, "j") | |
Converts a decimal number to signed binary format. | DEC2BIN(decimal_number, [significant_digits]) | DEC2BIN("100", 8) | |
Converts a decimal number to signed hexadecimal format. | DEC2HEX(decimal_number, [significant_digits]) | DEC2HEX(100, 8) | |
Converts a decimal number to signed octal format. | DEC2OCT(decimal_number, [significant_digits]) | DEC2OCT("100", 8) | |
Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. | DELTA(number1, [number2]) | DELTA(5, 4) | |
ERF | Returns the integral of the Gauss error function over an interval of values. | ERF(z1, [z2]) | ERF(-2.3, -0.7) |
Returns the integral of the Gauss error function over an interval of values, rounded up to the nearest integer or to the nearest multiple of significance. | ERF.PRECISE(x) | ERF.PRECISE(0.745) | |
ERFC | Returns the complementary Gauss error function of a value. | ERFC(z) | ERFC(2) |
ERFC.PRECISE | Returns the complementary Gauss error function of a value, rounded up to the nearest integer or to the nearest multiple of significance. | ERFC.PRECISE(z) | ERFC.PRECISE(2) |
Returns 1 if the rate is strictly greater than or equal to the provided step value, or 0 otherwise. If no step value is provided, then the default value of 0 will be used. | GESTEP(value, [step]) | GESTEP(5, 2) | |
HEX2BIN | Converts a signed hexadecimal number to signed binary format. | HEX2BIN(signed_hexadecimal_number, [significant_digits]) | HEX2BIN("f3", 8) |
HEX2DEC | The HEX2DEC function converts a signed hexadecimal number to decimal format. | HEX2DEC(signed_hexadecimal_number) | HEX2DEC("f3") |
HEX2OCT | Converts a signed hexadecimal number to signed octal format. | HEX2OCT(signed_hexadecimal_number, [significant_digits]) | HEX2OCT("f3", 8) |
Returns the absolute value (or modulus) of a complex number. | IMABS(number) | IMABS("3+4i") | |
Returns the imaginary coefficient of a complex number. | IMAGINARY(complex_number) | IMAGINARY("4+9i") | |
Returns the argument (theta) of a complex number, an angle expressed in radians. | IMARGUMENT(number) | IMARGUMENT("2+3i") | |
Returns the complex conjugate of a number. | IMCONJUGATE(number) | IMCONJUGATE("4+9i") | |
Returns the cosine of a complex number in x + yi or x + yj text format. | IMCOS(number) | IMCOS("2+3i") | |
IMCOSH | Returns the hyperbolic cosine of a complex number. | IMCOSH(number) | IMCOSH("2+3i") |
IMCOT | Returns the cotangent of a complex number. | IMCOT(number) | IMCOT("2+3i") |
IMCOTH | Returns the hyperbolic cotangent of a complex number. | IMCOTH(number) | IMCOTH("2+3i") |
IMCSC | Returns the cosecant of a complex number. | IMCSC(number) | IMCSC("2+3i") |
IMCSCH | Returns the hyperbolic cosecant of a complex number. | IMCSCH(number) | IMCSCH("2+3i") |
Returns one complex number divided by another. | IMDIV(dividend, divisor) | IMDIV("11+16i", "3+2i") | |
Returns Euler's number, e (~2.718) raised to a complex power. | IMEXP(exponent) | IMEXP("2+3i") | |
IMLN | Returns the logarithm of a complex number, base e (Euler's number). | IMLN(complex_value) | IMLN("2+3i") |
IMLOG | The logarithm of an imaginary number. | IMLOG(value, base) | IMLOG("100 + 10i", 10) |
Returns the base 10 logarithm of an imaginary number. | IMLOG10(value) | IMLOG10("100 + 10i") | |
Returns the base 2 logarithm of an imaginary number. | IMLOG2(value) | IMLOG2("100 + 10i") | |
IMPOWER | Returns a complex number raised to a power. | IMPOWER(complex_base, exponent) | IMPOWER("2+3i", 1.7) |
Returns the result of multiplying a series of complex numbers together. | IMPRODUCT(factor1, [factor2, ...]) | IMPRODUCT(A2:A100, "4+3i") | |
IMREAL | The real coefficient of a complex number. | IMREAL(complex_number ) | IMREAL("2+3i") |
IMSEC | Returns the secant of a complex number. | IMSEC(number) | IMSEC("2+3i") |
IMSECH | Returns the hyperbolic secant of a complex number. | IMSECH(number) | IMSECH("2+3i") |
IMSIN | Returns the sine of a complex number. | IMSIN(number) | IMSIN("2+3i") |
IMSINH | Returns the hyperbolic sine of a complex number. | IMSINH(number) | IMSINH("2+3i") |
IMSQRT | The square root of a complex number. | IMSQRT(complex_number) | IMSQRT("2+3i") |
IMSUB | Returns the difference of two complex numbers in x + yi or x + yj text format. | IMSUB(inumber1, inumber2) | IMSUB("4+9i", "3+2i") |
Returns the sum of a series of complex numbers. | IMSUM(value1, [value2, ...]) | IMSUM(A2:A100, "4+3i") | |
IMTAN | Returns the hyperbolic sine of a complex number. | IMTAN(number) | IMTAN("2+3i") |
IMTANH | Returns the hyperbolic tangent of a complex number. | IMTANH(number) | IMTANH("2+3i") |
OCT2BIN | Converts a signed octal number to signed binary format. | OCT2BIN(signed_octal_number, [significant_digits]) | OCT2BIN(37, 8) |
OCT2DEC | Converts a signed octal number to decimal format. | OCT2DEC(signed_octal_number) | OCT2DEC(37) |
OCT2HEX | Converts a decimal number to signed hexadecimal format. | OCT2HEX(signed_octal_number, [significant_digits]) | OCT2HEX(37, 8) |
Practical Functions
Function | Description | Syntax | Example |
Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays. | ARRAYFORMULA(array_formula) | ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0))) | |
Constrains an array result to a specified size. | ARRAY_CONSTRAIN(input_range, num_rows, num_cols) | ARRAY_CONSTRAIN(A1:C10, 2, 3) | |
Inserts an image into a cell | IMAGE(URL, [mode], [width], [height]) | IMAGE("https://www.example.com/image.png", 4, 50, 101) | |
Imports data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) format. | IMPORTDATA(URL, [delimiter], [locale]) | IMPORTDATA("http://www.example.com/data.csv", ",", "en_US") | |
Imports an RSS or Atom feed | IMPORTFEED(URL, [query], [headers], [num_items]) | IMPORTFEED("http://news.example.com", "items", FALSE, 11) | |
Imports data from a table or list within an HTML page | IMPORTHTML(URL, query, index, [locale]) | IMPORTHTML("http://www.example.com", "table", 5, "en_US") | |
Imports data from any of various structured data types including XML, HTML, CSV, TSV and RSS and Atom XML feeds. | IMPORTXML(URL, xpath_query, [locale]) | IMPORTXML("https://www.example.com", "//a/@href", "en_US") | |
Use SQL-like syntax to find, sort, and verify your data. | QUERY(range, [query], [headers]) | QUERY(A1:D10, "select A where B='Market'") | |
Returns matching substrings with regular expression. | REGEXEXTRACT(Text, regular_expression) | REGEXEXTRACT("abcedfg", "c.*f") | |
Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range. | UNIQUE(range, [[by_col]], [[exactly_once]]) | UNIQUE(A2:B26) | |
searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | XLOOKUP(F2, B2:B11, D2:D11) |