Functions
Which functions exist, how criteria are written, and how dates and regular expressions behave.
Function status
The compatibility report lists every function with one of three statuses, and the same data is in compatibility.json:
| Status | Meaning |
|---|---|
implemented |
Matches Excel on the reference workbooks. |
known deviation |
Implemented, with a described difference from Excel. |
not implemented |
The function name evaluates to #NAME?. |
A function that does not appear in the report evaluates to #NAME? as well.
Criteria
COUNTIF, SUMIF, AVERAGEIF, the *IFS functions and the D database functions take criteria written as in Excel:
| Criterion | Matches |
|---|---|
5 or "=5" |
The number 5 and the text "5" |
">5", "<=5", "<>5" |
Numbers compared with 5. < and > compare only values of the same type. |
"<>" |
Any non-empty cell; the text "" counts as non-empty |
"" |
Empty cells and the text "" |
"app*", "?pple" |
Text with wildcards: * any run of characters, ? one character |
"~*" |
A literal *; ~ escapes *, ? and ~ |
TRUE, "true" |
Logical values only |
Wildcards apply to text only. In the D functions, a plain text criterion matches text that starts with it, as in Excel.
MATCH (match type 0), XMATCH and XLOOKUP (match mode 2) accept the same wildcards but none of the operators: MATCH(">5", A1:A9, 0) looks for the text ">5".
Dates and times
Dates and times are numbers: serial days, with the time of day as the fraction. Text that looks like a date or time, such as "2024-01-10" or "12:00", is converted to a number when arithmetic or a function needs one, using Workbook.Culture. Two-digit years 00–29 mean 2000–2029.
Grouping
GROUPBY and PIVOTBY summarize a table the way a pivot table does, and PERCENTOF gives a
part of a total. The function argument is a function passed by name (SUM, AVERAGE,
PERCENTOF), a LAMBDA, or several of them side by side with HSTACK. A function of two
arguments, such as PERCENTOF or LAMBDA(part, all, SUM(part)/SUM(all)), also gets the values its
group is a part of (in PIVOTBY, the ones relative_to picks):
sheet["D1"].Formula = "=GROUPBY(A1:A3, B1:B3, SUM)"; // North 40, South 20, Total 60
sheet["G1"].Formula = "=GROUPBY(A1:A3, B1:B3, HSTACK(SUM, MAX))"; // a header row SUM, MAX, then one column each
workbook.Recalculate();
Groups are sorted ascending, empty keys last; totals are labelled Total, or Grand Total when
there are subtotals. The results were compared with Excel for the web cell by cell. In PIVOTBY,
several functions at once give #VALUE!.
Regular expressions
REGEXTEST, REGEXEXTRACT, REGEXREPLACE and match mode 3 of XLOOKUP and XMATCH use .NET regular expressions, not PCRE2 as Excel does. Most patterns behave the same. \d and \w also match non-ASCII digits and letters, and PCRE-only syntax such as possessive quantifiers is not available.