Functions

Spreadsheet functions are divided in the following categories:

注解

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

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

LOOKUP(search_key, search_array, [result_range])

Excel LOOKUP article

MATCH(search_key, range, [search_type])

Excel MATCH article

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

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)

ODOO.PIVOT(pivot_id, measure_name, [domain_field_name, …], [domain_value, …])

Get the value from a pivot (not compatible with Excel)

ODOO.PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …])

Get the header of a pivot (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

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

LARGE(data, n)

Excel LARGE article

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

PERCENTILE(data, percentile)

Excel PERCENTILE article

PERCENTILE.EXC(data, percentile)

Excel PERCENTILE.EXC article

PERCENTILE.INC(data, percentile)

Excel PERCENTILE.INC article

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

SMALL(data, n)

Excel SMALL article

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

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