Function reference — Nixt Sheets docs

Nixt Sheets calculates 478 worksheet functions. This page lists every one, grouped by category, with the syntax the app shows and what the function is for. For how to write formulas and insert functions, see Writing formulas.

Reading the syntax

Arguments in square brackets, such as [basis], are optional.
… means you can give more arguments of the same kind.
Separate arguments with commas.
Function names can be typed in any capitals.

Category	Functions	Button on the Formulas tab
Financial	55	Financial
Logical	19	Logical
Text	46	Text
Date and time	25	Date
Lookup and reference	36	Lookup
Maths and trigonometry	81	Maths
Statistical	129	Stats
Engineering	54	None — use the function browser
Information	21	None — use the function browser
Database	12	None — use the function browser

Financial

The app calls this category Financial. Open it with Formulas › Function library › Financial.

Function	Syntax	What it does
ACCRINT	`ACCRINT(issue, first, settlement, rate, par, frequency, [basis])`	Accrued interest for a security that pays periodically.
ACCRINTM	`ACCRINTM(issue, settlement, rate, par, [basis])`	Accrued interest for a security that pays at maturity.
AMORDEGRC	`AMORDEGRC(cost, purchased, first, salvage, period, rate, [basis])`	French degressive depreciation, with the coefficient the asset’s life earns it.
AMORLINC	`AMORLINC(cost, purchased, first, salvage, period, rate, [basis])`	French linear depreciation, prorated in the first period.
COUPDAYBS	`COUPDAYBS(settlement, maturity, frequency, [basis])`	Days from the last coupon to settlement.
COUPDAYS	`COUPDAYS(settlement, maturity, frequency, [basis])`	Days in the coupon period containing settlement.
COUPDAYSNC	`COUPDAYSNC(settlement, maturity, frequency, [basis])`	Days from settlement to the next coupon.
COUPNCD	`COUPNCD(settlement, maturity, frequency, [basis])`	The coupon date after settlement.
COUPNUM	`COUPNUM(settlement, maturity, frequency, [basis])`	How many coupons are left before maturity.
COUPPCD	`COUPPCD(settlement, maturity, frequency, [basis])`	The coupon date on or before settlement.
CUMIPMT	`CUMIPMT(rate, nper, pv, start, end, type)`	The total interest paid between two periods.
CUMPRINC	`CUMPRINC(rate, nper, pv, start, end, type)`	The total principal repaid between two periods.
DB	`DB(cost, salvage, life, period, [month])`	Fixed-declining-balance depreciation for one period.
DDB	`DDB(cost, salvage, life, period, [factor])`	Declining-balance depreciation for one period.
DISC	`DISC(settlement, maturity, price, redemption, [basis])`	The discount rate of a security.
DOLLARDE	`DOLLARDE(fractional, fraction)`	Converts a price written as a fraction into a decimal.
DOLLARFR	`DOLLARFR(decimal, fraction)`	Converts a decimal price into one written as a fraction.
DURATION	`DURATION(settlement, maturity, coupon, yield, frequency, [basis])`	Macaulay duration — the weighted average time to a bond’s cash flows.
EFFECT	`EFFECT(nominal_rate, npery)`	The real annual rate once compounding is counted.
FV	`FV(rate, nper, pmt, [pv], [type])`	What an investment will be worth.
FVSCHEDULE	`FVSCHEDULE(principal, schedule)`	The future value of a principal after a series of different rates.
INTRATE	`INTRATE(settlement, maturity, investment, redemption, [basis])`	The interest rate of a fully invested security.
IPMT	`IPMT(rate, per, nper, pv, [fv], [type])`	The interest part of one loan payment.
IRR	`IRR(values, [guess])`	The rate at which a set of cash flows breaks even.
ISPMT	`ISPMT(rate, period, periods, presentValue)`	The interest paid in one period of a straight-line loan.
MDURATION	`MDURATION(settlement, maturity, coupon, yield, frequency, [basis])`	Modified duration — the price sensitivity to a change in yield.
MIRR	`MIRR(values, finance_rate, reinvest_rate)`	A rate of return that separates borrowing cost from reinvestment.
NOMINAL	`NOMINAL(effect_rate, npery)`	The quoted rate behind a real annual rate.
NPER	`NPER(rate, pmt, pv, [fv], [type])`	How many payments a loan takes.
NPV	`NPV(rate, value1, ...)`	The present value of a series of future cash flows.
ODDFPRICE	`ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yield, redemption, frequency, [basis])`	The price of a bond whose first coupon period is not a whole one.
ODDFYIELD	`ODDFYIELD(settlement, maturity, issue, first_coupon, rate, price, redemption, frequency, [basis])`	The yield of a bond whose first coupon period is not a whole one.
ODDLPRICE	`ODDLPRICE(settlement, maturity, last_interest, rate, yield, redemption, frequency, [basis])`	The price of a bond whose last coupon period is not a whole one.
ODDLYIELD	`ODDLYIELD(settlement, maturity, last_interest, rate, price, redemption, frequency, [basis])`	The yield of a bond whose last coupon period is not a whole one.
PDURATION	`PDURATION(rate, presentValue, futureValue)`	How many periods an investment needs to reach a value.
PMT	`PMT(rate, nper, pv, [fv], [type])`	The payment for a loan with constant payments and a constant rate.
PPMT	`PPMT(rate, per, nper, pv, [fv], [type])`	The principal part of one loan payment.
PRICE	`PRICE(settlement, maturity, rate, yield, redemption, frequency, [basis])`	The price per £100 of a bond that pays periodic interest.
PRICEDISC	`PRICEDISC(settlement, maturity, discount, redemption, [basis])`	The price per £100 of a discounted security.
PRICEMAT	`PRICEMAT(settlement, maturity, issue, rate, yield, [basis])`	The price per £100 of a security that pays interest at maturity.
PV	`PV(rate, nper, pmt, [fv], [type])`	What a future stream of payments is worth today.
RATE	`RATE(nper, pmt, pv, [fv], [type], [guess])`	The interest rate implied by a set of payments.
RECEIVED	`RECEIVED(settlement, maturity, investment, discount, [basis])`	The amount received at maturity for a fully invested security.
RRI	`RRI(periods, presentValue, futureValue)`	The rate an investment must earn to reach a value.
SLN	`SLN(cost, salvage, life)`	Straight-line depreciation for one period.
SYD	`SYD(cost, salvage, life, period)`	Sum-of-years depreciation for one period.
TBILLEQ	`TBILLEQ(settlement, maturity, discount)`	The bond-equivalent yield of a Treasury bill.
TBILLPRICE	`TBILLPRICE(settlement, maturity, discount)`	The price per £100 of a Treasury bill.
TBILLYIELD	`TBILLYIELD(settlement, maturity, price)`	The yield of a Treasury bill.
VDB	`VDB(cost, salvage, life, start, end, [factor], [noSwitch])`	Declining-balance depreciation over a partial range of periods.
XIRR	`XIRR(values, dates, [guess])`	The rate of return for cash flows on specific dates.
XNPV	`XNPV(rate, values, dates)`	The present value of cash flows on specific dates.
YIELD	`YIELD(settlement, maturity, rate, price, redemption, frequency, [basis])`	The yield of a bond that pays periodic interest.
YIELDDISC	`YIELDDISC(settlement, maturity, price, redemption, [basis])`	The annual yield of a discounted security.
YIELDMAT	`YIELDMAT(settlement, maturity, issue, rate, price, [basis])`	The yield of a security that pays interest at maturity.

