Functions

Spreadsheet functions are divided in the following categories:

• Array

• Database

• Date

• Engineering

• Filter

• Financial

• Info

• Logical

• Lookup

• Math

• Misc

• Odoo

• Operators

• Statistical

• Text

• Web

Note

Formulas containing functions that are not compatible with Excel are replaced by their evaluated result when exporting a spreadsheet.

Array

Name and arguments	Description or link
ARRAY.CONSTRAIN(input_range, rows, columns)	Returns a result array constrained to a specific width and height (not compatible with Excel)
CHOOSECOLS(array, col_num, [col_num2, …])	Excel CHOOSECOLS article
CHOOSEROWS(array, row_num, [row_num2, …])	Excel CHOOSEROWS article
EXPAND(array, rows, [columns], [pad_with])	Excel EXPAND article
FLATTEN(range, [range2, …])	Flattens all the values from one or more ranges into a single column (not compatible with Excel)
FREQUENCY(data, classes)	Excel FREQUENCY article
HSTACK(range1, [range2, …])	Excel HSTACK article
MDETERM(square_matrix)	Excel MDETERM article
MINVERSE(square_matrix)	Excel MINVERSE article
MMULT(matrix1, matrix2)	Excel MMULT article
SUMPRODUCT(range1, [range2, …])	Excel SUMPRODUCT article
SUMX2MY2(array_x, array_y)	Excel SUMX2MY2 article
SUMX2PY2(array_x, array_y)	Excel SUMX2PY2 article
SUMXMY2(array_x, array_y)	Excel SUMXMY2 article
TOCOL(array, [ignore], [scan_by_column])	Excel TOCOL article
TOROW(array, [ignore], [scan_by_column])	Excel TOROW article
TRANSPOSE(range)	Excel TRANSPOSE article
VSTACK(range1, [range2, …])	Excel VSTACK article
WRAPCOLS(range, wrap_count, [pad_with])	Excel WRAPCOLS article
WRAPROWS(range, wrap_count, [pad_with])	Excel WRAPROWS article

Database

Name and arguments	Description or link
DAVERAGE(database, field, criteria)	Excel DAVERAGE article
DCOUNT(database, field, criteria)	Excel DCOUNT article
DCOUNTA(database, field, criteria)	Excel DCOUNTA article
DGET(database, field, criteria)	Excel DGET article
DMAX(database, field, criteria)	Excel DMAX article
DMIN(database, field, criteria)	Excel DMIN article
DPRODUCT(database, field, criteria)	Excel DPRODUCT article
DSTDEV(database, field, criteria)	Excel DSTDEV article
DSTDEVP(database, field, criteria)	Excel DSTDEVP article
DSUM(database, field, criteria)	Excel DSUM article
DVAR(database, field, criteria)	Excel DVAR article
DVARP(database, field, criteria)	Excel DVARP article

Date

Name and arguments	Description or link
DATE(year, month, day)	Excel DATE article
DATEDIF(start_date, end_date, unit)	Excel DATEDIF article
DATEVALUE(date_string)	Excel DATEVALUE article
DAY(date)	Excel DAY article
DAYS(end_date, start_date)	Excel DAYS article
DAYS360(start_date, end_date, [method])	Excel DAYS360 article
EDATE(start_date, months)	Excel EDATE article
EOMONTH(start_date, months)	Excel EOMONTH article
HOUR(time)	Excel HOUR article
ISOWEEKNUM(date)	Excel ISOWEEKNUM article
MINUTE(time)	Excel MINUTE article
MONTH(date)	Excel MONTH article
NETWORKDAYS(start_date, end_date, [holidays])	Excel NETWORKDAYS article
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])	Excel NETWORKDAYS.INTL article
NOW()	Excel NOW article
SECOND(time)	Excel SECOND article
TIME(hour, minute, second)	Excel TIME article
TIMEVALUE(time_string)	Excel TIMEVALUE article
TODAY()	Excel TODAY article
WEEKDAY(date, [type])	Excel WEEKDAY article
WEEKNUM(date, [type])	Excel WEEKNUM article
WORKDAY(start_date, num_days, [holidays])	Excel WORKDAY article
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])	Excel WORKDAY.INTL article
YEAR(date)	Excel YEAR article
YEARFRAC(start_date, end_date, [day_count_convention])	Exact number of years between two dates (not compatible with Excel)
MONTH.START(date)	First day of the month preceding a date (not compatible with Excel)
MONTH.END(date)	Last day of the month following a date (not compatible with Excel)
QUARTER(date)	Quarter of the year a specific date falls in (not compatible with Excel)
QUARTER.START(date)	First day of the quarter of the year a specific date falls in (not compatible with Excel)
QUARTER.END(date)	Last day of the quarter of the year a specific date falls in (not compatible with Excel)
YEAR.START(date)	First day of the year a specific date falls in (not compatible with Excel)
YEAR.END(date)	Last day of the year a specific date falls in (not compatible with Excel)

