Formulas and functions

Odoo Spreadsheet supports functions found in most spreadsheet solutions, allowing you to perform calculations, retrieve information, and manipulate data using formulas. In addition, using Odoo-specific functions allows you to work with live Odoo data from your database.

To help ensure data accuracy, Odoo Spreadsheet offers various ways to troubleshoot errors and inconsistencies in formulas and functions.

Tip

Use F2 to view the formula behind an active cell, and, if desired, edit it directly in the cell.

Available functions

This section presents the available functions by category. Odoo-specific functions are included both in the relevant category and in a dedicated Odoo category:

• Array

• Database

• Date

• Engineering

• Filter

• Financial

• Info

• Logical

• Lookup

• Math

• Operators

• Parser

• Statistical

• Text

• Web

• Odoo-specific functions

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
MONTH.END(date)	Last day of the month following a date (not compatible with Excel)
MONTH.START(date)	First day of the month preceding a date (not compatible with Excel)
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
QUARTER(date)	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)
QUARTER.START(date)	First day of the quarter of the year a specific date falls in (not compatible with Excel)
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
YEAR.END(date)	Last day 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)
YEARFRAC(start_date, end_date, [day_count_convention])	Exact number of years between two dates (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
ODOO.FILTER.LABEL(filter_name)	Returns the label of the current value of a spreadsheet filter (not compatible with Excel)
ODOO.FILTER.VALUE(filter_name)	Returns the current value of a spreadsheet filter (not compatible with Excel)
SORT(range, [sort_column, …], [is_ascending, …])	Excel SORT 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
COUPDAYBS(settlement, maturity, frequency, [day_count_convention])	Excel COUPDAYBS article
COUPDAYS(settlement, maturity, frequency, [day_count_convention])	Excel COUPDAYS 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
ODOO.ACCOUNT.GROUP(type)	Returns the account ids of a given group (not compatible with Excel)
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted])	Returns the total balance for the specified account(s) and period (not compatible with Excel)
ODOO.BALANCE.TAG(account_tag_ids, [date_range], [offset], [company_id], [include_unposted])	Returns the balance of accounts for the specified tag(s) and period (not compatible with Excel)
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted])	Returns the total credit for the specified account(s) and period (not compatible with Excel)
ODOO.CURRENCY.RATE(currency_from, currency_to, [date])	Takes two currency codes as arguments, and returns the exchange rate from the first currency to the second as float (not compatible with Excel)
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted])	Returns the total debit for the specified account(s) and period (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.FISCALYEAR.START(day, [company_id])	Returns the starting date of the fiscal year encompassing the provided date (not compatible with Excel)
ODOO.PARTNER.BALANCE(partner_ids, [account_codes], [date_range], [offset], [company_id], [include_unposted])	Returns the partner balance for the specified account(s) and period (not compatible with Excel)
ODOO.RESIDUAL([account_codes], [date_range], [offset], [company_id], [include_unposted])	Returns the residual amount for the specified account(s) and period (not compatible with Excel)
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
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
PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])	Excel PV 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
TBILLEQ(settlement, maturity, discount)	Excel TBILLEQ article
TBILLPRICE(settlement, maturity, discount)	Excel TBILLPRICE 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
ISBLANK(value)	Excel IS article
ISERR(value)	Excel IS article
ISERROR(value)	Excel IS article
ISFORMULA(cell_reference)	Excel ISFORMULA 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
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
SWITCH(expression, case1, value1, [case2, …], [value2, …], [default])	Excel SWITCH 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
CHOOSE(index, [choice, …])	Excel CHOOSE article
COLUMN([cell_reference])	Excel COLUMN article
COLUMNS(range)	Excel COLUMNS article
DROP(array, rows, [columns])	Excel DROP article
FORMULATEXT(cell_reference)	Excel FORMULATEXT 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
OFFSET(reference, rows, cols, [height], [width])	Excel OFFSET article
PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count])	Creates a pivot table (not compatible with Excel)
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …])	Returns the header of a pivot table (not compatible with Excel)
PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])	Returns the value from a pivot table (not compatible with Excel)
ROW([cell_reference])	Excel ROW article
ROWS(range)	Excel ROWS article
TAKE(array, rows, [columns])	Excel TAKE 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
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
LOG(value, [base])	Returns the logarithm of a number for a given base (not compatible with Excel)
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
SEQUENCE(rows, [columns], [start], ][step])	Excel SEQUENCE article
SIN(angle)	Excel SIN article
SINH(value)	Excel SINH article
SQRT(value)	Excel SQRT article
SUBTOTAL(function_code, ref1, [ref2, …])	Excel SUBTOTAL 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

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)