Logical

The app calls this category Logical. Open it with Formulas › Function library › Logical.

Function	Syntax	What it does
AND	`AND(value1, ...)`	True when every one of them is true.
BYCOL (typed only)	`BYCOL(array, lambda)`	Applies a LAMBDA to each column of an array and returns one result per column.
BYROW (typed only)	`BYROW(array, lambda)`	Applies a LAMBDA to each row of an array and returns one result per row.
FALSE	`FALSE()`	The value FALSE.
IF (typed only)	`IF(logical_test, [value_if_true], [value_if_false])`	One value when a test is true and another when it is false. Only the branch it takes is calculated; with no values given it returns TRUE or FALSE.
IFERROR (typed only)	`IFERROR(value, [value_if_error])`	The value, or a replacement when it is any error. With no replacement given, an error becomes blank.
IFNA (typed only)	`IFNA(value, [value_if_na])`	The value, or a replacement when it is #N/A.
IFS (typed only)	`IFS(test1, value1, [test2, value2], …)`	The value paired with the first test that is true, or #N/A when none is.
LAMBDA (typed only)	`LAMBDA([parameter1, …], calculation)`	A function of your own. Saved as a defined name, it can be called like a built-in function.
LET (typed only)	`LET(name1, value1, …, calculation)`	Names intermediate results inside a formula, then calculates with them. A later value can use an earlier name.
MAKEARRAY (typed only)	`MAKEARRAY(rows, columns, lambda)`	Builds an array by calling a LAMBDA with each row and column number. Up to 100,000 cells.
MAP (typed only)	`MAP(array1, [array2, …], lambda)`	Applies a LAMBDA to each value, pairing up the values of several arrays, and returns an array of the results.
NOT	`NOT(value)`	Flips true to false.
OR	`OR(value1, ...)`	True when any one of them is true.
REDUCE (typed only)	`REDUCE(initial_value, array, lambda)`	Runs a LAMBDA over each value, carrying an accumulated result from one to the next, and returns the final result.
SCAN (typed only)	`SCAN(initial_value, array, lambda)`	Like REDUCE, but returns every intermediate result — a running total, for example.
SWITCH (typed only)	`SWITCH(expression, value1, result1, …, [default])`	Compares an expression with a list of values and returns the result for the first match; the default, or #N/A, when nothing matches.
TRUE	`TRUE()`	The value TRUE.
XOR	`XOR(value1, ...)`	True when an odd number of them are true.

Text

The app calls this category Text. Open it with Formulas › Function library › Text.

Function	Syntax	What it does
ARRAYTOTEXT	`ARRAYTOTEXT(array, [format])`	An array written out as text.
ASC	`ASC(text)`	Full-width characters turned into half-width ones.
BAHTTEXT	`BAHTTEXT(number)`	A number written out in Thai words, followed by the word baht.
CHAR	`CHAR(number)`	The character with a given code.
CLEAN	`CLEAN(text)`	Removes characters that do not print.
CODE	`CODE(text)`	The code of the first character.
CONCAT	`CONCAT(text1, …)`	Joins text together, ranges included.
CONCATENATE	`CONCATENATE(text1, …)`	Joins text together.
DBCS	`DBCS(text)`	Half-width characters turned into full-width ones.
DOLLAR	`DOLLAR(number, [decimals])`	Formats a number as currency text.
EXACT	`EXACT(text1, text2)`	Whether two pieces of text are identical, case included.
FIND	`FIND(find_text, within_text, [start])`	Where one piece of text appears inside another. Case matters.
FINDB	`FINDB(find_text, within_text, [start])`	The same as FIND. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
FIXED	`FIXED(number, [decimals], [no_commas])`	Formats a number with a fixed number of decimals.
LEFT	`LEFT(text, [count])`	The first few characters.
LEFTB	`LEFTB(text, [count])`	The same as LEFT. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
LEN	`LEN(text)`	How many characters are in the text.
LENB	`LENB(text)`	The same as LEN. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
LOWER	`LOWER(text)`	The text in lower case.
MID	`MID(text, start, count)`	The characters from a position onwards.
MIDB	`MIDB(text, start, length)`	The same as MID. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
NUMBERVALUE	`NUMBERVALUE(text, [decimal_separator], [group_separator])`	Turns text into a number using the separators you name.
PROPER	`PROPER(text)`	The text with each word capitalised.
REGEXEXTRACT	`REGEXEXTRACT(text, pattern, [returnMode], [caseSensitive])`	The part of the text a pattern matches.
REGEXREPLACE	`REGEXREPLACE(text, pattern, replacement, [occurrence], [caseSensitive])`	Replaces what a pattern matches.
REGEXTEST	`REGEXTEST(text, pattern, [caseSensitive])`	Whether a pattern matches anywhere in the text.
REPLACE	`REPLACE(text, start, count, new)`	Replaces characters at a position.
REPLACEB	`REPLACEB(old, start, length, new)`	The same as REPLACE. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
REPT	`REPT(text, count)`	Repeats text a number of times.
RIGHT	`RIGHT(text, [count])`	The last few characters.
RIGHTB	`RIGHTB(text, [count])`	The same as RIGHT. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
SEARCH	`SEARCH(find_text, within_text, [start])`	Where one piece of text appears inside another, ignoring case and allowing wildcards.
SEARCHB	`SEARCHB(find_text, within_text, [start])`	The same as SEARCH. It counts bytes rather than characters, which differs only in Japanese, Chinese and Korean.
SUBSTITUTE	`SUBSTITUTE(text, old, new, [instance])`	Replaces one piece of text with another.
T	`T(value)`	The value if it is text, and nothing if it is not.
TEXT	`TEXT(value, format)`	Formats a number as text using a format code.
TEXTAFTER	`TEXTAFTER(text, delimiter, [instance])`	Everything after a separator.
TEXTBEFORE	`TEXTBEFORE(text, delimiter, [instance])`	Everything before a separator.
TEXTJOIN	`TEXTJOIN(delimiter, ignore_empty, text1, …)`	Joins text with a separator between each piece.
TEXTSPLIT	`TEXTSPLIT(text, columnDelimiter, [rowDelimiter], [ignoreEmpty])`	Splits text across columns and rows.
TRIM	`TRIM(text)`	Removes leading, trailing and repeated spaces.
UNICHAR	`UNICHAR(number)`	The character with a given Unicode code point.
UNICODE	`UNICODE(text)`	The Unicode code point of the first character.
UPPER	`UPPER(text)`	The text in capitals.
VALUE	`VALUE(text)`	Turns text that looks like a number into a number.
VALUETOTEXT	`VALUETOTEXT(value, [format])`	A value written out as text.

