Functions

Expanse recognises 345 functions. The most-used ones run on a native, columnar fast path; the rest evaluate through an Excel-compatible engine. Either way the formula syntax, argument order, and results match what you’d type in Excel.

The Detailed reference below documents the curated set with full argument notes and examples. The Full catalog at the bottom lists every recognised name.

Detailed reference

ABS

ABS(number)

Returns the absolute value.

Argument	Type	Required	Example	Description
number	value	required	-3	Value to take the absolute value of.

ACCRINT

ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

Returns the accrued interest for a security that pays periodic interest.

Argument	Type	Required	Example	Description
issue	value	required	DATE(2008,3,1)	The security’s issue date.
first_interest	value	required	DATE(2008,8,31)	The security’s first interest date.
settlement	value	required	DATE(2008,5,1)	The security’s settlement (purchase) date.
rate	value	required	0.1	The security’s annual coupon rate.
par	value	required	1000	The security’s par value (defaults to 1000).
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	0	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.
calc_method	value	optional	TRUE	TRUE accrues from issue, FALSE from first interest (default TRUE).

ACCRINTM

ACCRINTM(issue, settlement, rate, [par], [basis])

Returns the accrued interest for a security that pays interest at maturity.

Argument	Type	Required	Example	Description
issue	value	required	DATE(2008,4,1)	The security’s issue date.
settlement	value	required	DATE(2008,6,15)	The security’s maturity (settlement) date.
rate	value	required	0.1	The security’s annual coupon rate.
par	value	optional	1000	Par value of the security (default 1000).
basis	value	optional	3	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

AND

AND(logical1, [logical2], ...)

Returns TRUE only if all arguments are TRUE.

Argument	Type	Required	Example	Description
logical1	logical	required	A1>0	First condition.
logical2	logical	repeatable	B1>0	Additional conditions.

AVERAGE

AVERAGE(number1, [number2], ...)

Returns the arithmetic mean of its arguments.

Argument	Type	Required	Example	Description
number1	value	required	A1:A10	First value or range.
number2	value	repeatable	B1:B10	Additional values or ranges.

AVERAGEIF

AVERAGEIF(range, criteria, [average_range])

Averages cells matching criteria.

Argument	Type	Required	Example	Description
range	range	required	A1:A10	Range tested against criteria.
criteria	any	required	">0"	Condition.
average_range	range	optional	B1:B10	Range to average (defaults to range).

CHOOSE

CHOOSE(index_num, value1, [value2], ...)

Picks the value at index_num.

Argument	Type	Required	Example	Description
index_num	value	required	2	1-based selector.
value1	any	required	"red"	First option.
value2	any	repeatable	"green"	Additional options.

CHOOSECOLS

CHOOSECOLS(array, col_num1, [col_num2], ...)

Returns the chosen columns of an array, in the order given.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to choose from.
col_num1	value	required	1	1-based column to return; a negative counts from the end.
col_num2	value	repeatable	3	Additional columns to return.

CHOOSEROWS

CHOOSEROWS(array, row_num1, [row_num2], ...)

Returns the chosen rows of an array, in the order given.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to choose from.
row_num1	value	required	1	1-based row to return; a negative counts from the end.
row_num2	value	repeatable	3	Additional rows to return.

CONCAT

CONCAT(text1, [text2], ...)

Joins text strings into one.

Argument	Type	Required	Example	Description
text1	text	required	"hello"	First string or range.
text2	text	repeatable	" world"	Additional strings or ranges.

CONCATENATE

CONCATENATE(text1, [text2], ...)

Joins text strings into one.

Argument	Type	Required	Example	Description
text1	text	required	"hello"	First string or range.
text2	text	repeatable	" world"	Additional strings or ranges.

COUNT

COUNT(value1, [value2], ...)

Counts how many cells contain numbers.

Argument	Type	Required	Example	Description
value1	any	required	A1:A10	First value or range to count.
value2	any	repeatable	B1:B10	Additional values or ranges.