Parser

Name and arguments	Description or link
CONVERT(number, from_unit, to_unit)	Excel CONVERT article

Statistical

Name and arguments	Description or link
AVEDEV(value1, [value2, …])	Excel AVEDEV article
AVERAGE(value1, [value2, …])	Excel AVERAGE article
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
AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …])	Weighted average (not compatible with Excel)
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)	Computes 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])	Computes the coefficients of polynomial regression of the dataset (not compatible with Excel)
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept])	Predicts 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
SLOPE(data_y, data_x)	Excel SLOPE article
SMALL(data, n)	Excel SMALL article
SPEARMAN(data_y, data_x)	Computes 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
ARRAYTOTEXT(array, [format])	Excel ARRAYTOTEXT article
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
REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])	Excel REGEXEXTRACT 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])	Splits text by specific character delimiter(s) (not compatible with Excel)
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])	Excel SUBSTITUTE article
TEXT(number, format)	Excel TEXT article
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])	Excel TEXTAFTER article
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])	Excel TEXTBEFORE article
TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])	Excel TEXTJOIN article
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])	Excel TEXTSPLIT article
TRIM(text)	Excel TRIM article
UPPER(text)	Excel UPPER article
VALUE(text)	Excel VALUE article

Web

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

Odoo-specific functions

This section contains functions that interact directly with your Odoo database.

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)
FLATTEN(range, [range2, …])	Flattens all the values from one or more ranges into a single column (not compatible with Excel)

Date

Name and arguments	Description or link
MONTH.END(date)	Last day of the month following a date (not compatible with Excel)
MONTH.START(date)	First day of the month preceding a date (not compatible with Excel)
QUARTER(date)	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)
QUARTER.START(date)	First day of the quarter 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)
YEAR.START(date)	First day of the year a specific date falls in (not compatible with Excel)
YEARFRAC(start_date, end_date, [day_count_convention])	Exact number of years between two dates (not compatible with Excel)

Financial

Name and arguments	Description or link
ODOO.ACCOUNT.GROUP(type)	Returns the account ids of a given group (not compatible with Excel)
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted])	Returns the total balance for the specified account(s) and period (not compatible with Excel)
ODOO.BALANCE.TAG(account_tag_ids, [date_range], [offset], [company_id], [include_unposted])	Returns the balance of accounts for the specified tag(s) and period (not compatible with Excel)
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted])	Returns the total credit for the specified account(s) and period (not compatible with Excel)
ODOO.CURRENCY.RATE(currency_from, currency_to, [date])	Takes two currency codes as arguments, and returns the exchange rate from the first currency to the second as float (not compatible with Excel)
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted])	Returns the total debit 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.PARTNER.BALANCE(partner_ids, [account_codes], [date_range], [offset], [company_id], [include_unposted])	Returns the partner balance for the specified account(s) and period (not compatible with Excel)
ODOO.RESIDUAL([account_codes], [date_range], [offset], [company_id], [include_unposted])	Returns the residual amount for the specified account(s) and period (not compatible with Excel)

Lookup

Name and arguments	Description or link
PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count])	Creates a pivot table (not compatible with Excel)
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …])	Returns the header of a pivot table (not compatible with Excel)
PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])	Returns the value from a pivot table (not compatible with Excel)