Date and time

The app calls this category Date & time. Open it with Formulas › Function library › Date.

Function	Syntax	What it does
DATE	`DATE(year, month, day)`	Builds a date from its parts.
DATEDIF	`DATEDIF(start, end, unit)`	The gap between two dates in years, months or days.
DATEVALUE	`DATEVALUE(text)`	Turns a written date into a date value.
DAY	`DAY(date)`	The day of a date.
DAYS	`DAYS(end, start)`	How many days between two dates.
DAYS360	`DAYS360(start, end, [european])`	Days between two dates on a 360-day year.
EDATE	`EDATE(start, months)`	The same day a number of months later.
EOMONTH	`EOMONTH(start, months)`	The last day of the month a number of months away.
HOUR	`HOUR(time)`	The hour of a time.
ISOWEEKNUM	`ISOWEEKNUM(date)`	The ISO week number — the one used for reporting.
MINUTE	`MINUTE(time)`	The minute of a time.
MONTH	`MONTH(date)`	The month of a date.
NETWORKDAYS	`NETWORKDAYS(start, end, [holidays])`	Working days between two dates.
NETWORKDAYS.INTL	`NETWORKDAYS.INTL(start, end, [weekend], [holidays])`	Working days between two dates, with your own weekend.
NOW	`NOW()`	The current date and time.
SECOND	`SECOND(time)`	The second of a time.
TIME	`TIME(hour, minute, second)`	Builds a time from its parts.
TIMEVALUE	`TIMEVALUE(text)`	Turns a written time into a time value.
TODAY	`TODAY()`	Today’s date.
WEEKDAY	`WEEKDAY(date, [type])`	Which day of the week a date is.
WEEKNUM	`WEEKNUM(date, [type])`	Which week of the year a date falls in.
WORKDAY	`WORKDAY(start, days, [holidays])`	The date a number of working days away.
WORKDAY.INTL	`WORKDAY.INTL(start, days, [weekend], [holidays])`	The date a number of working days away, with your own weekend.
YEAR	`YEAR(date)`	The year of a date.
YEARFRAC	`YEARFRAC(start, end, [basis])`	The fraction of a year between two dates.

Lookup and reference

The app calls this category Lookup & reference. Open it with Formulas › Function library › Lookup.

Function	Syntax	What it does
ADDRESS	`ADDRESS(row, column, [abs], [a1], [sheet])`	Builds a reference as text from a row and column number.
AREAS	`AREAS(reference)`	How many areas a reference covers.
CHOOSE (typed only)	`CHOOSE(index, value1, [value2], …)`	The value at a position in the list of values. Only the chosen value is calculated.
CHOOSECOLS	`CHOOSECOLS(array, col1, …)`	Picks columns out of an array, in the order given.
CHOOSEROWS	`CHOOSEROWS(array, row1, …)`	Picks rows out of an array, in the order given.
COLUMN	`COLUMN([reference])`	The column number of a cell, or of the formula itself.
COLUMNS	`COLUMNS(array)`	How many columns a range has.
DROP	`DROP(array, rows, [columns])`	Drops rows from the start or, with a negative count, the end.
EXPAND	`EXPAND(array, rows, [columns], [pad])`	Grows an array to a size, padding what it does not fill.
FILTER	`FILTER(array, include, [if_empty])`	Only the rows where a test is true.
FORMULATEXT	`FORMULATEXT(reference)`	The formula in a cell, as text.
GETPIVOTDATA	`GETPIVOTDATA(field, pivot_table, [name, item]…)`	One number out of a pivot table, found by what it means rather than where it sits.
HLOOKUP	`HLOOKUP(value, table, row, [approximate])`	Finds a value along the first row of a table and returns something from the same column.
HSTACK	`HSTACK(array1, …)`	Stacks arrays side by side.
HYPERLINK	`HYPERLINK(url, [label])`	A clickable link.
INDEX	`INDEX(array, row, [column])`	The value at a position in a range.
INDIRECT	`INDIRECT(text, [a1])`	Turns text into a real reference.
LOOKUP	`LOOKUP(value, lookup_vector, [result_vector])`	The older, simpler lookup: finds a value in a sorted list.
MATCH	`MATCH(value, array, [type])`	The position of a value within a list.
OFFSET	`OFFSET(reference, rows, cols, [height], [width])`	A range shifted from another one, which is how to build a reference that moves.
ROW	`ROW([reference])`	The row number of a cell, or of the formula itself.
ROWS	`ROWS(array)`	How many rows a range has.
SEQUENCE	`SEQUENCE(rows, [columns], [start], [step])`	A block of numbers counting up.
SORT	`SORT(array, [sort_index], [sort_order], [by_column])`	A range put in order.
SORTBY	`SORTBY(array, by1, [order1], …)`	Sorts an array by the values of another.
TAKE	`TAKE(array, rows, [columns])`	Takes rows from the start or, with a negative count, the end.
TOCOL	`TOCOL(array, [ignore])`	Flattens an array into a single column.
TOROW	`TOROW(array, [ignore])`	Flattens an array into a single row.
TRANSPOSE	`TRANSPOSE(array)`	Flips a range so rows become columns.
UNIQUE	`UNIQUE(array, [by_column], [exactly_once])`	The distinct values from a range.
VLOOKUP	`VLOOKUP(value, table, column, [approximate])`	Finds a value down the first column of a table and returns something from the same row.
VSTACK	`VSTACK(array1, …)`	Stacks arrays on top of each other.
WRAPCOLS	`WRAPCOLS(vector, count, [pad])`	Wraps a vector into columns of a fixed height.
WRAPROWS	`WRAPROWS(vector, count, [pad])`	Wraps a vector into rows of a fixed width.
XLOOKUP	`XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`	Finds a value in one range and returns the matching item from another, with a proper answer when there is no match.
XMATCH	`XMATCH(value, array, [match_mode], [search_mode])`	The position of a value, with exact, next-larger or next-smaller matching.