COUNTA

COUNTA(value1, [value2], ...)

Counts how many cells are not empty.

Argument	Type	Required	Example	Description
value1	any	required	A1:A10	First value or range.
value2	any	repeatable	B1:B10	Additional values or ranges.

COUNTIF

COUNTIF(range, criteria)

Counts cells in range matching criteria.

Argument	Type	Required	Example	Description
range	range	required	A1:A10	Range to test.
criteria	any	required	">0"	Condition.

COUNTIFS

COUNTIFS(criteria_range1, criteria1, [criteria_range2], ...)

Counts cells satisfying multiple criteria.

Argument	Type	Required	Example	Description
criteria_range1	range	required	A1:A10	First range tested.
criteria1	any	required	">0"	First condition.
criteria_range2	range	repeatable	B1:B10	Additional range/criteria pairs.

COUPDAYBS

COUPDAYBS(settlement, maturity, frequency, [basis])

Returns the number of days from the start of the coupon period to settlement.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2011,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2011,11,15)	The security’s maturity date.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

COUPDAYS

COUPDAYS(settlement, maturity, frequency, [basis])

Returns the number of days in the coupon period containing the settlement date.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2011,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2011,11,15)	The security’s maturity date.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

COUPDAYSNC

COUPDAYSNC(settlement, maturity, frequency, [basis])

Returns the number of days from settlement to the next coupon date.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2011,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2011,11,15)	The security’s maturity date.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

COUPNCD

COUPNCD(settlement, maturity, frequency, [basis])

Returns the next coupon date after the settlement date.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2011,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2011,11,15)	The security’s maturity date.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

COUPNUM

COUPNUM(settlement, maturity, frequency, [basis])

Returns the number of coupons payable between settlement and maturity.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2011,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2011,11,15)	The security’s maturity date.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

COUPPCD

COUPPCD(settlement, maturity, frequency, [basis])

Returns the previous coupon date before the settlement date.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2011,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2011,11,15)	The security’s maturity date.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

DATE

DATE(year, month, day)

Date serial number for the given year/month/day.

Argument	Type	Required	Example	Description
year	value	required	2026	Four-digit year.
month	value	required	3	Month (1-12).
day	value	required	15	Day of month.

DAY

DAY(serial_number)

Day component of a date.

Argument	Type	Required	Example	Description
serial_number	value	required	A1	Date value.

DISC

DISC(settlement, maturity, pr, redemption, [basis])

Returns the discount rate for a security.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2007,1,25)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2007,6,15)	The security’s maturity date.
pr	value	required	97.975	Price per $100 face value.
redemption	value	required	100	Redemption value per $100 face value.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

DROP

DROP(array, rows, [columns])

Removes rows/columns from the start or end of an array.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to subset.
rows	value	required	1	Rows to drop; a negative count drops from the end.
columns	value	optional	1	Columns to drop; a negative count drops from the end.

DURATION

DURATION(settlement, maturity, coupon, yld, frequency, [basis])

Returns the Macaulay duration of a security with an assumed $100 par value.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2018,7,1)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2048,1,1)	The security’s maturity date.
coupon	value	required	0.08	The security’s annual coupon rate.
yld	value	required	0.09	The security’s annual yield.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

EXPAND

EXPAND(array, rows, [columns], [pad_with])

Pads an array out to the given number of rows and columns.

