There are 149 built-in functions. Names and arguments follow Excel.

An unregistered function name produces #NAME?. You can add your own (Custom Functions).

Math and trigonometry

Statistics

The Excel 2010 spellings (STDEV.S, VAR.P) are the same functions as the older names (STDEV, VARP).

Aggregation over visible rows (SUBTOTAL / AGGREGATE)

These are the aggregates meant to be used with a filter or hidden rows. Both always leave out rows a filter removed, and both ignore cells inside their own range that are themselves SUBTOTAL / AGGREGATE — so a grand total never counts its sub-totals twice.

SUBTOTAL(function_num, ref1, [ref2], …)
AGGREGATE(function_num, options, ref1, [ref2], …)
AGGREGATE(function_num, options, array, k)          ← 14-19

* AGGREGATE only. SUBTOTAL also takes 101-111 (1-11 plus 100), which additionally leave out rows hidden by hand.

AGGREGATE’s options combine as follows.

Row visibility is not a cell edit, so it is not part of the dependency graph. These formulas are recomputed after a filter is applied, after an outline is collapsed or expanded, and after an undo; hiding or showing a row by hand is picked up when the grid next paints. A headless host that hides rows itself calls Worksheet.SyncAggregateFormulas() (a no-op when nothing moved).

Logic

IF IFS IFERROR IFNA AND OR NOT XOR SWITCH TRUE FALSE

Text

FIND is case-sensitive and SEARCH is not (as in Excel).

JIS (and its alias DBCS) widens half-width text to full-width, ASC narrows it back. Voiced marks compose and decompose (ｶ + ﾞ ↔ ガ), and characters with no half-width form — hiragana, kanji — are left alone. V4 delegated this to Microsoft.VisualBasic.Strings.StrConv; V5 carries its own table, so the result is the same on macOS and Linux.

Information

ISBLANK ISERR ISERROR ISNA ISNUMBER ISTEXT ISNONTEXT ISLOGICAL ISEVEN ISODD NA

Lookup and reference

VLOOKUP HLOOKUP XLOOKUP INDEX MATCH XMATCH OFFSET CHOOSE ADDRESS INDIRECT ROW ROWS COLUMN COLUMNS

Functions that return a reference — ROW, COLUMN, OFFSET and the like — read their arguments’ syntax tree directly, evaluating lazily. That is what lets OFFSET(A1, 1, 0) return a reference itself.

XLOOKUP / XMATCH take a match mode (0 exact, -1 next smaller, 1 next larger, 2 wildcard) and a search direction (1 first to last, -1 last to first). When the return array is more than one column (or row) wide, the matching row (or column) comes back as a range, which is what makes SUM(XLOOKUP(…)) work.

INDIRECT resolves text to a reference. A1-style text goes through the formula parser, so a sheet qualifier such as 'My Sheet'!A1 and $ markers behave exactly as they do inside a formula. Pass FALSE as the second argument for R1C1 text (R[1]C[0] is relative to the calling cell).

INDIRECT builds its reference from a string while evaluating, so the dependency graph cannot see what it reads. As in Excel it is volatile: re-evaluated on every recalculation.

OFFSET is not, as long as its displacement and size are literals: the target rectangle is resolved when the formula is registered and recorded as an ordinary precedent, so OFFSET(A1,1,0) recomputes when A2 changes and at no other time. Only a computed displacement (OFFSET(A1,C1,0)) falls back to being volatile.

Date and time

Dates are held as OLE Automation serial numbers. Both 1900-based and 1904-based xlsx files can be read.

NETWORKDAYS and WORKDAY treat Saturday and Sunday as the weekend and take an optional list of holidays (a range works) as their third argument. WEEKNUM’s second argument picks the day a week starts on (1 Sunday, 2 Monday, 11-17, 21 for ISO).

Criteria strings (the SUMIF family)

SUMIF, COUNTIF, AVERAGEIF and their multi-criteria versions accept the same criteria strings as Excel.

Error propagation

When any argument is an error, that error is generally what comes back. The exceptions are IFERROR, IFNA, ISERROR, ISERR and ISNA, whose job is to receive one.

Differences from Excel

• Localized function names are not supported. Japanese names such as 合計 do not work
• PHONETIC is not implemented. It presupposes a way to attach furigana (<rPh>) to a cell, so it is not implemented on its own
• Dynamic-array functions are not implemented — FILTER SORT UNIQUE SEQUENCE LET LAMBDA

Listing what is registered

foreach (string name in unvell.ReoGrid.Core.Formula.FunctionRegistry.Default.Names)
	Console.WriteLine(name);

That list is always authoritative. Where it disagrees with this page, the implementation is right.

What to read next

• Custom Functions
• Error Values