Maths and trigonometry

The app calls this category Maths & trig. Open it with Formulas › Function library › Maths.

Function	Syntax	What it does
ABS	`ABS(number)`	The size of a number, ignoring its sign.
ACOS	`ACOS(number)`	The inverse cosine, in radians.
ACOSH	`ACOSH(number)`	The inverse hyperbolic cosine.
ACOT	`ACOT(number)`	The arccotangent, from 0 to π.
ACOTH	`ACOTH(number)`	The inverse hyperbolic cotangent.
AGGREGATE	`AGGREGATE(function, options, array, [k])`	One of nineteen aggregates, optionally ignoring errors and hidden rows.
ARABIC	`ARABIC(text)`	The number a Roman numeral stands for.
ASIN	`ASIN(number)`	The inverse sine, in radians.
ASINH	`ASINH(number)`	The inverse hyperbolic sine.
ATAN	`ATAN(number)`	The inverse tangent, in radians.
ATAN2	`ATAN2(x, y)`	The angle from the x-axis to a point, in radians.
ATANH	`ATANH(number)`	The inverse hyperbolic tangent.
BASE	`BASE(number, radix, [minLength])`	Writes a number in another base.
CEILING	`CEILING(number, significance)`	Rounds up to the nearest multiple of another number.
CEILING.MATH	`CEILING.MATH(number, [significance], [mode])`	Rounds up to a multiple, with a choice of how negatives behave.
CEILING.PRECISE	`CEILING.PRECISE(number, [significance])`	Rounds up to a multiple, always away from zero.
COMBIN	`COMBIN(n, k)`	How many ways k things can be chosen from n, order not mattering.
COMBINA	`COMBINA(number, chosen)`	Combinations with repetition allowed.
COS	`COS(angle)`	The cosine of an angle in radians.
COSH	`COSH(number)`	The hyperbolic cosine.
COT	`COT(number)`	The cotangent of an angle.
COTH	`COTH(number)`	The hyperbolic cotangent.
CSC	`CSC(number)`	The cosecant of an angle.
CSCH	`CSCH(number)`	The hyperbolic cosecant.
DECIMAL	`DECIMAL(text, radix)`	Reads a number written in another base.
DEGREES	`DEGREES(angle)`	Converts radians to degrees.
EVEN	`EVEN(number)`	Rounds away from zero to the next even whole number.
EXP	`EXP(number)`	e raised to a power.
FACT	`FACT(number)`	The factorial — 5 gives 120.
FACTDOUBLE	`FACTDOUBLE(number)`	The double factorial — every other number multiplied together.
FLOOR	`FLOOR(number, significance)`	Rounds down to the nearest multiple of another number.
FLOOR.MATH	`FLOOR.MATH(number, [significance], [mode])`	Rounds down to a multiple, with a choice of how negatives behave.
FLOOR.PRECISE	`FLOOR.PRECISE(number, [significance])`	Rounds down to a multiple, always towards zero.
GCD	`GCD(number1, …)`	The largest whole number that divides all of them.
INT	`INT(number)`	Rounds down to the nearest whole number — towards minus infinity, not towards zero.
ISO.CEILING	`ISO.CEILING(number, [significance])`	Rounds up to a multiple, always away from zero.
LCM	`LCM(number1, …)`	The smallest whole number all of them divide into.
LN	`LN(number)`	The natural logarithm.
LOG	`LOG(number, [base])`	The logarithm of a number in any base, base 10 by default.
LOG10	`LOG10(number)`	The base-10 logarithm.
MDETERM	`MDETERM(array)`	The determinant of a square matrix.
MINVERSE	`MINVERSE(array)`	The inverse of a square matrix.
MMULT	`MMULT(array1, array2)`	The matrix product of two arrays.
MOD	`MOD(number, divisor)`	The remainder after division. Takes the sign of the divisor, as in Excel.
MROUND	`MROUND(number, multiple)`	Rounds to the nearest multiple of another number.
MULTINOMIAL	`MULTINOMIAL(number1, …)`	The multinomial coefficient of a set of numbers.
MUNIT	`MUNIT(size)`	The identity matrix of a given size.
ODD	`ODD(number)`	Rounds away from zero to the next odd whole number.
PERMUT	`PERMUT(n, k)`	How many ways k things can be chosen from n when order matters.
PI	`PI()`	The constant pi.
POWER	`POWER(number, power)`	A number raised to a power.
PRODUCT	`PRODUCT(number1, …)`	Multiplies everything together.
QUOTIENT	`QUOTIENT(numerator, denominator)`	The whole-number part of a division.
RADIANS	`RADIANS(angle)`	Converts degrees to radians.
RAND	`RAND()`	A random number from 0 up to but not including 1.
RANDARRAY	`RANDARRAY([rows], [columns], [min], [max], [whole])`	A block of random numbers.
RANDBETWEEN	`RANDBETWEEN(bottom, top)`	A random whole number between two bounds, inclusive.
ROMAN	`ROMAN(number, [form])`	A number written in Roman numerals.
ROUND	`ROUND(number, digits)`	Rounds to a number of decimal places, half away from zero.
ROUNDDOWN	`ROUNDDOWN(number, digits)`	Rounds towards zero, always.
ROUNDUP	`ROUNDUP(number, digits)`	Rounds away from zero, always.
SEC	`SEC(number)`	The secant of an angle.
SECH	`SECH(number)`	The hyperbolic secant.
SERIESSUM	`SERIESSUM(x, n, m, coefficients)`	Sums a power series.
SIGN	`SIGN(number)`	Gives 1, 0 or −1 depending on whether a number is positive, zero or negative.
SIN	`SIN(angle)`	The sine of an angle in radians.
SINH	`SINH(number)`	The hyperbolic sine.
SQRT	`SQRT(number)`	The square root.
SQRTPI	`SQRTPI(number)`	The square root of a number multiplied by pi.
SUBTOTAL	`SUBTOTAL(function_num, ref1, …)`	Runs one of eleven aggregate functions, chosen by number, over a range.
SUM	`SUM(number1, …)`	Adds everything up.
SUMIF	`SUMIF(range, criteria, [sum_range])`	Adds up the cells that match a condition.
SUMIFS	`SUMIFS(sum_range, criteria_range1, criteria1, …)`	Adds up the cells that match every one of several conditions.
SUMPRODUCT	`SUMPRODUCT(array1, …)`	Multiplies the arrays cell by cell, then adds the results.
SUMSQ	`SUMSQ(number1, …)`	Adds up the squares.
SUMX2MY2	`SUMX2MY2(arrayX, arrayY)`	The sum of the differences of squares.
SUMX2PY2	`SUMX2PY2(arrayX, arrayY)`	The sum of the sums of squares.
SUMXMY2	`SUMXMY2(arrayX, arrayY)`	The sum of the squares of the differences.
TAN	`TAN(angle)`	The tangent of an angle in radians.
TANH	`TANH(number)`	The hyperbolic tangent.
TRUNC	`TRUNC(number, [digits])`	Cuts off the decimals without rounding.