Argument	Type	Required	Example	Description
array	range	required	A1:B2	Range or array to expand.
rows	value	required	5	Target row count.
columns	value	optional	3	Target column count (default: current width).
pad_with	any	optional	0	Value for the new cells (default #N/A).

FILTER

FILTER(array, include, [if_empty])

Returns the rows of a range that meet a condition.

Argument	Type	Required	Example	Description
array	range	required	A1:C100	Range or array to filter.
include	range	required	C1:C100	Boolean column the same height as array; TRUE keeps the row.
if_empty	any	optional	"none"	Returned when nothing matches.

FVSCHEDULE

FVSCHEDULE(principal, schedule)

Returns the future value of a principal after applying a series of compound rates.

Argument	Type	Required	Example	Description
principal	value	required	1	The present value to compound.
schedule	range	required	B1:B3	Range of interest rates to apply in sequence.

HLOOKUP

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Looks up a value in the first row and returns a value from the same column.

Argument	Type	Required	Example	Description
lookup_value	any	required	"Q1"	Value to find.
table_array	range	required	A1:Z5	Range to search.
row_index_num	value	required	2	Row to return (1-based).
range_lookup	logical	optional	FALSE	FALSE for exact match, TRUE for approximate.

HSTACK

HSTACK(array1, [array2], ...)

Stacks ranges side by side into one wider array.

Argument	Type	Required	Example	Description
array1	range	required	A1:A3	First range to stack.
array2	range	repeatable	B1:B3	Additional ranges, placed to the right.

IF

IF(logical_test, value_if_true, [value_if_false])

Returns one of two values depending on a condition.

Argument	Type	Required	Example	Description
logical_test	logical	required	A1>0	Condition that returns TRUE/FALSE.
value_if_true	any	required	"yes"	Returned when the test is TRUE.
value_if_false	any	optional	"no"	Returned when the test is FALSE.

IFERROR

IFERROR(value, value_if_error)

Returns value_if_error when value is an error.

Argument	Type	Required	Example	Description
value	any	required	A1/B1	Expression to evaluate.
value_if_error	any	required	0	Fallback when value is an error.

IFNA

IFNA(value, value_if_na)

Returns value_if_na when value is #N/A.

Argument	Type	Required	Example	Description
value	any	required	VLOOKUP(...)	Expression to evaluate.
value_if_na	any	required	"not found"	Fallback when value is #N/A.

IFS

IFS(logical_test1, value1, [logical_test2], ...)

Returns the value for the first true test.

Argument	Type	Required	Example	Description
logical_test1	logical	required	A1>0	First condition.
value1	any	required	"positive"	Value when first test is TRUE.
logical_test2	logical	repeatable	A1=0	Additional test/value pairs.

INDEX

INDEX(array, row_num, [column_num])

Returns the value at a given row/column position.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to index into.
row_num	value	required	3	1-based row position.
column_num	value	optional	2	1-based column position.

INT

INT(number)

Rounds down to the nearest integer.

Argument	Type	Required	Example	Description
number	value	required	3.7	Value to truncate downward.

INTRATE

INTRATE(settlement, maturity, investment, redemption, [basis])

Returns the interest rate for a fully invested security.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,15)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2008,5,15)	The security’s maturity date.
investment	value	required	1000000	Amount invested in the security.
redemption	value	required	1014420	Amount received at maturity.
basis	value	optional	2	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

LEFT

LEFT(text, [num_chars])

Leftmost num_chars characters of text.

Argument	Type	Required	Example	Description
text	text	required	"hello"	Source string.
num_chars	value	optional	3	Characters to take (default 1).

LEN

LEN(text)

Number of characters in text.

Argument	Type	Required	Example	Description
text	text	required	"hello"	String to measure.

LOWER

LOWER(text)

Converts text to lowercase.

Argument	Type	Required	Example	Description
text	text	required	"HELLO"	String to lowercase.

MATCH

MATCH(lookup_value, lookup_array, [match_type])

Returns the relative position of a match.

Argument	Type	Required	Example	Description
lookup_value	any	required	"apple"	Value to find.
lookup_array	range	required	A1:A100	Range to search.
match_type	value	optional	0	0 exact, 1 ≤, -1 ≥.

MAX

MAX(number1, [number2], ...)

Returns the largest value.

Argument	Type	Required	Example	Description
number1	value	required	A1:A10	First value or range.
number2	value	repeatable	B1:B10	Additional values or ranges.

MDURATION

MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Returns the modified Macaulay duration of a security with an assumed $100 par value.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2018,7,1)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2048,1,1)	The security’s maturity date.
coupon	value	required	0.08	The security’s annual coupon rate.
yld	value	required	0.09	The security’s annual yield.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	1	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

MID

MID(text, start_num, num_chars)

Substring of text starting at start_num.

Argument	Type	Required	Example	Description
text	text	required	"hello"	Source string.
start_num	value	required	2	1-based start position.
num_chars	value	required	3	Characters to take.

MIN

MIN(number1, [number2], ...)

Returns the smallest value.

Argument	Type	Required	Example	Description
number1	value	required	A1:A10	First value or range.
number2	value	repeatable	B1:B10	Additional values or ranges.

MOD

MOD(number, divisor)

Returns the remainder.

Argument	Type	Required	Example	Description
number	value	required	10	Numerator.
divisor	value	required	3	Divisor.

MONTH

MONTH(serial_number)

Month component of a date.

Argument	Type	Required	Example	Description
serial_number	value	required	A1	Date value.

NOT

NOT(logical)

Reverses the logical value.

Argument	Type	Required	Example	Description
logical	logical	required	A1>0	Condition to negate.

NOW

NOW()

Current date and time.

OR

OR(logical1, [logical2], ...)

Returns TRUE if any argument is TRUE.

Argument	Type	Required	Example	Description
logical1	logical	required	A1>0	First condition.
logical2	logical	repeatable	B1>0	Additional conditions.

POWER

POWER(number, power)

Raises number to power.

Argument	Type	Required	Example	Description
number	value	required	2	Base.
power	value	required	10	Exponent.

PRICE

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

Returns the price per $100 face value of a security that pays periodic interest.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,15)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2017,11,15)	The security’s maturity date.
rate	value	required	0.0575	The security’s annual coupon rate.
yld	value	required	0.065	The security’s annual yield.
redemption	value	required	100	The redemption value per $100 face value.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	0	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

PRICEDISC

PRICEDISC(settlement, maturity, discount, redemption, [basis])

Returns the price per $100 face value of a discounted security.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,16)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2008,3,1)	The security’s maturity date.
discount	value	required	0.0525	The security’s discount rate.
redemption	value	required	100	Redemption value per $100 face value.
basis	value	optional	2	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

PRICEMAT

PRICEMAT(settlement, maturity, issue, rate, yld, [basis])

Returns the price per $100 face value of a security that pays interest at maturity.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,15)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2008,4,13)	The security’s maturity date.
issue	value	required	DATE(2007,11,11)	The security’s issue date.
rate	value	required	0.061	The security’s interest rate at issue.
yld	value	required	0.061	The security’s annual yield.
basis	value	optional	0	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

PRODUCT

PRODUCT(number1, [number2], ...)

Multiplies its arguments.

Argument	Type	Required	Example	Description
number1	value	required	A1:A10	First value or range to multiply.
number2	value	repeatable	B1:B10	Additional values or ranges.

RANDARRAY

RANDARRAY([rows], [columns], [min], [max], [whole_number])

Returns an array of random numbers between a minimum and maximum.

Argument	Type	Required	Example	Description
rows	value	optional	3	Number of rows (default 1).
columns	value	optional	2	Number of columns (default 1).
min	value	optional	1	Smallest value (default 0).
max	value	optional	100	Largest value (default 1).
whole_number	logical	optional	TRUE	TRUE to return integers instead of decimals.

RECEIVED

RECEIVED(settlement, maturity, investment, discount, [basis])

Returns the amount received at maturity for a fully invested security.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,15)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2008,5,15)	The security’s maturity date.
investment	value	required	1000000	Amount invested in the security.
discount	value	required	0.0575	The security’s discount rate.
basis	value	optional	2	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

RIGHT

RIGHT(text, [num_chars])

Rightmost num_chars characters of text.

