Based on https://support.google.com/docs/table/25273?hl=en
Name | Syntax | Description |
---|---|---|
ARRAY_CONSTRAIN | ARRAY_CONSTRAIN(input_range, num_rows, num_cols) |
Constrains an array result to a specified size. |
BYCOL | BYCOL(array_or_range, LAMBDA) |
Groups an array by columns by application of a LAMBDA function to each column. |
BYROW | BYROW(array_or_range, LAMBDA) |
Groups an array by rows by application of a LAMBDA function to each row. |
FLATTEN | FLATTEN(range1,[range2,...]) |
Flattens all the values from one or more ranges into a single column. |
FREQUENCY | FREQUENCY(data, classes) |
Calculates the frequency distribution of a one-column array into specified classes. |
GROWTH | GROWTH(known_data_y, [known_data_x], [new_data_x], [b]) |
Given partial data about an exponential growth trend, fits an ideal exponential growth trend and/or predicts further values. |
LINEST | LINEST(known_data_y, [known_data_x], [calculate_b], [verbose]) |
Given partial data about a linear trend, calculates various parameters about the ideal linear trend using the least-squares method. |
LOGEST | LOGEST(known_data_y, [known_data_x], [b], [verbose]) |
Given partial data about an exponential growth curve, calculates various parameters about the best fit ideal exponential growth curve. |
MAKEARRAY | MAKEARRAY(rows, columns, LAMBDA) |
Returns an array of specified dimensions with values calculated by application of a LAMBDA function. |
MAP | MAP(array1, [array2, ...], LAMBDA) |
Maps each value in the given arrays to a new value by application of a LAMBDA function to each value. |
MDETERM | MDETERM(square_matrix) |
Returns the matrix determinant of a square matrix specified as an array or range. |
MINVERSE | MINVERSE(square_matrix) |
Returns the multiplicative inverse of a square matrix specified as an array or range. |
MMULT | MMULT(matrix1, matrix2) |
Calculates the matrix product of two matrices specified as arrays or ranges. |
REDUCE | REDUCE(initial_value, array_or_range, LAMBDA) |
Reduces an array to an accumulated result by application of a LAMBDA function to each value. |
SCAN | SCAN(initial_value, array_or_range, LAMBDA) |
Scans an array and produces intermediate values by application of a LAMBDA function to each value. Returns an array of the intermediate values obtained at each step. |
SUMPRODUCT | SUMPRODUCT(array1, [array2, ...]) |
Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges. |
SUMX2MY2 | SUMX2MY2(array_x, array_y) |
Calculates the sum of the differences of the squares of values in two arrays. |
SUMX2PY2 | SUMX2PY2(array_x, array_y) |
Calculates the sum of the sums of the squares of values in two arrays. |
SUMXMY2 | SUMXMY2(array_x, array_y) |
Calculates the sum of the squares of differences of values in two arrays. |
TRANSPOSE | TRANSPOSE(array_or_range) |
Transposes the rows and columns of an array or range of cells. |
TREND | TREND(known_data_y, [known_data_x], [new_data_x], [b]) |
Given partial data about a linear trend, fits an ideal linear trend using the least squares method and/or predicts further values. |
Name | Syntax | Description |
---|---|---|
DAVERAGE | DAVERAGE(database, field, criteria) |
Returns the average of a set of values selected from a database table-like array or range using a SQL-like query. |
DCOUNT | DCOUNT(database, field, criteria) |
Counts numeric values selected from a database table-like array or range using a SQL-like query. |
DCOUNTA | DCOUNTA(database, field, criteria) |
Counts values, including text, selected from a database table-like array or range using a SQL-like query. |
DGET | DGET(database, field, criteria) |
Returns a single value from a database table-like array or range using a SQL-like query. |
DMAX | DMAX(database, field, criteria) |
Returns the maximum value selected from a database table-like array or range using a SQL-like query. |
DMIN | DMIN(database, field, criteria) |
Returns the minimum value selected from a database table-like array or range using a SQL-like query. |
DPRODUCT | DPRODUCT(database, field, criteria) |
Returns the product of values selected from a database table-like array or range using a SQL-like query. |
DSTDEV | DSTDEV(database, field, criteria) |
Returns the standard deviation of a population sample selected from a database table-like array or range using a SQL-like query. |
DSTDEVP | DSTDEVP(database, field, criteria) |
Returns the standard deviation of an entire population selected from a database table-like array or range using a SQL-like query. |
DSUM | DSUM(database, field, criteria) |
Returns the sum of values selected from a database table-like array or range using a SQL-like query. |
DVAR | DVAR(database, field, criteria) |
Returns the variance of a population sample selected from a database table-like array or range using a SQL-like query. |
DVARP | DVARP(database, field, criteria) |
Returns the variance of an entire population selected from a database table-like array or range using a SQL-like query. |
Name | Syntax | Description |
---|---|---|
DATE | DATE(year, month, day) |
Converts a provided year, month, and day into a date. |
DATEDIF | DATEDIF(start_date, end_date, unit) |
Calculates the number of days, months, or years between two dates. |
DATEVALUE | DATEVALUE(date_string) |
Converts a provided date string in a known format to a date value. |
DAY | DAY(date) |
Returns the day of the month that a specific date falls on, in numeric format. |
DAYS | DAYS(end_date, start_date) |
Returns the number of days between two dates. |
DAYS360 | DAYS360(start_date, end_date, [method]) |
Returns the difference between two days based on the 360 day year used in some financial interest calculations. |
EDATE | EDATE(start_date, months) |
Returns a date a specified number of months before or after another date. |
EOMONTH | EOMONTH(start_date, months) |
Returns a date representing the last day of a month which falls a specified number of months before or after another date. |
HOUR | HOUR(time) |
Returns the hour component of a specific time, in numeric format. |
ISOWEEKNUM | ISOWEEKNUM(date) |
Returns the number of the ISO week of the year where the provided date falls. |
MINUTE | MINUTE(time) |
Returns the minute component of a specific time, in numeric format. |
MONTH | MONTH(date) |
Returns the month of the year a specific date falls in, in numeric format. |
NETWORKDAYS | NETWORKDAYS(start_date, end_date, [holidays]) |
Returns the number of net working days between two provided days. |
NETWORKDAYS.INTL | NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) |
Returns the number of net working days between two provided days excluding specified weekend days and holidays. |
NOW | NOW() |
Returns the current date and time as a date value. |
SECOND | SECOND(time) |
Returns the second component of a specific time, in numeric format. |
TIME | TIME(hour, minute, second) |
Converts a provided hour, minute, and second into a time. |
TIMEVALUE | TIMEVALUE(time_string) |
Returns the fraction of a 24-hour day the time represents. |
TODAY | TODAY() |
Returns the current date as a date value. |
WEEKDAY | WEEKDAY(date, [type]) |
Returns a number representing the day of the week of the date provided. |
WEEKNUM | WEEKNUM(date, [type]) |
Returns a number representing the week of the year where the provided date falls. |
WORKDAY | WORKDAY(start_date, num_days, [holidays]) |
Calculates the end date after a specified number of working days. |
WORKDAY.INTL | WORKDAY.INTL(start_date, num_days, [weekend], [holidays]) |
Calculates the date after a specified number of workdays excluding specified weekend days and holidays. |
YEAR | YEAR(date) |
Returns the year specified by a given date. |
YEARFRAC | YEARFRAC(start_date, end_date, [day_count_convention]) |
Returns the number of years, including fractional years, between two dates using a specified day count convention. |
Name | Syntax | Description |
---|---|---|
BIN2DEC | BIN2DEC(signed_binary_number) |
Converts a signed binary number to decimal format. |
BIN2HEX | BIN2HEX(signed_binary_number, [significant_digits]) |
Converts a signed binary number to signed hexadecimal format. |
BIN2OCT | BIN2OCT(signed_binary_number, [significant_digits]) |
Converts a signed binary number to signed octal format. |
BITAND | BITAND(value1, value2) |
Bitwise boolean AND of two numbers. |
BITLSHIFT | BITLSHIFT(value, shift_amount) |
Shifts the bits of the input a certain number of places to the left. |
BITOR | BITOR(value1, value2) |
Bitwise boolean OR of 2 numbers. |
BITRSHIFT | BITRSHIFT(value, shift_amount) |
Shifts the bits of the input a certain number of places to the right. |
BITXOR | BITXOR(value1, value2) |
Bitwise XOR (exclusive OR) of 2 numbers. |
COMPLEX | COMPLEX(real_part, imaginary_part, [suffix]) |
Creates a complex number given real and imaginary coefficients. |
DEC2BIN | DEC2BIN(decimal_number, [significant_digits]) |
Converts a decimal number to signed binary format. |
DEC2HEX | DEC2HEX(decimal_number, [significant_digits]) |
Converts a decimal number to signed hexadecimal format. |
DEC2OCT | DEC2OCT(decimal_number, [significant_digits]) |
Converts a decimal number to signed octal format. |
DELTA | DELTA(number1, [number2]) |
Compare two numeric values, returning 1 if they're equal. |
ERF | ERF(lower_bound, [upper_bound]) |
The ERF function returns the integral of the Gauss error function over an interval of values. . |
ERF.PRECISE | ERF.PRECISE(lower_bound, [upper_bound]) |
See |
GESTEP | GESTEP(value, [step]) |
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. |
HEX2BIN | HEX2BIN(signed_hexadecimal_number, [significant_digits]) |
Converts a signed hexadecimal number to signed binary format. |
HEX2DEC | HEX2DEC(signed_hexadecimal_number) |
Converts a signed hexadecimal number to decimal format. |
HEX2OCT | HEX2OCT(signed_hexadecimal_number, significant_digits) |
Converts a signed hexadecimal number to signed octal format. |
IMABS | IMABS(number) |
Returns absolute value of a complex number. |
IMAGINARY | IMAGINARY(complex_number) |
Returns the imaginary coefficient of a complex number. |
IMARGUMENT | IMARGUMENT(number) |
The IMARGUMENT function returns the angle (also known as the argument or \theta) of the given complex number in radians. . |
IMCONJUGATE | IMCONJUGATE(number) |
Returns the complex conjugate of a number. |
IMCOS | IMCOS(number) |
The IMCOS function returns the cosine of the given complex number. . |
IMCOSH | IMCOSH(number) |
Returns the hyperbolic cosine of the given complex number. For example, a given complex number "x+yi" returns "cosh(x+yi)." . |
IMCOT | IMCOT(number) |
Returns the cotangent of the given complex number. For example, a given complex number "x+yi" returns "cot(x+yi)." . |
IMCOTH | IMCOTH(number) |
Returns the hyperbolic cotangent of the given complex number. For example, a given complex number "x+yi" returns "coth(x+yi)." . |
IMCSC | IMCSC(number) |
Returns the cosecant of the given complex number. . |
IMCSCH | IMCSCH(number) |
Returns the hyperbolic cosecant of the given complex number. For example, a given complex number "x+yi" returns "csch(x+yi)." . |
IMDIV | IMDIV(dividend, divisor) |
Returns one complex number divided by another. |
IMEXP | IMEXP(exponent) |
Returns Euler's number, e (~2.718) raised to a complex power. . |
IMLOG | IMLOG(value, base) |
Returns the logarithm of a complex number for a specified base. . |
IMLOG10 | IMLOG10(value) |
Returns the logarithm of a complex number with base 10. . |
IMLOG2 | IMLOG2(value) |
Returns the logarithm of a complex number with base 2. . |
IMPRODUCT | IMPRODUCT(factor1, [factor2, ...]) |
Returns the result of multiplying a series of complex numbers together. |
IMREAL | IMREAL(complex_number) |
Returns the real coefficient of a complex number. |
IMSEC | IMSEC(number) |
Returns the secant of the given complex number. For example, a given complex number "x+yi" returns "sec(x+yi)." . |
IMSECH | IMSECH(number) |
Returns the hyperbolic secant of the given complex number. For example, a given complex number "x+yi" returns "sech(x+yi)." . |
IMSIN | IMSIN (number) |
Returns the sine of the given complex number. . |
IMSINH | IMSINH(number) |
Returns the hyperbolic sine of the given complex number. For example, a given complex number "x+yi" returns "sinh(x+yi)." . |
IMSUB | IMSUB(first_number, second_number) |
Returns the difference between two complex numbers. |
IMSUM | IMSUM(value1, [value2, ...]) |
Returns the sum of a series of complex numbers. |
IMTAN | IMTAN(number) |
Returns the tangent of the given complex number. . |
IMTANH | IMTANH(number) |
Returns the hyperbolic tangent of the given complex number. For example, a given complex number "x+yi" returns "tanh(x+yi)." . |
OCT2BIN | OCT2BIN(signed_octal_number, [significant_digits]) |
Converts a signed octal number to signed binary format. |
OCT2DEC | OCT2DEC(signed_octal_number) |
Converts a signed octal number to decimal format. |
OCT2HEX | OCT2HEX(signed_octal_number, [significant_digits]) |
Converts a signed octal number to signed hexadecimal format. |
Name | Syntax | Description |
---|---|---|
FILTER | FILTER(range, condition1, [condition2]) |
Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions. |
SORT | SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2]) |
Sorts the rows of a given array or range by the values in one or more columns. |
SORTN | SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...) |
Returns the first n items in a data set after performing a sort. |
UNIQUE | UNIQUE(range) |
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. |
Name | Syntax | Description |
---|---|---|
ACCRINT | ACCRINT(issue, first_payment, settlement, rate, redemption, frequency, [day_count_convention]) |
Calculates the accrued interest of a security that has periodic payments. |
ACCRINTM | ACCRINTM(issue, maturity, rate, [redemption], [day_count_convention]) |
Calculates the accrued interest of a security that pays interest at maturity. |
AMORLINC | AMORLINC(cost, purchase_date, first_period_end, salvage, period, rate, [basis]) |
Returns the depreciation for an accounting period, or the prorated depreciation if the asset was purchased in the middle of a period. |
COUPDAYBS | COUPDAYBS(settlement, maturity, frequency, [day_count_convention]) |
Calculates the number of days from the first coupon, or interest payment, until settlement. |
COUPDAYS | COUPDAYS(settlement, maturity, frequency, [day_count_convention]) |
Calculates the number of days in the coupon, or interest payment, period that contains the specified settlement date. |
COUPDAYSNC | COUPDAYSNC(settlement, maturity, frequency, [day_count_convention]) |
Calculates the number of days from the settlement date until the next coupon, or interest payment. |
COUPNCD | COUPNCD(settlement, maturity, frequency, [day_count_convention]) |
Calculates next coupon, or interest payment, date after the settlement date. |
COUPNUM | COUPNUM(settlement, maturity, frequency, [day_count_convention]) |
Calculates the number of coupons, or interest payments, between the settlement date and the maturity date of the investment. |
COUPPCD | COUPPCD(settlement, maturity, frequency, [day_count_convention]) |
Calculates last coupon, or interest payment, date before the settlement date. |
CUMIPMT | CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning) |
Calculates the cumulative interest over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. |
CUMPRINC | CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning) |
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. |
DB | DB(cost, salvage, life, period, [month]) |
Calculates the depreciation of an asset for a specified period using the arithmetic declining balance method. |
DDB | DDB(cost, salvage, life, period, [factor]) |
Calculates the depreciation of an asset for a specified period using the double-declining balance method. |
DISC | DISC(settlement, maturity, price, redemption, [day_count_convention]) |
Calculates the discount rate of a security based on price. |
DOLLARDE | DOLLARDE(fractional_price, unit) |
Converts a price quotation given as a decimal fraction into a decimal value. |
DOLLARFR | DOLLARFR(decimal_price, unit) |
Converts a price quotation given as a decimal value into a decimal fraction. |
DURATION | DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) |
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. |
EFFECT | EFFECT(nominal_rate, periods_per_year) |
Calculates the annual effective interest rate given the nominal rate and number of compounding periods per year. |
FV | FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning]) |
Calculates the future value of an annuity investment based on constant-amount periodic payments and a constant interest rate. |
FVSCHEDULE | FVSCHEDULE(principal, rate_schedule) |
Calculates the future value of some principal based on a specified series of potentially varying interest rates. |
INTRATE | INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention]) |
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. |
IPMT | IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) |
Calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate. |
IRR | IRR(cashflow_amounts, [rate_guess]) |
Calculates the internal rate of return on an investment based on a series of periodic cash flows. |
ISPMT | ISPMT(rate, period, number_of_periods, present_value) |
The ISPMT function calculates the interest paid during a particular period of an investment. . |
MDURATION | MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) |
Calculates the modified Macaulay duration of a security paying periodic interest, such as a US Treasury Bond, based on expected yield. |
MIRR | MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate) |
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. |
NOMINAL | NOMINAL(effective_rate, periods_per_year) |
Calculates the annual nominal interest rate given the effective rate and number of compounding periods per year. |
NPER | NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning]) |
Calculates the number of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. |
NPV | NPV(discount, cashflow1, [cashflow2, ...]) |
Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate. |
PDURATION | PDURATION(rate, present_value, future_value) |
Returns the number of periods for an investment to reach a specific value at a given rate. . |
PMT | PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) |
Calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate. |
PPMT | PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) |
Calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate. |
PRICE | PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention]) |
Calculates the price of a security paying periodic interest, such as a US Treasury Bond, based on expected yield. |
PRICEDISC | PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention]) |
Calculates the price of a discount (non-interest-bearing) security, based on expected yield. |
PRICEMAT | PRICEMAT(settlement, maturity, issue, rate, yield, [day_count_convention]) |
Calculates the price of a security paying interest at maturity, based on expected yield. |
PV | PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning]) |
Calculates the present value of an annuity investment based on constant-amount periodic payments and a constant interest rate. |
RATE | RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess]) |
Calculates the interest rate of an annuity investment based on constant-amount periodic payments and the assumption of a constant interest rate. |
RECEIVED | RECEIVED(settlement, maturity, investment, discount, [day_count_convention]) |
Calculates the amount received at maturity for an investment in fixed-income securities purchased on a given date. |
RRI | RRI(number_of_periods, present_value, future_value) |
Returns the interest rate needed for an investment to reach a specific value within a given number of periods. . |
SLN | SLN(cost, salvage, life) |
Calculates the depreciation of an asset for one period using the straight-line method. |
SYD | SYD(cost, salvage, life, period) |
Calculates the depreciation of an asset for a specified period using the sum of years digits method. |
TBILLEQ | TBILLEQ(settlement, maturity, discount) |
Calculates the equivalent annualized rate of return of a US Treasury Bill based on discount rate. |
TBILLPRICE | TBILLPRICE(settlement, maturity, discount) |
Calculates the price of a US Treasury Bill based on discount rate. |
TBILLYIELD | TBILLYIELD(settlement, maturity, price) |
Calculates the yield of a US Treasury Bill based on price. |
VDB | VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) |
Returns the depreciation of an asset for a particular period (or partial period). . |
XIRR | XIRR(cashflow_amounts, cashflow_dates, [rate_guess]) |
Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows. |
XNPV | XNPV(discount, cashflow_amounts, cashflow_dates) |
Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate. |
YIELD | YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention]) |
Calculates the annual yield of a security paying periodic interest, such as a US Treasury Bond, based on price. |
YIELDDISC | YIELDDISC(settlement, maturity, price, redemption, [day_count_convention]) |
Calculates the annual yield of a discount (non-interest-bearing) security, based on price. |
YIELDMAT | YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention]) |
Calculates the annual yield of a security paying interest at maturity, based on price. |
Name | Syntax | Description |
---|---|---|
ARRAYFORMULA | ARRAYFORMULA(array_formula) |
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. |
DETECTLANGUAGE | DETECTLANGUAGE(text_or_range) |
Identifies the language used in text within the specified range. |
GOOGLEFINANCE | `GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date | num_days], [interval])` |
GOOGLETRANSLATE | GOOGLETRANSLATE(text, [source_language], [target_language]) |
Translates text from one language into another |
IMAGE | IMAGE(url, [mode], [height], [width]) |
Inserts an image into a cell. |
QUERY | QUERY(data, query, [headers]) |
Runs a Google Visualization API Query Language query across data. |
SPARKLINE | SPARKLINE(data, [options]) |
Creates a miniature chart contained within a single cell. |
Name | Syntax | Description |
---|---|---|
CELL | CELL(info_type, reference) |
Returns the requested information about the specified cell. |
ERROR.TYPE | ERROR.TYPE(reference) |
Returns a number corresponding to the error value in a different cell. |
ISBLANK | ISBLANK(value) |
Checks whether the referenced cell is empty. |
ISDATE | ISDATE(value) |
Returns whether a value is a date. |
ISEMAIL | ISEMAIL(value) |
Checks whether a value is a valid email address. |
ISERR | ISERR(value) |
Checks whether a value is an error other than #N/A . |
ISERROR | ISERROR(value) |
Checks whether a value is an error. |
ISFORMULA | ISFORMULA(cell) |
Checks whether a formula is in the referenced cell. |
ISLOGICAL | ISLOGICAL(value) |
Checks whether a value is TRUE or FALSE . |
ISNA | ISNA(value) |
Checks whether a value is the error #N/A . |
ISNONTEXT | ISNONTEXT(value) |
Checks whether a value is non-textual. |
ISNUMBER | ISNUMBER(value) |
Checks whether a value is a number. |
ISREF | ISREF(value) |
Checks whether a value is a valid cell reference. |
ISTEXT | ISTEXT(value) |
Checks whether a value is text. |
N | N(value) |
Returns the argument provided as a number. |
NA | NA() |
Returns the "value not available" error, #N/A . |
TYPE | TYPE(value) |
Returns a number associated with the type of data passed into the function. |
Name | Syntax | Description |
---|---|---|
AND | AND(logical_expression1, [logical_expression2, ...]) |
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false. |
FALSE | FALSE() |
Returns the logical value FALSE . |
IF | IF(logical_expression, value_if_true, value_if_false) |
Returns one value if a logical expression is TRUE and another if it is FALSE . |
IFERROR | IFERROR(value, [value_if_error]) |
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. |
IFNA | IFNA(value, value_if_na) |
Evaluates a value. If the value is an #N/A error, returns the specified value. . |
IFS | IFS(condition1, value1, [condition2, value2], …) |
Evaluates multiple conditions and returns a value that corresponds to the first true condition. |
LAMBDA | LAMBDA(name, formula_expression) |
Creates and returns a custom function with a set of names and a formula_expression that uses them. To calculate the formula_expression, you can call the returned function with as many values as the name declares. |
NOT | NOT(logical_expression) |
Returns the opposite of a logical value - NOT(TRUE) returns FALSE ; NOT(FALSE) returns TRUE . |
OR | OR(logical_expression1, [logical_expression2, ...]) |
Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false. |
SWITCH | SWITCH(expression, case1, value1, [default or case2, value2], …) |
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. |
TRUE | TRUE() |
Returns the logical value TRUE . |
XOR | XOR(logical_expression1, [logical_expression2, ...]) |
The XOR function performs an exclusive or of 2 numbers that returns a 1 if the numbers are different, and a 0 otherwise. . |
Name | Syntax | Description |
---|---|---|
ADDRESS | ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) |
Returns a cell reference as a string. |
CHOOSE | CHOOSE(index, choice1, [choice2, ...]) |
Returns an element from a list of choices based on index. |
COLUMN | COLUMN([cell_reference]) |
Returns the column number of a specified cell, with A=1 . |
COLUMNS | COLUMNS(range) |
Returns the number of columns in a specified array or range. |
FORMULATEXT | FORMULATEXT(cell) |
Returns the formula as a string. . |
GETPIVOTDATA | GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, ...], [pivot_item, ...] |
Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings. |
HLOOKUP | HLOOKUP(search_key, range, index, [is_sorted]) |
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. |
INDEX | INDEX(reference, [row], [column]) |
Returns the content of a cell, specified by row and column offset. |
INDIRECT | INDIRECT(cell_reference_as_string, [is_A1_notation]) |
Returns a cell reference specified by a string. |
LOOKUP | `LOOKUP(search_key, search_range | search_result_array, [result_range])` |
MATCH | MATCH(search_key, range, [search_type]) |
Returns the relative position of an item in a range that matches a specified value. |
OFFSET | OFFSET(cell_reference, offset_rows, offset_columns, [height], [width]) |
Returns a range reference shifted a specified number of rows and columns from a starting cell reference. |
ROW | ROW([cell_reference]) |
Returns the row number of a specified cell. |
ROWS | ROWS(range) |
Returns the number of rows in a specified array or range. |
VLOOKUP | VLOOKUP(search_key, range, index, [is_sorted]) |
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. |
XLOOKUP | XLOOKUP(search_key, lookup_range, result_range, missing_value, [match_mode], [search_mode]) |
Returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match. |
Name | Syntax | Description |
---|---|---|
ABS | ABS(value) |
Returns the absolute value of a number. |
ACOS | ACOS(value) |
Returns the inverse cosine of a value, in radians. |
ACOSH | ACOSH(value) |
Returns the inverse hyperbolic cosine of a number. |
ACOT | ACOT(value) |
Returns the inverse cotangent of a value, in radians. . |
ACOTH | ACOTH(value) |
Returns the inverse hyperbolic cotangent of a value, in radians. Must not be between -1 and 1, inclusive.. |
ASIN | ASIN(value) |
Returns the inverse sine of a value, in radians. |
ASINH | ASINH(value) |
Returns the inverse hyperbolic sine of a number. |
ATAN | ATAN(value) |
Returns the inverse tangent of a value, in radians. |
ATAN2 | ATAN2(x, y) |
Returns the angle between the x-axis and a line segment from the origin (0,0) to specified coordinate pair (x ,y ), in radians. |
ATANH | ATANH(value) |
Returns the inverse hyperbolic tangent of a number. |
BASE | BASE(value, base, [min_length]) |
Converts a number into a text representation in another base, for example, base 2 for binary. . |
CEILING | CEILING(value, [factor]) |
Rounds a number up to the nearest integer multiple of specified significance. |
CEILING.MATH | CEILING.MATH(number, [significance], [mode]) |
Rounds a number up to the nearest integer multiple of specified significance, with negative numbers rounding toward or away from 0 depending on the mode. |
CEILING.PRECISE | CEILING.PRECISE(number, [significance]) |
Rounds a number up to the nearest integer multiple of specified significance. If the number is positive or negative, it is rounded up. |
COMBIN | COMBIN(n, k) |
Returns the number of ways to choose some number of objects from a pool of a given size of objects. |
COMBINA | COMBINA(n, k) |
Returns the number of ways to choose some number of objects from a pool of a given size of objects, including ways that choose the same object multiple times. . |
COS | COS(angle) |
Returns the cosine of an angle provided in radians. |
COSH | COSH(value) |
Returns the hyperbolic cosine of any real number. |
COT | COT(angle) |
Cotangent of an angle provided in radians. . |
COTH | COTH(value) |
Returns the hyperbolic cotangent of any real number. . |
COUNTBLANK | COUNTBLANK(range) |
Returns the number of empty cells in a given range. |
COUNTIF | COUNTIF(range, criterion) |
Returns a conditional count across a range. |
COUNTIFS | COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
Returns the count of a range depending on multiple criteria. |
COUNTUNIQUE | COUNTUNIQUE(value1, [value2, ...]) |
Counts the number of unique values in a list of specified values and ranges. |
CSC | CSC(angle) |
Returns the cosecant of an angle provided in radians. . |
CSCH | CSCH(value) |
The CSCH function returns the hyperbolic cosecant of any real number. . |
DECIMAL | DECIMAL(value, base) |
The DECIMAL function converts the text representation of a number in another base, to base 10 (decimal). . |
DEGREES | DEGREES(angle) |
Converts an angle value in radians to degrees. |
ERFC | ERFC(z) |
Returns the complementary Gauss error function of a value. |
ERFC.PRECISE | ERFC.PRECISE(z) |
See |
EVEN | EVEN(value) |
Rounds a number up to the nearest even integer. |
EXP | EXP(exponent) |
Returns Euler's number, e (~2.718) raised to a power. |
FACT | FACT(value) |
Returns the factorial of a number. |
FACTDOUBLE | FACTDOUBLE(value) |
Returns the "double factorial" of a number. |
FLOOR | FLOOR(value, [factor]) |
Rounds a number down to the nearest integer multiple of specified significance. |
FLOOR.MATH | FLOOR.MATH(number, [significance], [mode]) |
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.PRECISE | FLOOR.PRECISE(number, [significance]) |
The FLOOR.PRECISE function rounds a number down to the nearest integer or multiple of specified significance. . |
GAMMALN | GAMMALN(value) |
Returns the the logarithm of a specified Gamma function, base e (Euler's number). |
GAMMALN.PRECISE | GAMMALN.PRECISE(value) |
See |
GCD | GCD(value1, value2) |
Returns the greatest common divisor of one or more integers. |
IMLN | IMLN(complex_value) |
Returns the logarithm of a complex number, base e (Euler's number). |
IMPOWER | IMPOWER(complex_base, exponent) |
Returns a complex number raised to a power. |
IMSQRT | IMSQRT(complex_number) |
Computes the square root of a complex number. |
INT | INT(value) |
Rounds a number down to the nearest integer that is less than or equal to it. |
ISEVEN | ISEVEN(value) |
Checks whether the provided value is even. |
ISO.CEILING | ISO.CEILING(number, [significance]) |
See |
ISODD | ISODD(value) |
Checks whether the provided value is odd. |
LCM | LCM(value1, value2) |
Returns the least common multiple of one or more integers. |
LN | LN(value) |
Returns the the logarithm of a number, base e (Euler's number). |
LOG | LOG(value, base) |
Returns the the logarithm of a number given a base. |
LOG10 | LOG10(value) |
Returns the the logarithm of a number, base 10. |
MOD | MOD(dividend, divisor) |
Returns the result of the modulo operator, the remainder after a division operation. |
MROUND | MROUND(value, factor) |
Rounds one number to the nearest integer multiple of another. |
MULTINOMIAL | MULTINOMIAL(value1, value2) |
Returns the factorial of the sum of values divided by the product of the values' factorials. |
MUNIT | MUNIT(dimension) |
Returns a unit matrix of size dimension x dimension. . |
ODD | ODD(value) |
Rounds a number up to the nearest odd integer. |
PI | PI() |
Returns the value of Pi to 14 decimal places. |
POWER | POWER(base, exponent) |
Returns a number raised to a power. |
PRODUCT | PRODUCT(factor1, [factor2, ...]) |
Returns the result of multiplying a series of numbers together. |
QUOTIENT | QUOTIENT(dividend, divisor) |
Returns one number divided by another. |
RADIANS | RADIANS(angle) |
Converts an angle value in degrees to radians. |
RAND | RAND() |
Returns a random number between 0 inclusive and 1 exclusive. |
RANDARRAY | RANDARRAY(rows, columns) |
Generates an array of random numbers between 0 and 1. . |
RANDBETWEEN | RANDBETWEEN(low, high) |
Returns a uniformly random integer between two values, inclusive. |
ROUND | ROUND(value, [places]) |
Rounds a number to a certain number of decimal places according to standard rules. |
ROUNDDOWN | ROUNDDOWN(value, [places]) |
Rounds a number to a certain number of decimal places, always rounding down to the next valid increment. |
ROUNDUP | ROUNDUP(value, [places]) |
Rounds a number to a certain number of decimal places, always rounding up to the next valid increment. |
SEC | SEC(angle) |
The SEC function returns the secant of an angle, measured in radians. . |
SECH | SECH(value) |
The SECH function returns the hyperbolic secant of an angle. |
SEQUENCE | SEQUENCE(rows, columns, start, step) |
Returns an array of sequential numbers, such as 1, 2, 3, 4. . |
SERIESSUM | SERIESSUM(x, n, m, a) |
Given parameters x, n, m, and a, returns the power series sum a1xn + a2x(n+m) + ... + aix(n+(i-1)m), where i is the number of entries in range a . |
SIGN | SIGN(value) |
Given an input number, returns -1 if it is negative, 1 if positive, and 0 if it is zero. |
SIN | SIN(angle) |
Returns the sine of an angle provided in radians. |
SINH | SINH(value) |
Returns the hyperbolic sine of any real number. |
SQRT | SQRT(value) |
Returns the positive square root of a positive number. |
SQRTPI | SQRTPI(value) |
Returns the positive square root of the product of Pi and the given positive number. |
SUBTOTAL | SUBTOTAL(function_code, range1, [range2, ...]) |
Returns a subtotal for a vertical range of cells using a specified aggregation function. |
SUM | SUM(value1, [value2, ...]) |
Returns the sum of a series of numbers and/or cells. |
SUMIF | SUMIF(range, criterion, [sum_range]) |
Returns a conditional sum across a range. |
SUMIFS | SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
Returns the sum of a range depending on multiple criteria. |
SUMSQ | SUMSQ(value1, [value2, ...]) |
Returns the sum of the squares of a series of numbers and/or cells. |
TAN | TAN(angle) |
Returns the tangent of an angle provided in radians. |
TANH | TANH(value) |
Returns the hyperbolic tangent of any real number. |
TRUNC | TRUNC(value, [places]) |
Truncates a number to a certain number of significant digits by omitting less significant digits. |
Name | Syntax | Description |
---|---|---|
ADD | ADD(value1, value2) |
Returns the sum of two numbers. Equivalent to the + operator. |
CONCAT | CONCAT(value1, value2) |
Returns the concatenation of two values. Equivalent to the & operator. |
DIVIDE | DIVIDE(dividend, divisor) |
Returns one number divided by another. Equivalent to the / operator. |
EQ | EQ(value1, value2) |
Returns TRUE if two specified values are equal and FALSE otherwise. Equivalent to the = operator. |
GT | GT(value1, value2) |
Returns TRUE if the first argument is strictly greater than the second, and FALSE otherwise. Equivalent to the > operator. |
GTE | GTE(value1, value2) |
Returns TRUE if the first argument is greater than or equal to the second, and FALSE otherwise. Equivalent to the >= operator. |
ISBETWEEN | ISBETWEEN(value_to_compare, lower_value, upper_value, lower_value_is_inclusive, upper_value_is_inclusive) |
Checks whether a provided number is between two other numbers either inclusively or exclusively. |
LT | LT(value1, value2) |
Returns TRUE if the first argument is strictly less than the second, and FALSE otherwise. Equivalent to the < operator. |
LTE | LTE(value1, value2) |
Returns TRUE if the first argument is less than or equal to the second, and FALSE otherwise. Equivalent to the <= operator. |
MINUS | MINUS(value1, value2) |
Returns the difference of two numbers. Equivalent to the - operator. |
MULTIPLY | MULTIPLY(factor1, factor2) |
Returns the product of two numbers. Equivalent to the * operator. |
NE | NE(value1, value2) |
Returns TRUE if two specified values are not equal and FALSE otherwise. Equivalent to the <> operator. |
POW | POW(base, exponent) |
Returns a number raised to a power. |
UMINUS | UMINUS(value) |
Returns a number with the sign reversed. |
UNARY_PERCENT | UNARY_PERCENT(percentage) |
Returns a value interpreted as a percentage; that is, UNARY_PERCENT(100) equals 1 . |
UNIQUE | UNIQUE(range, by_column, exactly_once) |
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. |
UPLUS | UPLUS(value) |
Returns a specified number, unchanged. |
Name | Syntax | Description |
---|---|---|
CONVERT | CONVERT(value, start_unit, end_unit) |
Converts a numeric value to a different unit of measure. |
TO_DATE | TO_DATE(value) |
Converts a provided number to a date. |
TO_DOLLARS | TO_DOLLARS(value) |
Converts a provided number to a dollar value. |
TO_PERCENT | TO_PERCENT(value) |
Converts a provided number to a percentage. |
TO_PURE_NUMBER | TO_PURE_NUMBER(value) |
Converts a provided date/time, percentage, currency or other formatted numeric value to a pure number without formatting. |
TO_TEXT | TO_TEXT(value) |
Converts a provided numeric value to a text value. |
Name | Syntax | Description |
---|---|---|
AVEDEV | AVEDEV(value1, [value2, ...]) |
Calculates the average of the magnitudes of deviations of data from a dataset's mean. |
AVERAGE | AVERAGE(value1, [value2, ...]) |
Returns the numerical average value in a dataset, ignoring text. |
AVERAGE.WEIGHTED | AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights]) |
Finds the weighted average of a set of values, given the values and the corresponding weights. . |
AVERAGEA | AVERAGEA(value1, [value2, ...]) |
Returns the numerical average value in a dataset. |
AVERAGEIF | AVERAGEIF(criteria_range, criterion, [average_range]) |
Returns the average of a range depending on criteria. |
AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
Returns the average of a range depending on multiple criteria. |
BETA.DIST | BETA.DIST(value, alpha, beta, cumulative, lower_bound, upper_bound) |
Returns the probability of a given value as defined by the beta distribution function. . |
BETA.INV | BETA.INV(probability, alpha, beta, lower_bound, upper_bound) |
Returns the value of the inverse beta distribution function for a given probability. |
BETADIST | BETADIST(value, alpha, beta, lower_bound, upper_bound) |
See . |
BETAINV | BETAINV(probability, alpha, beta, lower_bound, upper_bound) |
See |
BINOM.DIST | BINOM.DIST(num_successes, num_trials, prob_success, cumulative) |
|
BINOM.INV | BINOM.INV(num_trials, prob_success, target_prob) |
See |
BINOMDIST | BINOMDIST(num_successes, num_trials, prob_success, cumulative) |
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. |
CHIDIST | CHIDIST(x, degrees_freedom) |
Calculates the right-tailed chi-squared distribution, often used in hypothesis testing. |
CHIINV | CHIINV(probability, degrees_freedom) |
Calculates the inverse of the right-tailed chi-squared distribution. |
CHISQ.DIST | CHISQ.DIST(x, degrees_freedom, cumulative) |
Calculates the left-tailed chi-squared distribution, often used in hypothesis testing. |
CHISQ.DIST.RT | CHISQ.DIST.RT(x, degrees_freedom) |
Calculates the right-tailed chi-squared distribution, which is commonly used in hypothesis testing. |
CHISQ.INV | CHISQ.INV(probability, degrees_freedom) |
Calculates the inverse of the left-tailed chi-squared distribution. |
CHISQ.INV.RT | CHISQ.INV.RT(probability, degrees_freedom) |
Calculates the inverse of the right-tailed chi-squared distribution. |
CHISQ.TEST | CHISQ.TEST(observed_range, expected_range) |
See |
CHITEST | CHITEST(observed_range, expected_range) |
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. |
CONFIDENCE | CONFIDENCE(alpha, standard_deviation, pop_size) |
See |
CONFIDENCE.NORM | CONFIDENCE.NORM(alpha, standard_deviation, pop_size) |
Calculates the width of half the confidence interval for a normal distribution. . |
CONFIDENCE.T | CONFIDENCE.T(alpha, standard_deviation, size) |
Calculates the width of half the confidence interval for a Student’s t-distribution. . |
CORREL | CORREL(data_y, data_x) |
Calculates r, the Pearson product-moment correlation coefficient of a dataset. |
COUNT | COUNT(value1, [value2, ...]) |
Returns a count of the number of numeric values in a dataset. |
COUNTA | COUNTA(value1, [value2, ...]) |
Returns a count of the number of values in a dataset. |
COVAR | COVAR(data_y, data_x) |
Calculates the covariance of a dataset. |
COVARIANCE.P | COVARIANCE.P(data_y, data_x) |
See |
COVARIANCE.S | COVARIANCE.S(data_y, data_x) |
Calculates the covariance of a dataset, where the dataset is a sample of the total population. . |
CRITBINOM | CRITBINOM(num_trials, prob_success, target_prob) |
Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria. |
DEVSQ | DEVSQ(value1, value2) |
Calculates the sum of squares of deviations based on a sample. |
EXPON.DIST | EXPON.DIST(x, LAMBDA, cumulative) |
Returns the value of the exponential distribution function with a specified LAMBDA at a specified value. . |
EXPONDIST | EXPONDIST(x, LAMBDA, cumulative) |
See |
F.DIST | F.DIST(x, degrees_freedom1, degrees_freedom2, cumulative) |
Calculates the left-tailed F probability distribution (degree of diversity) for two data sets with given input x. Alternately called Fisher-Snedecor distribution or Snedecor's F distribution. |
F.DIST.RT | F.DIST.RT(x, degrees_freedom1, degrees_freedom2) |
Calculates the right-tailed F probability distribution (degree of diversity) for two data sets with given input x. Alternately called Fisher-Snedecor distribution or Snedecor's F distribution. |
F.INV | F.INV(probability, degrees_freedom1, degrees_freedom2) |
Calculates the inverse of the left-tailed F probability distribution. Also called the Fisher-Snedecor distribution or Snedecor’s F distribution. |
F.INV.RT | F.INV.RT(probability, degrees_freedom1, degrees_freedom2) |
Calculates the inverse of the right-tailed F probability distribution. Also called the Fisher-Snedecor distribution or Snedecor’s F distribution. |
F.TEST | F.TEST(range1, range2) |
See . |
FDIST | FDIST(x, degrees_freedom1, degrees_freedom2) |
See . |
FINV | FINV(probability, degrees_freedom1, degrees_freedom2) |
See |
FISHER | FISHER(value) |
Returns the Fisher transformation of a specified value. |
FISHERINV | FISHERINV(value) |
Returns the inverse Fisher transformation of a specified value. |
FORECAST | FORECAST(x, data_y, data_x) |
Calculates the expected y-value for a specified x based on a linear regression of a dataset. |
FORECAST.LINEAR | FORECAST.LINEAR(x, data_y, data_x) |
See |
FTEST | FTEST(range1, range2) |
Returns the probability associated with an F-test for equality of variances. Determines whether two samples are likely to have come from populations with the same variance. |
GAMMA | GAMMA(number) |
Returns the Gamma function evaluated at the specified value. . |
GAMMA.DIST | GAMMA.DIST(x, alpha, beta, cumulative) |
Calculates the gamma distribution, a two-parameter continuous probability distribution. |
GAMMA.INV | GAMMA.INV(probability, alpha, beta) |
The GAMMA.INV function returns the value of the inverse gamma cumulative distribution function for the specified probability and alpha and beta parameters. . |
GAMMADIST | GAMMADIST(x, alpha, beta, cumulative) |
See |
GAMMAINV | GAMMAINV(probability, alpha, beta) |
See . |
GAUSS | GAUSS(z) |
The GAUSS function returns the probability that a random variable, drawn from a normal distribution, will be between the mean and z standard deviations above (or below) the mean. . |
GEOMEAN | GEOMEAN(value1, value2) |
Calculates the geometric mean of a dataset. |
HARMEAN | HARMEAN(value1, value2) |
Calculates the harmonic mean of a dataset. |
HYPGEOM.DIST | HYPGEOM.DIST(num_successes, num_draws, successes_in_pop, pop_size) |
See |
HYPGEOMDIST | HYPGEOMDIST(num_successes, num_draws, successes_in_pop, pop_size) |
Calculates the probability of drawing a certain number of successes in a certain number of tries given a population of a certain size containing a certain number of successes, without replacement of draws. |
INTERCEPT | INTERCEPT(data_y, data_x) |
Calculates the y-value at which the line resulting from linear regression of a dataset will intersect the y-axis (x=0). |
KURT | KURT(value1, value2) |
Calculates the kurtosis of a dataset, which describes the shape, and in particular the "peakedness" of that dataset. |
LARGE | LARGE(data, n) |
Returns the nth largest element from a data set, where n is user-defined. |
LOGINV | LOGINV(x, mean, standard_deviation) |
Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value. |
LOGNORM.DIST | LOGNORM.DIST(x, mean, standard_deviation) |
See |
LOGNORM.INV | LOGNORM.INV(x, mean, standard_deviation) |
See |
LOGNORMDIST | LOGNORMDIST(x, mean, standard_deviation) |
Returns the value of the log-normal cumulative distribution with given mean and standard deviation at a specified value. |
MAX | MAX(value1, [value2, ...]) |
Returns the maximum value in a numeric dataset. |
MAXA | MAXA(value1, value2) |
Returns the maximum numeric value in a dataset. |
MAXIFS | MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], …) |
Returns the maximum value in a range of cells, filtered by a set of criteria. |
MEDIAN | MEDIAN(value1, [value2, ...]) |
Returns the median value in a numeric dataset. |
MIN | MIN(value1, [value2, ...]) |
Returns the minimum value in a numeric dataset. |
MINA | MINA(value1, value2) |
Returns the minimum numeric value in a dataset. |
MINIFS | MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], …) |
Returns the minimum value in a range of cells, filtered by a set of criteria. |
MODE | MODE(value1, [value2, ...]) |
Returns the most commonly occurring value in a dataset. |
MODE.MULT | MODE.MULT(value1, value2) |
Returns the most commonly occurring values in a dataset. . |
MODE.SNGL | MODE.SNGL(value1, [value2, ...]) |
See |
NEGBINOM.DIST | NEGBINOM.DIST(num_failures, num_successes, prob_success) |
See |
NEGBINOMDIST | NEGBINOMDIST(num_failures, num_successes, prob_success) |
Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials. |
NORM.DIST | NORM.DIST(x, mean, standard_deviation, cumulative) |
See |
NORM.INV | NORM.INV(x, mean, standard_deviation) |
See |
NORM.S.DIST | NORM.S.DIST(x) |
See |
NORM.S.INV | NORM.S.INV(x) |
See |
NORMDIST | NORMDIST(x, mean, standard_deviation, cumulative) |
Returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation. |
NORMINV | NORMINV(x, mean, standard_deviation) |
Returns the value of the inverse normal distribution function for a specified value, mean, and standard deviation. |
NORMSDIST | NORMSDIST(x) |
Returns the value of the standard normal cumulative distribution function for a specified value. |
NORMSINV | NORMSINV(x) |
Returns the value of the inverse standard normal distribution function for a specified value. |
PEARSON | PEARSON(data_y, data_x) |
Calculates r, the Pearson product-moment correlation coefficient of a dataset. |
PERCENTILE | PERCENTILE(data, percentile) |
Returns the value at a given percentile of a dataset. |
PERCENTILE.EXC | PERCENTILE.EXC(data, percentile) |
Returns the value at a given percentile of a dataset, exclusive of 0 and 1. . |
PERCENTILE.INC | PERCENTILE.INC(data, percentile) |
See |
PERCENTRANK | PERCENTRANK(data, value, [significant_digits]) |
Returns the percentage rank (percentile) of a specified value in a dataset. |
PERCENTRANK.EXC | PERCENTRANK.EXC(data, value, [significant_digits]) |
Returns the percentage rank (percentile) from 0 to 1 exclusive of a specified value in a dataset. |
PERCENTRANK.INC | PERCENTRANK.INC(data, value, [significant_digits]) |
Returns the percentage rank (percentile) from 0 to 1 inclusive of a specified value in a dataset. |
PERMUT | PERMUT(n, k) |
Returns the number of ways to choose some number of objects from a pool of a given size of objects, considering order. |
PERMUTATIONA | PERMUTATIONA(number, number_chosen) |
Returns the number of permutations for selecting a group of objects (with replacement) from a total number of objects. . |
PHI | PHI(x) |
The PHI function returns the value of the normal distribution with mean 0 and standard deviation 1. . |
POISSON | POISSON(x, mean, cumulative) |
See |
POISSON.DIST | POISSON.DIST(x, mean, [cumulative]) |
Returns the value of the Poisson distribution function (or Poisson cumulative distribution function) for a specified value and mean. . |
PROB | PROB(data, probabilities, low_limit, [high_limit]) |
Given a set of values and corresponding probabilities, calculates the probability that a value chosen at random falls between two limits. |
QUARTILE | QUARTILE(data, quartile_number) |
Returns a value nearest to a specified quartile of a dataset. |
QUARTILE.EXC | QUARTILE.EXC(data, quartile_number) |
Returns value nearest to a given quartile of a dataset, exclusive of 0 and 4. . |
QUARTILE.INC | QUARTILE.INC(data, quartile_number) |
See |
RANK | RANK(value, data, [is_ascending]) |
Returns the rank of a specified value in a dataset. |
RANK.AVG | RANK.AVG(value, data, [is_ascending]) |
Returns the rank of a specified value in a dataset. If there is more than one entry of the same value in the dataset, the average rank of the entries will be returned. |
RANK.EQ | RANK.EQ(value, data, [is_ascending]) |
Returns the rank of a specified value in a dataset. If there is more than one entry of the same value in the dataset, the top rank of the entries will be returned. |
RSQ | RSQ(data_y, data_x) |
Calculates the square of r, the Pearson product-moment correlation coefficient of a dataset. |
SKEW | SKEW(value1, value2) |
Calculates the skewness of a dataset, which describes the symmetry of that dataset about the mean. |
SKEW.P | SKEW.P(value1, value2) |
Calculates the skewness of a dataset that represents the entire population. . |
SLOPE | SLOPE(data_y, data_x) |
Calculates the slope of the line resulting from linear regression of a dataset. |
SMALL | SMALL(data, n) |
Returns the nth smallest element from a data set, where n is user-defined. |
STANDARDIZE | STANDARDIZE(value, mean, standard_deviation) |
Calculates the normalized equivalent of a random variable given mean and standard deviation of the distribution. |
STDEV | STDEV(value1, [value2, ...]) |
Calculates the standard deviation based on a sample. |
STDEV.P | STDEV.P(value1, [value2, ...]) |
See |
STDEV.S | STDEV.S(value1, [value2, ...]) |
See |
STDEVA | STDEVA(value1, value2) |
Calculates the standard deviation based on a sample, setting text to the value 0 . |
STDEVP | STDEVP(value1, value2) |
Calculates the standard deviation based on an entire population. |
STDEVPA | STDEVPA(value1, value2) |
Calculates the standard deviation based on an entire population, setting text to the value 0 . |
STEYX | STEYX(data_y, data_x) |
Calculates the standard error of the predicted y-value for each x in the regression of a dataset. |
T.DIST | T.DIST(x, degrees_freedom, cumulative) |
Returns the right tailed Student distribution for a value x. . |
T.DIST.2T | T.DIST.2T(x, degrees_freedom) |
Returns the two tailed Student distribution for a value x. . |
T.DIST.RT | T.DIST.RT(x, degrees_freedom) |
Returns the right tailed Student distribution for a value x. . |
T.INV | T.INV(probability, degrees_freedom) |
Calculates the negative inverse of the one-tailed TDIST function. |
T.INV.2T | T.INV.2T(probability, degrees_freedom) |
Calculates the inverse of the two-tailed TDIST function. |
T.TEST | T.TEST(range1, range2, tails, type) |
Returns the probability associated with Student's t-test. Determines whether two samples are likely to have come from the same two underlying populations that have the same mean. . |
TDIST | TDIST(x, degrees_freedom, tails) |
Calculates the probability for Student's t-distribution with a given input (x). |
TINV | TINV(probability, degrees_freedom) |
See |
TRIMMEAN | TRIMMEAN(data, exclude_proportion) |
Calculates the mean of a dataset excluding some proportion of data from the high and low ends of the dataset. |
TTEST | TTEST(range1, range2, tails, type) |
See . |
VAR | VAR(value1, [value2, ...]) |
Calculates the variance based on a sample. |
VAR.P | VAR.P(value1, [value2, ...]) |
See |
VAR.S | VAR.S(value1, [value2, ...]) |
See |
VARA | VARA(value1, value2) |
Calculates an estimate of variance based on a sample, setting text to the value 0 . |
VARP | VARP(value1, value2) |
Calculates the variance based on an entire population. |
VARPA | VARPA(value1, value2,...) |
Calculates the variance based on an entire population, setting text to the value 0 . |
WEIBULL | WEIBULL(x, shape, scale, cumulative) |
Returns the value of the Weibull distribution function (or Weibull cumulative distribution function) for a specified shape and scale. |
WEIBULL.DIST | WEIBULL.DIST(x, shape, scale, cumulative) |
See |
Z.TEST | Z.TEST(data, value, [standard_deviation]) |
Returns the one-tailed P-value of a Z-test with standard distribution. . |
ZTEST | ZTEST(data, value, [standard_deviation]) |
See . |
Name | Syntax | Description |
---|---|---|
ARABIC | ARABIC(roman_numeral) |
Computes the value of a Roman numeral. |
ASC | ASC(text) |
Converts full-width ASCII and katakana characters to their half-width counterparts. All standard-width characters will remain unchanged. |
CHAR | CHAR(table_number) |
Convert a number into a character according to the current Unicode table. |
CLEAN | CLEAN(text) |
Returns the text with the non-printable ASCII characters removed. |
CODE | CODE(string) |
Returns the numeric Unicode map value of the first character in the string provided. |
CONCATENATE | CONCATENATE(string1, [string2, ...]) |
Appends strings to one another. |
DOLLAR | DOLLAR(number, [number_of_places]) |
Formats a number into the locale-specific currency format. |
EXACT | EXACT(string1, string2) |
Tests whether two strings are identical. |
FIND | FIND(search_for, text_to_search, [starting_at]) |
Returns the position at which a string is first found within text. |
FINDB | FINDB(search_for, text_to_search, [starting_at]) |
Returns the position at which a string is first found within text counting each double-character as 2. |
FIXED | FIXED(number, [number_of_places], [suppress_separator]) |
Formats a number with a fixed number of decimal places. |
JOIN | JOIN(delimiter, value_or_array1, [value_or_array2, ...]) |
Concatenates the elements of one or more one-dimensional arrays using a specified delimiter. |
LEFT | LEFT(string, [number_of_characters]) |
Returns a substring from the beginning of a specified string. |
LEFTB | LEFTB(string, num_of_bytes) |
Returns the left portion of a string up to a certain number of bytes. . |
LEN | LEN(text) |
Returns the length of a string. |
LENB | LENB(string) |
Returns the length of a string in bytes." . |
LOWER | LOWER(text) |
Converts a specified string to lowercase. |
MID | MID(string, starting_at, extract_length) |
Returns a segment of a string. |
MIDB | MIDB(string) |
Returns a section of a string starting at a given character and up to a specified number of bytes. . |
PROPER | PROPER(text_to_capitalize) |
Capitalizes each word in a specified string. |
REGEXEXTRACT | REGEXEXTRACT(text, regular_expression) |
Extracts matching substrings according to a regular expression. |
REGEXMATCH | REGEXMATCH(text, regular_expression) |
Whether a piece of text matches a regular expression. |
REGEXREPLACE | REGEXREPLACE(text, regular_expression, replacement) |
Replaces part of a text string with a different text string using regular expressions. |
REPLACE | REPLACE(text, position, length, new_text) |
Replaces part of a text string with a different text string. |
REPLACEB | REPLACEB(text, position, num_bytes, new_text) |
Replaces part of a text string, based on a number of bytes, with a different text string. . |
REPT | REPT(text_to_repeat, number_of_repetitions) |
Returns specified text repeated a number of times. |
RIGHT | RIGHT(string, [number_of_characters]) |
Returns a substring from the end of a specified string. |
RIGHTB | RIGHTB(string, num_of_bytes) |
Returns the right portion of a string up to a certain number of bytes. . |
ROMAN | ROMAN(number, [rule_relaxation]) |
Formats a number in Roman numerals. |
SEARCH | SEARCH(search_for, text_to_search, [starting_at]) |
Returns the position at which a string is first found within text. |
SEARCHB | SEARCHB(search_for, text_to_search, [starting_at]) |
Returns the position at which a string is first found within text counting each double-character as 2. |
SPLIT | SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) |
Divides text around a specified character or string, and puts each fragment into a separate cell in the row. |
SUBSTITUTE | SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) |
Replaces existing text with new text in a string. |
T | T(value) |
Returns string arguments as text. |
TEXT | TEXT(number, format) |
Converts a number into text according to a specified format. |
TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) |
Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts. |
TRIM | TRIM(text) |
Removes leading and trailing spaces in a specified string. |
UNICHAR | UNICHAR(number) |
Returns the Unicode character for a number. . |
UNICODE | UNICODE(text) |
Returns the decimal Unicode value of the first character of the text. |
UPPER | UPPER(text) |
Converts a specified string to uppercase. |
VALUE | VALUE(text) |
Converts a string in any of the date, time or number formats that Google Sheets understands into a number. |
Name | Syntax | Description |
---|---|---|
ENCODEURL | ENCODEURL(text) |
Encodes a string of text for the purpose of using in a URL query. . |
HYPERLINK | HYPERLINK(url, [link_label]) |
Creates a hyperlink inside a cell. |
IMPORTDATA | IMPORTDATA(url) |
Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format. |
IMPORTFEED | IMPORTFEED(url, [query], [headers], [num_items]) |
Imports a RSS or ATOM feed. |
IMPORTHTML | IMPORTHTML(url, query, index) |
Imports data from a table or list within an HTML page. |
IMPORTRANGE | IMPORTRANGE(spreadsheet_url, range_string) |
Imports a range of cells from a specified spreadsheet. |
IMPORTXML | IMPORTXML(url, xpath_query) |
Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. |
ISURL | ISURL(value) |
Checks whether a value is a valid URL. |