Engineering

Name and arguments	Description or link
DELTA(number1, [number2])	Excel DELTA article

Filter

Name and arguments	Description or link
FILTER(range, condition1, [condition2, …])	Excel FILTER article
UNIQUE(range, [by_column], [exactly_once])	Excel UNIQUE article

Financial

Name and arguments	Description or link
ACCRINTM(issue, maturity, rate, redemption, [day_count_convention])	Excel ACCRINTM article
AMORLINC(cost, purchase_date, first_period_end, salvage, period, rate, [day_count_convention])	Excel AMORLINC article
COUPDAYS(settlement, maturity, frequency, [day_count_convention])	Excel COUPDAYS article
COUPDAYBS(settlement, maturity, frequency, [day_count_convention])	Excel COUPDAYBS article
COUPDAYSNC(settlement, maturity, frequency, [day_count_convention])	Excel COUPDAYSNC article
COUPNCD(settlement, maturity, frequency, [day_count_convention])	Excel COUPNCD article
COUPNUM(settlement, maturity, frequency, [day_count_convention])	Excel COUPNUM article
COUPPCD(settlement, maturity, frequency, [day_count_convention])	Excel COUPPCD article
CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, [end_or_beginning])	Excel CUMIPMT article
CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, [end_or_beginning])	Excel CUMPRINC article
DB(cost, salvage, life, period, [month])	Excel DB article
DDB(cost, salvage, life, period, [factor])	Excel DDB article
DISC(settlement, maturity, price, redemption, [day_count_convention])	Excel DISC article
DOLLARDE(fractional_price, unit)	Excel DOLLARDE article
DOLLARFR(decimal_price, unit)	Excel DOLLARFR article
DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])	Excel DURATION article
EFFECT(nominal_rate, periods_per_year)	Excel EFFECT article
FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])	Excel FV article
FVSCHEDULE(principal, rate_schedule)	Excel FVSCHEDULE article
INTRATE(settlement, maturity, investment, redemption, [day_count_convention])	Excel INTRATE article
IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])	Excel IPMT article
IRR(cashflow_amounts, [rate_guess])	Excel IRR article
ISPMT(rate, period, number_of_periods, present_value)	Excel ISPMT article
MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])	Excel MDURATION article
MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)	Excel MIRR article
NOMINAL(effective_rate, periods_per_year)	Excel NOMINAL article
NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])	Excel NPER article
NPV(discount, cashflow1, [cashflow2, …])	Excel NPV article
PDURATION(rate, present_value, future_value)	Excel PDURATION article
PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])	Excel PMT article
PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])	Excel PPMT article
PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])	Excel PV article
PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])	Excel PRICE article
PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention])	Excel PRICEDISC article
PRICEMAT(settlement, maturity, issue, rate, yield, [day_count_convention])	Excel PRICEMAT article
RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])	Excel RATE article
RECEIVED(settlement, maturity, investment, discount, [day_count_convention])	Excel RECEIVED article
RRI(number_of_periods, present_value, future_value)	Excel RRI article
SLN(cost, salvage, life)	Excel SLN article
SYD(cost, salvage, life, period)	Excel SYD article
TBILLPRICE(settlement, maturity, discount)	Excel TBILLPRICE article
TBILLEQ(settlement, maturity, discount)	Excel TBILLEQ article
TBILLYIELD(settlement, maturity, price)	Excel TBILLYIELD article
VDB(cost, salvage, life, start, end, [factor], [no_switch])	Excel VDB article
XIRR(cashflow_amounts, cashflow_dates, [rate_guess])	Excel XIRR article
XNPV(discount, cashflow_amounts, cashflow_dates)	Excel XNPV article
YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])	Excel YIELD article
YIELDDISC(settlement, maturity, price, redemption, [day_count_convention])	Excel YIELDDISC article
YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention])	Excel YIELDMAT article