Argument	Type	Required	Example	Description
text	text	required	"hello"	Source string.
num_chars	value	optional	3	Characters to take (default 1).

ROUND

ROUND(number, num_digits)

Rounds to a specified number of digits.

Argument	Type	Required	Example	Description
number	value	required	3.14159	Value to round.
num_digits	value	required	2	Number of digits to round to.

SEQUENCE

SEQUENCE(rows, [columns], [start], [step])

Returns an array of sequential numbers.

Argument	Type	Required	Example	Description
rows	value	required	3	Number of rows to fill.
columns	value	optional	2	Number of columns (default 1).
start	value	optional	10	First value (default 1).
step	value	optional	5	Increment between values (default 1).

SORT

SORT(array, [sort_index], [sort_order], [by_col])

Sorts the rows of a range or array.

Argument	Type	Required	Example	Description
array	range	required	A1:C100	Range or array to sort.
sort_index	value	optional	1	1-based column to sort by (default 1).
sort_order	value	optional	1	1 ascending (default), -1 descending.
by_col	logical	optional	FALSE	TRUE to sort columns instead of rows.

SORTBY

SORTBY(array, by_array, [sort_order], [by_array2], ...)

Sorts a range by the values in one or more corresponding ranges.

Argument	Type	Required	Example	Description
array	range	required	A1:C100	Range or array to sort.
by_array	range	required	D1:D100	Values to sort by (same height as array).
sort_order	value	optional	1	1 ascending (default), -1 descending.
by_array2	range	repeatable	E1:E100	Additional by-range / order pairs.

SQRT

SQRT(number)

Returns the positive square root.

Argument	Type	Required	Example	Description
number	value	required	16	Value (must be non-negative).

SUM

SUM(number1, [number2], ...)

Adds its arguments.

Argument	Type	Required	Example	Description
number1	value	required	A1:A10	First value or range to add.
number2	value	repeatable	B1:B10	Additional values or ranges.

SUMIF

SUMIF(range, criteria, [sum_range])

Adds cells in sum_range where the matching cell in range meets criteria.

Argument	Type	Required	Example	Description
range	range	required	A1:A10	Range tested against criteria.
criteria	any	required	">0"	Condition (e.g. ">0", "=apple").
sum_range	range	optional	B1:B10	Range to add (defaults to range).

SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], ...)

Adds cells satisfying multiple criteria.

Argument	Type	Required	Example	Description
sum_range	range	required	C1:C10	Range whose cells to add.
criteria_range1	range	required	A1:A10	First range tested.
criteria1	any	required	">0"	First condition.
criteria_range2	range	repeatable	B1:B10	Additional range/criteria pairs.

SWITCH

SWITCH(expression, value1, result1, [default_or_value2], ...)

Picks the result for the first matching value.

Argument	Type	Required	Example	Description
expression	any	required	A1	Value to compare against.
value1	any	required	1	First candidate value.
result1	any	required	"one"	Result when matched.
default_or_value2	any	repeatable	2	Default, or more value/result pairs.

TAKE

TAKE(array, rows, [columns])

Returns the first or last rows/columns of an array.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to subset.
rows	value	required	2	Rows to keep; a negative count takes from the end.
columns	value	optional	2	Columns to keep; a negative count takes from the end.

TEXTJOIN

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Joins text strings with a delimiter.

Argument	Type	Required	Example	Description
delimiter	text	required	", "	Separator between values.
ignore_empty	logical	required	TRUE	TRUE to skip empty strings.
text1	text	required	A1:A10	First string or range.
text2	text	repeatable	B1:B10	Additional strings or ranges.

TOCOL

TOCOL(array, [ignore], [scan_by_column])

Returns the array as a single column.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to flatten.
ignore	value	optional	0	0 keep all, 1 skip blanks, 2 skip errors, 3 skip both.
scan_by_column	logical	optional	FALSE	TRUE to scan down columns first.

TODAY

TODAY()

Current date.

TOROW