Statistical

The app calls this category Statistical. Open it with Formulas › Function library › Stats.

Function	Syntax	What it does
AVEDEV	`AVEDEV(number1, …)`	The average distance from the mean.
AVERAGE	`AVERAGE(number1, …)`	The arithmetic mean of the numbers, ignoring text and blanks.
AVERAGEA	`AVERAGEA(value1, …)`	The mean, counting text as zero and TRUE as one.
AVERAGEIF	`AVERAGEIF(range, criteria, [average_range])`	The mean of the cells that match a condition.
AVERAGEIFS	`AVERAGEIFS(average_range, criteria_range1, criteria1, …)`	The mean of the cells matching every one of several conditions.
BETA.DIST	`BETA.DIST(x, alpha, beta, cumulative, [a], [b])`	The beta distribution, optionally over a range other than 0 to 1.
BETA.INV	`BETA.INV(probability, alpha, beta, [a], [b])`	The inverse of the beta distribution.
BINOM.DIST	`BINOM.DIST(successes, trials, probability, cumulative)`	The binomial distribution.
BINOM.DIST.RANGE	`BINOM.DIST.RANGE(trials, probability, from, [to])`	The chance of a number of successes in a range.
BINOM.INV	`BINOM.INV(trials, probability, alpha)`	The smallest number of successes whose cumulative chance reaches alpha.
CHISQ.DIST	`CHISQ.DIST(x, degrees, cumulative)`	The left tail of the chi-square distribution.
CHISQ.DIST.RT	`CHISQ.DIST.RT(x, degrees)`	The right tail of the chi-square distribution.
CHISQ.INV	`CHISQ.INV(probability, degrees)`	The inverse of the chi-square left tail.
CHISQ.INV.RT	`CHISQ.INV.RT(probability, degrees)`	The inverse of the chi-square right tail.
CONFIDENCE	`CONFIDENCE(alpha, sd, size)`	The half-width of a confidence interval for a mean.
CONFIDENCE.NORM	`CONFIDENCE.NORM(alpha, sd, size)`	The half-width of a normal confidence interval.
CONFIDENCE.T	`CONFIDENCE.T(alpha, sd, size)`	The half-width of a Student’s t confidence interval.
CORREL	`CORREL(array1, array2)`	How closely two sets of numbers move together, from −1 to 1.
COUNT	`COUNT(value1, …)`	How many of the cells hold numbers.
COUNTA	`COUNTA(value1, …)`	How many of the cells are not empty.
COUNTBLANK	`COUNTBLANK(range)`	How many of the cells are empty.
COUNTIF	`COUNTIF(range, criteria)`	How many cells match a condition.
COUNTIFS	`COUNTIFS(criteria_range1, criteria1, …)`	How many cells match every one of several conditions.
COVAR	`COVAR(array1, array2)`	How two sets of numbers vary together.
COVARIANCE.P	`COVARIANCE.P(array1, array2)`	Population covariance.
COVARIANCE.S	`COVARIANCE.S(array1, array2)`	Sample covariance.
DEVSQ	`DEVSQ(number1, …)`	The sum of squared deviations from the mean.
EXPON.DIST	`EXPON.DIST(x, lambda, cumulative)`	The exponential distribution.
F.DIST	`F.DIST(x, degrees1, degrees2, cumulative)`	The left tail of the F distribution.
F.DIST.RT	`F.DIST.RT(x, degrees1, degrees2)`	The right tail of the F distribution.
F.INV	`F.INV(probability, degrees1, degrees2)`	The inverse of the F left tail.
F.INV.RT	`F.INV.RT(probability, degrees1, degrees2)`	The inverse of the F right tail.
FISHER	`FISHER(x)`	Fisher’s transformation.
FISHERINV	`FISHERINV(y)`	The inverse of Fisher’s transformation.
FORECAST	`FORECAST(x, known_y, known_x)`	Predicts a value from the best-fit line through known points.
FORECAST.ETS	`FORECAST.ETS(target, values, timeline, [seasonality], [completion], [aggregation])`	Predicts a future value by exponential triple smoothing, fitting the level, trend and season to the history given.
FORECAST.ETS.CONFINT	`FORECAST.ETS.CONFINT(target, values, timeline, [confidence], [seasonality], [completion], [aggregation])`	Half the width of the prediction interval around FORECAST.ETS — the value to add and subtract. It widens with distance, because the uncertainty does.
FORECAST.ETS.SEASONALITY	`FORECAST.ETS.SEASONALITY(values, timeline, [completion], [aggregation])`	The length of the repeating pattern found in the history, or zero when there is none.
FORECAST.ETS.STAT	`FORECAST.ETS.STAT(values, timeline, statistic, [seasonality], [completion], [aggregation])`	One of the fitted model’s numbers: 1-3 the smoothing constants alpha, beta and gamma, 4 MASE, 5 SMAPE, 6 MAE, 7 RMSE. MASE under one means the model beats assuming nothing changes.
FREQUENCY	`FREQUENCY(data, bins)`	How many values fall in each bin.
GAMMA	`GAMMA(x)`	The gamma function.
GAMMA.DIST	`GAMMA.DIST(x, alpha, beta, cumulative)`	The gamma distribution.
GAMMA.INV	`GAMMA.INV(probability, alpha, beta)`	The inverse of the gamma distribution.
GAMMALN	`GAMMALN(x)`	The natural log of the gamma function.
GAMMALN.PRECISE	`GAMMALN.PRECISE(x)`	The natural log of the gamma function.
GAUSS	`GAUSS(z)`	The chance a standard normal is between zero and z.
GEOMEAN	`GEOMEAN(number1, …)`	The geometric mean — the right average for growth rates.
GROWTH	`GROWTH(knownY, [knownX], [newX])`	Values along the exponential curve through the known points.
HARMEAN	`HARMEAN(number1, …)`	The harmonic mean — the right average for rates.
HYPGEOM.DIST	`HYPGEOM.DIST(successes, draw, populationSuccesses, population, cumulative)`	The hypergeometric distribution.
INTERCEPT	`INTERCEPT(known_y, known_x)`	Where the best-fit line crosses the y-axis.
KURT	`KURT(number1, …)`	How heavy the tails of the distribution are.
LARGE	`LARGE(array, k)`	The kth largest value.
LINEST	`LINEST(knownY, [knownX])`	The slope and intercept of the least-squares line.
LOGEST	`LOGEST(knownY, [knownX])`	The base and coefficient of the least-squares exponential curve.
LOGNORM.DIST	`LOGNORM.DIST(x, mean, sd, cumulative)`	The lognormal distribution.
LOGNORM.INV	`LOGNORM.INV(probability, mean, sd)`	The inverse of the lognormal distribution.
MAX	`MAX(number1, …)`	The largest number.
MAXA	`MAXA(value1, …)`	The largest value, counting text as zero.
MAXIFS	`MAXIFS(max_range, criteria_range1, criteria1, …)`	The largest value among the cells matching several conditions.
MEDIAN	`MEDIAN(number1, …)`	The middle value once they are sorted.
MIN	`MIN(number1, …)`	The smallest number.
MINA	`MINA(value1, …)`	The smallest value, counting text as zero.
MINIFS	`MINIFS(min_range, criteria_range1, criteria1, …)`	The smallest value among the cells matching several conditions.
MODE	`MODE(number1, …)`	The value that appears most often.
MODE.SNGL	`MODE.SNGL(number1, …)`	The value that appears most often.
NEGBINOM.DIST	`NEGBINOM.DIST(failures, successes, probability, cumulative)`	The negative binomial distribution.
NORM.DIST	`NORM.DIST(x, mean, sd, cumulative)`	The normal distribution.
NORM.INV	`NORM.INV(probability, mean, sd)`	The inverse of the normal distribution.
NORM.S.DIST	`NORM.S.DIST(z, cumulative)`	The standard normal distribution at a point.
NORM.S.INV	`NORM.S.INV(probability)`	The z-score with a given probability below it.
NORMSDIST	`NORMSDIST(z)`	The standard normal cumulative distribution.
NORMSINV	`NORMSINV(probability)`	The z-score with a given probability below it.
PEARSON	`PEARSON(array1, array2)`	The Pearson correlation coefficient.
PERCENTILE	`PERCENTILE(array, k)`	The value below which a given fraction of the data falls.
PERCENTILE.INC	`PERCENTILE.INC(array, k)`	The value below which a given fraction of the data falls.
PERCENTRANK	`PERCENTRANK(array, x, [digits])`	The fraction of the data at or below a value.
PERMUTATIONA	`PERMUTATIONA(number, chosen)`	Permutations with repetition allowed.
PHI	`PHI(x)`	The standard normal density at x.
POISSON.DIST	`POISSON.DIST(x, mean, cumulative)`	The Poisson distribution.
PROB	`PROB(range, probabilities, lower, [upper])`	The chance that a value falls in a range.
QUARTILE	`QUARTILE(array, quart)`	The value at a quarter, half or three-quarters of the data.
RANK	`RANK(number, ref, [order])`	Where a number places within a list.
RANK.EQ	`RANK.EQ(number, ref, [order])`	Where a number places within a list, ties sharing the higher rank.
RSQ	`RSQ(known_y, known_x)`	How much of one series is explained by the other.
SKEW	`SKEW(number1, …)`	How lopsided the distribution is.
SLOPE	`SLOPE(known_y, known_x)`	The gradient of the best-fit line.
SMALL	`SMALL(array, k)`	The kth smallest value.
STANDARDIZE	`STANDARDIZE(x, mean, sd)`	A value expressed in standard deviations from the mean.
STDEV	`STDEV(number1, …)`	The spread of a sample around its mean.
STDEV.P	`STDEV.P(number1, …)`	The spread of a whole population around its mean.
STDEV.S	`STDEV.S(number1, …)`	The spread of a sample around its mean.
STDEVP	`STDEVP(number1, …)`	The spread of a whole population around its mean.
STEYX	`STEYX(knownY, knownX)`	The standard error of the predicted y values.
T.DIST	`T.DIST(x, degrees, cumulative)`	The left tail of Student’s t distribution.
T.DIST.2T	`T.DIST.2T(x, degrees)`	Both tails of Student’s t distribution.
T.DIST.RT	`T.DIST.RT(x, degrees)`	The right tail of Student’s t distribution.
T.INV	`T.INV(probability, degrees)`	The inverse of Student’s t left tail.
T.INV.2T	`T.INV.2T(probability, degrees)`	The inverse of Student’s two-tailed t.
TREND	`TREND(knownY, [knownX], [newX])`	Values along the least-squares line through the known points.
TRIMMEAN	`TRIMMEAN(array, percent)`	The mean after discarding a percentage from each end.
VAR	`VAR(number1, …)`	The variance of a sample.
VAR.P	`VAR.P(number1, …)`	The variance of a whole population.
VAR.S	`VAR.S(number1, …)`	The variance of a sample.
VARP	`VARP(number1, …)`	The variance of a whole population.
WEIBULL.DIST	`WEIBULL.DIST(x, alpha, beta, cumulative)`	The Weibull distribution.
Z.TEST	`Z.TEST(array, x, [sigma])`	The one-tailed probability of a z-test.