Info

Name and arguments	Description or link
CELL(info_type, reference)	Excel CELL article
ISERR(value)	Excel IS article
ISERROR(value)	Excel IS article
ISLOGICAL(value)	Excel IS article
ISNA(value)	Excel IS article
ISNONTEXT(value)	Excel IS article
ISNUMBER(value)	Excel IS article
ISTEXT(value)	Excel IS article
ISBLANK(value)	Excel IS article
NA()	Excel NA article

Logical

Name and arguments	Description or link
AND(logical_expression1, [logical_expression2, …])	Excel AND article
FALSE()	Excel FALSE article
IF(logical_expression, value_if_true, [value_if_false])	Excel IF article
IFERROR(value, [value_if_error])	Excel IFERROR article
IFNA(value, [value_if_error])	Excel IFNA article
IFS(condition1, value1, [condition2, …], [value2, …])	Excel IFS article
NOT(logical_expression)	Excel NOT article
OR(logical_expression1, [logical_expression2, …])	Excel OR article
TRUE()	Excel TRUE article
XOR(logical_expression1, [logical_expression2, …])	Excel XOR article

Lookup

Name and arguments	Description or link
ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])	Excel ADDRESS article
COLUMN([cell_reference])	Excel COLUMN article
COLUMNS(range)	Excel COLUMNS article
HLOOKUP(search_key, range, index, [is_sorted])	Excel HLOOKUP article
INDEX(reference, row, column)	Excel INDEX article
INDIRECT(reference, [use_a1_notation])	Excel INDIRECT article
LOOKUP(search_key, search_array, [result_range])	Excel LOOKUP article
MATCH(search_key, range, [search_type])	Excel MATCH article
PIVOT(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])	Get the value from a pivot (not compatible with Excel)
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …])	Get the header of a pivot (not compatible with Excel)
PIVOT.TABLE(pivot_id, [row_count], [include_total], [include_column_titles])	Get a pivot table (not compatible with Excel)
ROW([cell_reference])	Excel ROW article
ROWS(range)	Excel ROWS article
VLOOKUP(search_key, range, index, [is_sorted])	Excel VLOOKUP article
XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])	Excel XLOOKUP article

Math

Name and arguments	Description or link
ABS(value)	Excel ABS article
ACOS(value)	Excel ACOS article
ACOSH(value)	Excel ACOSH article
ACOT(value)	Excel ACOT article
ACOTH(value)	Excel ACOTH article
ASIN(value)	Excel ASIN article
ASINH(value)	Excel ASINH article
ATAN(value)	Excel ATAN article
ATAN2(x, y)	Excel ATAN2 article
ATANH(value)	Excel ATANH article
CEILING(value, [factor])	Excel CEILING article
CEILING.MATH(number, [significance], [mode])	Excel CEILING.MATH article
CEILING.PRECISE(number, [significance])	Excel CEILING.PRECISE article
COS(angle)	Excel COS article
COSH(value)	Excel COSH article
COT(angle)	Excel COT article
COTH(value)	Excel COTH article
COUNTBLANK(value1, [value2, …])	Excel COUNTBLANK article
COUNTIF(range, criterion)	Excel COUNTIF article
COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])	Excel COUNTIFS article
COUNTUNIQUE(value1, [value2, …])	Counts number of unique values in a range (not compatible with Excel)
COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])	Counts number of unique values in a range, filtered by a set of criteria (not compatible with Excel)
CSC(angle)	Excel CSC article
CSCH(value)	Excel CSCH article
DECIMAL(value, base)	Excel DECIMAL article
DEGREES(angle)	Excel DEGREES article
EXP(value)	Excel EXP article
FLOOR(value, [factor])	Excel FLOOR article
FLOOR.MATH(number, [significance], [mode])	Excel FLOOR.MATH article
FLOOR.PRECISE(number, [significance])	Excel FLOOR.PRECISE article
INT(value)	Excel INT article
ISEVEN(value)	Excel ISEVEN article
ISO.CEILING(number, [significance])	Excel ISO.CEILING article
ISODD(value)	Excel ISODD article
LN(value)	Excel LN article
MOD(dividend, divisor)	Excel MOD article
MUNIT(dimension)	Excel MUNIT article
ODD(value)	Excel ODD article
PI()	Excel PI article
POWER(base, exponent)	Excel POWER article
PRODUCT(factor1, [factor2, …])	Excel PRODUCT article
RAND()	Excel RAND article
RANDARRAY([rows], [columns], [min], [max], [whole_number])	Excel RANDARRAY article
RANDBETWEEN(low, high)	Excel RANDBETWEEN article
ROUND(value, [places])	Excel ROUND article
ROUNDDOWN(value, [places])	Excel ROUNDDOWN article
ROUNDUP(value, [places])	Excel ROUNDUP article
SEC(angle)	Excel SEC article
SECH(value)	Excel SECH article
SIN(angle)	Excel SIN article
SINH(value)	Excel SINH article
SQRT(value)	Excel SQRT article
SUM(value1, [value2, …])	Excel SUM article
SUMIF(criteria_range, criterion, [sum_range])	Excel SUMIF article
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])	Excel SUMIFS article
TAN(angle)	Excel TAN article
TANH(value)	Excel TANH article
TRUNC(value, [places])	Excel TRUNC article