TOROW(array, [ignore], [scan_by_column])

Returns the array as a single row.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to flatten.
ignore	value	optional	0	0 keep all, 1 skip blanks, 2 skip errors, 3 skip both.
scan_by_column	logical	optional	FALSE	TRUE to scan down columns first.

TRANSPOSE

TRANSPOSE(array)

Flips an array so rows become columns and vice versa.

Argument	Type	Required	Example	Description
array	range	required	A1:C10	Range or array to transpose.

TRIM

TRIM(text)

Removes extra spaces.

Argument	Type	Required	Example	Description
text	text	required	" hi "	String to trim.

UNIQUE

UNIQUE(array, [by_col], [exactly_once])

Returns the distinct rows of a range, in first-seen order.

Argument	Type	Required	Example	Description
array	range	required	A1:A100	Range or array to dedupe.
by_col	logical	optional	FALSE	TRUE to dedupe columns instead of rows.
exactly_once	logical	optional	TRUE	TRUE to return only rows that appear exactly once.

UPPER

UPPER(text)

Converts text to uppercase.

Argument	Type	Required	Example	Description
text	text	required	"hello"	String to uppercase.

VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks up a value in the first column and returns a value from the same row.

Argument	Type	Required	Example	Description
lookup_value	any	required	"apple"	Value to find.
table_array	range	required	A1:C100	Range to search.
col_index_num	value	required	2	Column to return (1-based).
range_lookup	logical	optional	FALSE	FALSE for exact match, TRUE for approximate.

VSTACK

VSTACK(array1, [array2], ...)

Stacks ranges on top of each other into one taller array.

Argument	Type	Required	Example	Description
array1	range	required	A1:C1	First range to stack.
array2	range	repeatable	A2:C2	Additional ranges, placed below.

WRAPCOLS

WRAPCOLS(vector, wrap_count, [pad_with])

Wraps a row/column vector into columns of a fixed height.

Argument	Type	Required	Example	Description
vector	range	required	A1:A12	1-D range to wrap.
wrap_count	value	required	3	Values per column.
pad_with	any	optional	0	Value for the short final column (default #N/A).

WRAPROWS

WRAPROWS(vector, wrap_count, [pad_with])

Wraps a row/column vector into rows of a fixed width.

Argument	Type	Required	Example	Description
vector	range	required	A1:A12	1-D range to wrap.
wrap_count	value	required	3	Values per row.
pad_with	any	optional	0	Value for the short final row (default #N/A).

XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Searches lookup_array, returns the matching element of return_array.

Argument	Type	Required	Example	Description
lookup_value	any	required	"apple"	Value to find.
lookup_array	range	required	A1:A100	Range to search.
return_array	range	required	B1:B100	Range whose value is returned.
if_not_found	any	optional	"missing"	Returned when no match.
match_mode	value	optional	0	0 exact, -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard.
search_mode	value	optional	1	1 first-to-last, -1 last-to-first, 2 binary asc, -2 binary desc.

YEAR

YEAR(serial_number)

Year component of a date.

Argument	Type	Required	Example	Description
serial_number	value	required	A1	Date value.

YIELD

YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Returns the yield on a security that pays periodic interest.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,15)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2016,11,15)	The security’s maturity date.
rate	value	required	0.0575	The security’s annual coupon rate.
pr	value	required	95.04287	The security’s price per $100 face value.
redemption	value	required	100	The redemption value per $100 face value.
frequency	value	required	2	Coupons per year: 1 annual, 2 semi-annual, 4 quarterly.
basis	value	optional	0	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

YIELDDISC

YIELDDISC(settlement, maturity, pr, redemption, [basis])

Returns the annual yield for a discounted security.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,2,16)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2008,3,1)	The security’s maturity date.
pr	value	required	99.795	Price per $100 face value.
redemption	value	required	100	Redemption value per $100 face value.
basis	value	optional	2	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

YIELDMAT