Older statistical names

These names were replaced in Excel 2010 by the functions in their descriptions. They calculate, so workbooks that use them keep working; the function browser’s search lists the current names.

Function	Syntax	What it does
BETADIST	`BETADIST(x, alpha, beta, [a], [b])`	The cumulative beta distribution. The pre-2010 name, whose fourth and fifth arguments are the bounds rather than a flag.
BETAINV	`BETAINV(probability, alpha, beta, [a], [b])`	The inverse of the beta distribution. The pre-2010 name for BETA.INV.
BINOMDIST	`BINOMDIST(successes, trials, probability, cumulative)`	The binomial distribution. The pre-2010 name for BINOM.DIST.
CHIDIST	`CHIDIST(x, degrees)`	The right tail of the chi-square distribution. The pre-2010 name for CHISQ.DIST.RT.
CHIINV	`CHIINV(probability, degrees)`	The inverse of the chi-square right tail. The pre-2010 name for CHISQ.INV.RT.
CRITBINOM	`CRITBINOM(trials, probability, alpha)`	The smallest number of successes whose cumulative chance reaches alpha. The pre-2010 name for BINOM.INV.
EXPONDIST	`EXPONDIST(x, lambda, cumulative)`	The exponential distribution. The pre-2010 name for EXPON.DIST.
FDIST	`FDIST(x, degrees1, degrees2)`	The right tail of the F distribution. The pre-2010 name for F.DIST.RT.
FINV	`FINV(probability, degrees1, degrees2)`	The inverse of the F right tail. The pre-2010 name for F.INV.RT.
GAMMADIST	`GAMMADIST(x, alpha, beta, cumulative)`	The gamma distribution. The pre-2010 name for GAMMA.DIST.
GAMMAINV	`GAMMAINV(probability, alpha, beta)`	The inverse of the gamma distribution. The pre-2010 name for GAMMA.INV.
HYPGEOMDIST	`HYPGEOMDIST(successes, draw, populationSuccesses, population)`	The hypergeometric distribution. The pre-2010 name, always non-cumulative.
LOGINV	`LOGINV(probability, mean, sd)`	The inverse of the lognormal distribution. The pre-2010 name for LOGNORM.INV.
LOGNORMDIST	`LOGNORMDIST(x, mean, sd, cumulative)`	The lognormal distribution. The pre-2010 name for LOGNORM.DIST.
NEGBINOMDIST	`NEGBINOMDIST(failures, successes, probability, cumulative)`	The negative binomial distribution. The pre-2010 name for NEGBINOM.DIST.
NORMDIST	`NORMDIST(x, mean, sd, cumulative)`	The normal distribution. The pre-2010 name for NORM.DIST.
NORMINV	`NORMINV(probability, mean, sd)`	The inverse of the normal distribution. The pre-2010 name for NORM.INV.
POISSON	`POISSON(x, mean, cumulative)`	The Poisson distribution. The pre-2010 name for POISSON.DIST.
TDIST	`TDIST(x, degrees)`	Both tails of Student’s t distribution. The pre-2010 name for T.DIST.2T.
TINV	`TINV(probability, degrees)`	The inverse of Student’s two-tailed t. The pre-2010 name for T.INV.2T.
WEIBULL	`WEIBULL(x, alpha, beta, cumulative)`	The Weibull distribution. The pre-2010 name for WEIBULL.DIST.
ZTEST	`ZTEST(array, x, [sigma])`	The one-tailed probability of a z-test. The pre-2010 name for Z.TEST.