Misc

Name and arguments	Description or link
FORMAT.LARGE.NUMBER(value, [unit])	Apply a large number format (not compatible with Excel)

Odoo

Name and arguments	Description or link
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted])	Get the total credit for the specified account(s) and period (not compatible with Excel)
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted])	Get the total debit for the specified account(s) and period (not compatible with Excel)
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted])	Get the total balance for the specified account(s) and period (not compatible with Excel)
ODOO.FISCALYEAR.START(day, [company_id])	Returns the starting date of the fiscal year encompassing the provided date (not compatible with Excel)
ODOO.FISCALYEAR.END(day, [company_id])	Returns the ending date of the fiscal year encompassing the provided date (not compatible with Excel)
ODOO.ACCOUNT.GROUP(type)	Returns the account ids of a given group (not compatible with Excel)
ODOO.CURRENCY.RATE(currency_from, currency_to, [date])	This function takes in two currency codes as arguments, and returns the exchange rate from the first currency to the second as float (not compatible with Excel)
ODOO.LIST(list_id, index, field_name)	Get the value from a list (not compatible with Excel)
ODOO.LIST.HEADER(list_id, field_name)	Get the header of a list (not compatible with Excel)
ODOO.FILTER.VALUE(filter_name)	Return the current value of a spreadsheet filter (not compatible with Excel)

Operators

Name and arguments	Description or link
ADD(value1, value2)	Sum of two numbers (not compatible with Excel)
CONCAT(value1, value2)	Excel CONCAT article
DIVIDE(dividend, divisor)	One number divided by another (not compatible with Excel)
EQ(value1, value2)	Equal (not compatible with Excel)
GT(value1, value2)	Strictly greater than (not compatible with Excel)
GTE(value1, value2)	Greater than or equal to (not compatible with Excel)
LT(value1, value2)	Less than (not compatible with Excel)
LTE(value1, value2)	Less than or equal to (not compatible with Excel)
MINUS(value1, value2)	Difference of two numbers (not compatible with Excel)
MULTIPLY(factor1, factor2)	Product of two numbers (not compatible with Excel)
NE(value1, value2)	Not equal (not compatible with Excel)
POW(base, exponent)	A number raised to a power (not compatible with Excel)
UMINUS(value)	A number with the sign reversed (not compatible with Excel)
UNARY.PERCENT(percentage)	Value interpreted as a percentage (not compatible with Excel)
UPLUS(value)	A specified number, unchanged (not compatible with Excel)

Statistical