YIELDMAT(settlement, maturity, issue, rate, pr, [basis])

Returns the annual yield of a security that pays interest at maturity.

Argument	Type	Required	Example	Description
settlement	value	required	DATE(2008,3,15)	The security’s settlement (purchase) date.
maturity	value	required	DATE(2008,11,3)	The security’s maturity date.
issue	value	required	DATE(2007,11,8)	The security’s issue date.
rate	value	required	0.0625	The security’s interest rate at issue.
pr	value	required	100.0123	Price per $100 face value.
basis	value	optional	0	Day-count: 0 US 30/360, 1 act/act, 2 act/360, 3 act/365, 4 Euro 30/360.

In-cell languages & AI

A handful of functions take raw source as their argument instead of a formula. They open the cell up to other engines:

• =PY(…): Run sandboxed Python. See Python in cells.
• =SQL(…): Run SQL (Apache DataFusion) over your connected data sources. See SQL in cells.
• =AI(…): Prompt an LLM and write the answer into the cell.
• =AI.CLASSIFY(…): Classify a value into one of a set of labels with an LLM.
• =AI.EXTRACT(…): Pull structured fields out of free text with an LLM.
• =AI.TRANSLATE(…): Translate text between languages with an LLM.

Full catalog

All 345 recognised functions. Bold names have a detailed entry above.