Math

Name and arguments	Description or link
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)

Misc

Name and arguments	Description or link
FORMAT.LARGE.NUMBER(value, [unit])	Applies a large number format (not compatible with Excel)
ODOO.LIST(list_id, index, field_name)	Returns the value from a list (not compatible with Excel)
ODOO.LIST.HEADER(list_id, field_name)	Returns the header of a list (not compatible with Excel)
ODOO.SURVEY(survey_id)	Returns the results of an Odoo survey (not compatible with Excel)

Operators

Name and arguments	Description or link
ADD(value1, value2)	Sum of two numbers (not compatible with Excel)
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
AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …])	Weighted average (not compatible with Excel)
GROWTH(known_data_y, [known_data_x], [new_data_x], [b])	Fits points to exponential growth trend (not compatible with Excel)
MATTHEWS(data_x, data_y)	Computes the Matthews correlation coefficient of a dataset (not compatible with Excel)
POLYFIT.COEFFS(data_y, data_x, order, [intercept])	Computes the coefficients of polynomial regression of the dataset (not compatible with Excel)
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept])	Predicts value by computing a polynomial regression of the dataset (not compatible with Excel)
SPEARMAN(data_y, data_x)	Computes the Spearman rank correlation coefficient of a dataset (not compatible with Excel)
TREND(known_data_y, [known_data_x], [new_data_x], [b])	Fits points to linear trend derived via least-squares (not compatible with Excel)

Text

Name and arguments	Description or link
JOIN(delimiter, value_or_array1, [value_or_array2, …])	Concatenates elements of arrays with delimiter (not compatible with Excel)

Troubleshooting

Understand errors

When a formula is unable to return a value, the cell displays an error message that identifies the type of error. For example, #N/A indicates that a value is not found when using a lookup function, #NAME? indicates that a function name is misspelled or unrecognised, while #BAD_EXPR indicates an issue with the arguments of a function.

Hovering over a cell with an error message reveals a card that provides more details about the error.

Tip

• Use the IFERROR function to provide an alternative result when an error occurs. For example, to avoid multiple #N/A errors when using a VLOOKUP function, the following formula returns Not Found instead of #N/A: =IFERROR(VLOOKUP(D1, A:B, 1, 0), "Not Found").

• In a large dataset, use the Irregularity map to visually highlight cells whose formulas are not consistent with the pattern established by surrounding cells.

Detect formula inconsistencies

Issues with formulas, such as accidentally overwriting a formula with a fixed value, breaking a cell reference, or not respecting the arguments of a function, can result in incorrect values being displayed. In a large dataset, such errors can be particularly difficult to identify manually.

Odoo Spreadsheet’s Irregularity map analyzes formulas for patterns and highlights anomalies visually: cells whose formulas follow the same pattern share the same color, while anomalies are shown in a different color.

To use the tool, click View ‣ Irregularity map from the menu bar, then check the spreadsheet for inconsistencies. To turn off the irregularity map, click Irregularity map or Turn off on the banner below the toolbar.

Example

In the example (in which the formulas have been made visible by selecting View ‣ Show ‣ Formulas from the menu bar), column D contains the amount of commission earned over the first and second quarters. This is calculated by adding the sales of the two quarters, shown in columns B and C, then multiplying it by the bonus rate in cell G1.

The irregularity map identifies the patterns in the formulas in column D. Cells that follow the pattern of the initial formula used in cell D2 have the same red color, while the following anomalies are highlighted separately:

• Cell D4: This cell features a static value instead of the formula.

• Cell D5: The formula in this cell does not follow the pattern of the initial formula.

• Cells D8 and D9: Cell D8 is missing the absolute reference ($) on G1; when the formula is dragged to cell D9, this creates a referencing error because cell G2 is empty. As the formulas in the two cells follow the same pattern, they share the same color.