Name and arguments	Description or link
AVEDEV(value1, [value2, …])	Excel AVEDEV article
AVERAGE(value1, [value2, …])	Excel AVERAGE article
AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …])	Weighted average (not compatible with Excel)
AVERAGEA(value1, [value2, …])	Excel AVERAGEA article
AVERAGEIF(criteria_range, criterion, [average_range])	Excel AVERAGEIF article
AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])	Excel AVERAGEIFS article
CORREL(data_y, data_x)	Excel CORREL article
COUNT(value1, [value2, …])	Excel COUNT article
COUNTA(value1, [value2, …])	Excel COUNTA article
COVAR(data_y, data_x)	Excel COVAR article
COVARIANCE.P(data_y, data_x)	Excel COVARIANCE.P article
COVARIANCE.S(data_y, data_x)	Excel COVARIANCE.S article
FORECAST(x, data_y, data_x)	Excel FORECAST article
GROWTH(known_data_y, [known_data_x], [new_data_x], [b])	Fits points to exponential growth trend (not compatible with Excel)
INTERCEPT(data_y, data_x)	Excel INTERCEPT article
LARGE(data, n)	Excel LARGE article
LINEST(data_y, [data_x], [calculate_b], [verbose])	Excel LINEST article
LOGEST(data_y, [data_x], [calculate_b], [verbose])	Excel LOGEST article
MATTHEWS(data_x, data_y)	Compute the Matthews correlation coefficient of a dataset (not compatible with Excel)
MAX(value1, [value2, …])	Excel MAX article
MAXA(value1, [value2, …])	Excel MAXA article
MAXIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])	Excel MAXIFS article
MEDIAN(value1, [value2, …])	Excel MEDIAN article
MIN(value1, [value2, …])	Excel MIN article
MINA(value1, [value2, …])	Excel MINA article
MINIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])	Excel MINIFS article
PEARSON(data_y, data_x)	Excel PEARSON article
PERCENTILE(data, percentile)	Excel PERCENTILE article
PERCENTILE.EXC(data, percentile)	Excel PERCENTILE.EXC article
PERCENTILE.INC(data, percentile)	Excel PERCENTILE.INC article
POLYFIT.COEFFS(data_y, data_x, order, [intercept])	Compute the coefficients of polynomial regression of the dataset (not compatible with Excel)
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept])	Predict value by computing a polynomial regression of the dataset (not compatible with Excel)
QUARTILE(data, quartile_number)	Excel QUARTILE article
QUARTILE.EXC(data, quartile_number)	Excel QUARTILE.EXC article
QUARTILE.INC(data, quartile_number)	Excel QUARTILE.INC article
RANK(value, data, [is_ascending])	Excel RANK article
RSQ(data_y, data_x)	Excel RSQ article
SMALL(data, n)	Excel SMALL article
SLOPE(data_y, data_x)	Excel SLOPE article
SPEARMAN(data_y, data_x)	Compute the Spearman rank correlation coefficient of a dataset (not compatible with Excel)
STDEV(value1, [value2, …])	Excel STDEV article
STDEV.P(value1, [value2, …])	Excel STDEV.P article
STDEV.S(value1, [value2, …])	Excel STDEV.S article
STDEVA(value1, [value2, …])	Excel STDEVA article
STDEVP(value1, [value2, …])	Excel STDEVP article
STDEVPA(value1, [value2, …])	Excel STDEVPA article
STEYX(data_y, data_x)	Excel STEYX article
TREND(known_data_y, [known_data_x], [new_data_x], [b])	Fits points to linear trend derived via least-squares (not compatible with Excel)
VAR(value1, [value2, …])	Excel VAR article
VAR.P(value1, [value2, …])	Excel VAR.P article
VAR.S(value1, [value2, …])	Excel VAR.S article
VARA(value1, [value2, …])	Excel VARA article
VARP(value1, [value2, …])	Excel VARP article
VARPA(value1, [value2, …])	Excel VARPA article

Text

Name and arguments	Description or link
CHAR(table_number)	Excel CHAR article
CLEAN(text)	Excel CLEAN article
CONCATENATE(string1, [string2, …])	Excel CONCATENATE article
EXACT(string1, string2)	Excel EXACT article
FIND(search_for, text_to_search, [starting_at])	Excel FIND article
JOIN(delimiter, value_or_array1, [value_or_array2, …])	Concatenates elements of arrays with delimiter (not compatible with Excel)
LEFT(text, [number_of_characters])	Excel LEFT article
LEN(text)	Excel LEN article
LOWER(text)	Excel LOWER article
MID(text, starting_at, extract_length)	Excel MID article
PROPER(text_to_capitalize)	Excel PROPER article
REPLACE(text, position, length, new_text)	Excel REPLACE article
RIGHT(text, [number_of_characters])	Excel RIGHT article
SEARCH(search_for, text_to_search, [starting_at])	Excel SEARCH article
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])	Excel TEXTSPLIT article
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])	Excel SUBSTITUTE article
TEXT(number, format)	Excel TEXT article
TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])	Excel TEXTJOIN article
TRIM(text)	Excel TRIM article
UPPER(text)	Excel UPPER article

Web

Name and arguments	Description or link
HYPERLINK(url, [link_label])	Excel HYPERLINK article