Engineering

The app calls this category Engineering. It has no button on the ribbon; search for these functions in the function browser.

Complex-number functions take and return complex numbers written as text, such as "3+4i", and keep the i or j suffix you use. CONVERT converts between units of the same kind — length, mass, temperature and so on — and refuses to convert between different kinds.

Function	Syntax	What it does
BESSELI	`BESSELI(x, n)`	The modified Bessel function In(x).
BESSELJ	`BESSELJ(x, n)`	The Bessel function Jn(x).
BESSELK	`BESSELK(x, n)`	The modified Bessel function Kn(x).
BESSELY	`BESSELY(x, n)`	The Bessel function Yn(x).
BIN2DEC	`BIN2DEC(text)`	A binary number as a decimal one.
BIN2HEX	`BIN2HEX(number, [places])`	Converts a number between bases.
BIN2OCT	`BIN2OCT(number, [places])`	Converts a number between bases.
BITAND	`BITAND(number1, number2)`	The bits set in both numbers.
BITLSHIFT	`BITLSHIFT(number, amount)`	The bits moved left.
BITOR	`BITOR(number1, number2)`	The bits set in either number.
BITRSHIFT	`BITRSHIFT(number, amount)`	The bits moved right.
BITXOR	`BITXOR(number1, number2)`	The bits set in one number but not the other.
COMPLEX	`COMPLEX(real, imaginary, [suffix])`	Builds a complex number from a real and an imaginary part.
CONVERT	`CONVERT(number, from, to)`	Converts a number from one unit of measurement to another.
DEC2BIN	`DEC2BIN(number, [places])`	A number written in binary.
DEC2HEX	`DEC2HEX(number, [places])`	A number written in hexadecimal.
DEC2OCT	`DEC2OCT(number, [places])`	A number written in octal.
DELTA	`DELTA(number1, [number2])`	One when two numbers are equal, zero otherwise.
ERF	`ERF(lower, [upper])`	The error function between two limits.
ERF.PRECISE	`ERF.PRECISE(x)`	The error function.
ERFC	`ERFC(x)`	The complementary error function, 1 − ERF(x).
ERFC.PRECISE	`ERFC.PRECISE(x)`	The complementary error function.
GESTEP	`GESTEP(number, [step])`	One when a number reaches a threshold.
HEX2BIN	`HEX2BIN(number, [places])`	Converts a number between bases.
HEX2DEC	`HEX2DEC(text)`	A hexadecimal number as a decimal one.
HEX2OCT	`HEX2OCT(number, [places])`	Converts a number between bases.
IMABS	`IMABS(inumber)`	The absolute value (modulus) of a complex number.
IMAGINARY	`IMAGINARY(inumber)`	The imaginary part of a complex number.
IMARGUMENT	`IMARGUMENT(inumber)`	The angle of a complex number, in radians.
IMCONJUGATE	`IMCONJUGATE(inumber)`	The complex conjugate.
IMCOS	`IMCOS(inumber)`	The cosine of a complex number.
IMCOSH	`IMCOSH(inumber)`	The hyperbolic cosine of a complex number.
IMCOT	`IMCOT(inumber)`	The cotangent of a complex number.
IMCSC	`IMCSC(inumber)`	The cosecant of a complex number.
IMCSCH	`IMCSCH(inumber)`	The hyperbolic cosecant of a complex number.
IMDIV	`IMDIV(inumber1, inumber2)`	Divides one complex number by another.
IMEXP	`IMEXP(inumber)`	e raised to a complex power.
IMLN	`IMLN(inumber)`	The natural logarithm of a complex number.
IMLOG10	`IMLOG10(inumber)`	The base-10 logarithm of a complex number.
IMLOG2	`IMLOG2(inumber)`	The base-2 logarithm of a complex number.
IMPOWER	`IMPOWER(inumber, number)`	Raises a complex number to a power.
IMPRODUCT	`IMPRODUCT(inumber1, …)`	Multiplies complex numbers.
IMREAL	`IMREAL(inumber)`	The real part of a complex number.
IMSEC	`IMSEC(inumber)`	The secant of a complex number.
IMSECH	`IMSECH(inumber)`	The hyperbolic secant of a complex number.
IMSIN	`IMSIN(inumber)`	The sine of a complex number.
IMSINH	`IMSINH(inumber)`	The hyperbolic sine of a complex number.
IMSQRT	`IMSQRT(inumber)`	The square root of a complex number.
IMSUB	`IMSUB(inumber1, inumber2)`	Subtracts one complex number from another.
IMSUM	`IMSUM(inumber1, …)`	Adds complex numbers.
IMTAN	`IMTAN(inumber)`	The tangent of a complex number.
OCT2BIN	`OCT2BIN(number, [places])`	Converts a number between bases.
OCT2DEC	`OCT2DEC(text)`	An octal number as a decimal one.
OCT2HEX	`OCT2HEX(number, [places])`	Converts a number between bases.