• ABS
• ACOS
• ACOSH
• ACOT
• ACOTH
• AND
• ARABIC
• ASIN
• ASINH
• ATAN
• ATAN2
• ATANH
• AVEDEV
• AVERAGE
• AVERAGEA
• AVERAGEIF
• AVERAGEIFS
• BASE
• BESSELI
• BESSELJ
• BESSELK
• BESSELY
• BETA.DIST
• BETA.INV
• BIN2DEC
• BIN2HEX
• BIN2OCT
• BINOM.DIST
• BINOM.DIST.RANGE
• BINOM.INV
• BITAND
• BITLSHIFT
• BITOR
• BITRSHIFT
• BITXOR
• CEILING
• CEILING.MATH
• CEILING.PRECISE
• CELL
• CHISQ.DIST
• CHISQ.DIST.RT
• CHISQ.INV
• CHISQ.INV.RT
• CHISQ.TEST
• CHOOSE
• COLUMN
• COLUMNS
• COMBIN
• COMBINA
• COMPLEX
• CONCAT
• CONCATENATE
• CONFIDENCE.NORM
• CONFIDENCE.T
• CONVERT
• CORREL
• COS
• COSH
• COT
• COTH
• COUNT
• COUNTA
• COUNTBLANK
• COUNTIF
• COUNTIFS
• COVARIANCE.P
• COVARIANCE.S
• CSC
• CSCH
• CUMIPMT
• CUMPRINC
• DATE
• DATEDIF
• DATEVALUE
• DAVERAGE
• DAY
• DAYS
• DAYS360
• DB
• DCOUNT
• DCOUNTA
• DDB
• DEC2BIN
• DEC2HEX
• DEC2OCT
• DECIMAL
• DEGREES
• DELTA
• DEVSQ
• DGET
• DMAX
• DMIN
• DOLLARDE
• DOLLARFR
• DPRODUCT
• DSTDEV
• DSTDEVP
• DSUM
• DVAR
• DVARP
• EDATE
• EFFECT
• EOMONTH
• ERF
• ERF.PRECISE
• ERFC
• ERFC.PRECISE
• ERROR.TYPE
• EVEN
• EXACT
• EXP
• EXPON.DIST
• F.DIST
• F.DIST.RT
• F.INV
• F.INV.RT
• F.TEST
• FACT
• FACTDOUBLE
• FALSE
• FIND
• FISHER
• FISHERINV
• FLOOR
• FLOOR.MATH
• FLOOR.PRECISE
• FORMULATEXT
• FV
• GAMMA
• GAMMA.DIST
• GAMMA.INV
• GAMMALN
• GAMMALN.PRECISE
• GAUSS
• GCD
• GEOMEAN
• GESTEP
• HARMEAN
• HEX2BIN
• HEX2DEC
• HEX2OCT
• HLOOKUP
• HOUR
• HYPGEOM.DIST
• IF
• IFERROR
• IFNA
• IFS
• IMABS
• IMAGINARY
• IMARGUMENT
• IMCONJUGATE
• IMCOS
• IMCOSH
• IMCOT
• IMCSC
• IMCSCH
• IMDIV
• IMEXP
• IMLN
• IMLOG10
• IMLOG2
• IMPOWER
• IMPRODUCT
• IMREAL
• IMSEC
• IMSECH
• IMSIN
• IMSINH
• IMSQRT
• IMSUB
• IMSUM
• IMTAN
• INDEX
• INDIRECT
• INFO
• INT
• INTERCEPT
• IPMT
• IRR
• ISBLANK
• ISERR
• ISERROR
• ISEVEN
• ISFORMULA
• ISLOGICAL
• ISNA
• ISNONTEXT
• ISNUMBER
• ISO.CEILING
• ISODD
• ISOWEEKNUM
• ISPMT
• ISREF
• ISTEXT
• KURT
• LARGE
• LCM
• LEFT
• LEN
• LN
• LOG
• LOG10
• LOGNORM.DIST
• LOGNORM.INV
• LOOKUP
• LOWER
• MATCH
• MAX
• MAXA
• MAXIFS
• MEDIAN
• MID
• MIN
• MINA
• MINIFS
• MINUTE
• MIRR
• MOD
• MONTH
• MROUND
• N
• NA
• NEGBINOM.DIST
• NETWORKDAYS
• NETWORKDAYS.INTL
• NOMINAL
• NORM.DIST
• NORM.INV
• NORM.S.DIST
• NORM.S.INV
• NOT
• NOW
• NPER
• NPV
• OCT2BIN
• OCT2DEC
• OCT2HEX
• ODD
• OFFSET
• OR
• PDURATION
• PEARSON
• PHI
• PI
• PMT
• POISSON.DIST
• POWER
• PPMT
• PRODUCT
• PV
• QUOTIENT
• RADIANS
• RAND
• RANDBETWEEN
• RANK.AVG
• RANK.EQ
• RATE
• REPT
• RIGHT
• ROMAN
• ROUND
• ROUNDDOWN
• ROUNDUP
• ROW
• ROWS
• RRI
• RSQ
• SEARCH
• SEC
• SECH
• SECOND
• SHEET
• SHEETS
• SIGN
• SIN
• SINH
• SKEW
• SKEW.P
• SLN
• SLOPE
• SMALL
• SQRT
• SQRTPI
• STANDARDIZE
• STDEV.P
• STDEV.S
• STDEVA
• STDEVPA
• STEYX
• SUBSTITUTE
• SUBTOTAL
• SUM
• SUMIF
• SUMIFS
• SUMSQ
• SUMX2MY2
• SUMX2PY2
• SUMXMY2
• SWITCH
• SYD
• T
• T.DIST
• T.DIST.2T
• T.DIST.RT
• T.INV
• T.INV.2T
• T.TEST
• TAN
• TANH
• TBILLEQ
• TBILLPRICE
• TBILLYIELD
• TEXT
• TEXTAFTER
• TEXTBEFORE
• TEXTJOIN
• TIME
• TIMEVALUE
• TODAY
• TRIM
• TRUE
• TRUNC
• TYPE
• UNICODE
• UPPER
• VALUE
• VALUETOTEXT
• VAR.P
• VAR.S
• VARA
• VARPA
• VLOOKUP
• WEEKDAY
• WEEKNUM
• WEIBULL.DIST
• WORKDAY
• WORKDAY.INTL
• XIRR
• XLOOKUP
• XNPV
• XOR
• YEAR
• YEARFRAC
• Z.TEST