Information

The app calls this category Information. It has no button on the ribbon; search for these functions in the function browser.

Function	Syntax	What it does
CELL	`CELL(info, [reference])`	Information about a cell — its address, contents, width or type.
ERROR.TYPE	`ERROR.TYPE(value)`	A number identifying which error a cell holds.
INFO	`INFO(type)`	Information about the environment.
ISBLANK	`ISBLANK(value)`	Whether a cell is empty.
ISERR	`ISERR(value)`	Whether a value is an error other than #N/A.
ISERROR	`ISERROR(value)`	Whether a value is any error.
ISEVEN	`ISEVEN(number)`	Whether a number is even.
ISFORMULA	`ISFORMULA(reference)`	Whether a cell holds a formula rather than a typed value.
ISLOGICAL	`ISLOGICAL(value)`	Whether a value is TRUE or FALSE.
ISNA	`ISNA(value)`	Whether a value is #N/A.
ISNONTEXT	`ISNONTEXT(value)`	Whether a value is anything but text.
ISNUMBER	`ISNUMBER(value)`	Whether a value is a number.
ISODD	`ISODD(number)`	Whether a number is odd.
ISOMITTED	`ISOMITTED(argument)`	Whether an argument was left out.
ISREF	`ISREF(value)`	Whether something is a reference.
ISTEXT	`ISTEXT(value)`	Whether a value is text.
N	`N(value)`	A value as a number: text becomes zero, TRUE becomes one.
NA	`NA()`	The #N/A value, for marking data that is genuinely missing.
SHEET	`SHEET([value])`	The number of a sheet.
SHEETS	`SHEETS()`	How many sheets the workbook has.
TYPE	`TYPE(value)`	A code for what kind of value something is.

Database

The app calls this category Database. It has no button on the ribbon; search for these functions in the function browser.

Each database function works on a list with a header row (database), a column named by its heading or position (field), and a criteria range (criteria): a header row naming columns, with conditions in the rows below. Conditions in one row must all hold; each row is an alternative.

Function	Syntax	What it does
DAVERAGE	`DAVERAGE(database, field, criteria)`	Averages the matching rows of a column.
DCOUNT	`DCOUNT(database, field, criteria)`	Counts the matching rows holding a number.
DCOUNTA	`DCOUNTA(database, field, criteria)`	Counts the matching rows whose field is not empty.
DGET	`DGET(database, field, criteria)`	The single matching value, or an error when there is not exactly one.
DMAX	`DMAX(database, field, criteria)`	The largest value among the matching rows.
DMIN	`DMIN(database, field, criteria)`	The smallest value among the matching rows.
DPRODUCT	`DPRODUCT(database, field, criteria)`	Multiplies the matching rows of a column.
DSTDEV	`DSTDEV(database, field, criteria)`	The sample standard deviation of the matches.
DSTDEVP	`DSTDEVP(database, field, criteria)`	The population standard deviation of the matches.
DSUM	`DSUM(database, field, criteria)`	Adds the matching rows of a column.
DVAR	`DVAR(database, field, criteria)`	The sample variance of the matches.
DVARP	`DVARP(database, field, criteria)`	The population variance of the matches.