Orders two values the way the comparison operators do.
Negative, zero, or positive, like any JS comparator.
This is the single rule behind =, <>, <, <=, >, >=, so use it for any
comparison, equality included (compare(a, b) === 0). It ranks across types
(number < text < FALSE < TRUE) rather than coercing, reads a blank as
the other operand's zero value, rounds numbers before comparing, and collates text
case-insensitively in the workbook locale.
Do not reach for localeCompare instead: it uses the host's locale rather than the
workbook's, and does not sort the way a spreadsheet does.
Errors order last so the comparator stays total, though operators never see that case — an error operand propagates first.
options is for functions like SORT and SORTBY that expose case sensitivity as a
user-facing toggle. It affects the text branch only; ranking, blanks, and numeric
rounding are fixed.
Compiles criteria text into a predicate.
The criteria, exactly as the user wrote it — a comparison (">10", "<>x"),
a wildcard pattern ("a*"), or a literal of any type.
Optionaloptions: CriteriaOptions
How the criteria is matched. See IFormulaContext.CriteriaOptions.
A predicate to test cell values with. Call it once per cell.
This is the rule behind COUNTIF/SUMIF/AVERAGEIF, their *IFS forms, and the D
functions' criteria table, so reach for it in any function that takes a criteria rather than
parsing one — a criteria touches all three localization planes at once (a number in the
workbook's locale, a boolean word or error label in its language, text in its collation), and
they are easy to get subtly wrong one at a time.
Compile ONCE and apply the predicate across the range; the parse is not free, and a criteria does not vary per cell.
Offer BLANK cells to the predicate rather than skipping them — a blank is what "" and "<>"
select, and "<>x" matches one too.
Creates a range to return as a dynamic array result.
Optionalrows: number
The number of rows of the range. Default is 0.
Optionalcolumns: number
The number of columns of the range. Default is 0.
Optionalvalues: A single value to fill the range with, a grid of values, or a callback
invoked per cell. Default is null (an empty range).
Returns a range as it reads without its hidden rows or columns.
The range to filter.
Optionaloptions: FilterHiddenOptions
range itself when nothing in it is hidden; otherwise the same range reading with
the hidden rows or columns skipped (IRange.IteratorOptions.processRowSkip).
Use this for functions that ignore hidden data, such as SUBTOTAL.
Nothing is copied. The shape of range is kept, and a reference stays a reference: a hidden
cell is not visited and reads as empty, so every position still means the cell it meant, and
an aggregate that ignores blanks ignores it. A computed array has no rows or columns to hide
and is returned as it is.
A hidden row is a hidden row, whoever hid it: a filter and the user leave the same mark, and nothing here tells them apart. What a filter adds is a state of the range's SHEET, which IFormulaContext.FilterHiddenOptions.onlyWhileFiltered reads.
A formula that calls SUBTOTAL or AGGREGATE is recalculated when a row or column is hidden
or shown, and when a filter is set or cleared. One that reaches this only through a custom
function is not: it reads the state when it is next calculated.
Returns a copy of a range with any cell whose formula is a nested total removed.
The range to filter.
A new range without the nested totals, or range itself when there are none.
Use this for functions that must not double-count a total that sits inside their own data —
SUBTOTAL and AGGREGATE, which is also the pair being looked for. Excluding a cell and
blanking it are equivalent for every code either function offers (all are order-independent
and all ignore blanks), so removal is the simpler contract.
A cell qualifies when its formula CALLS SUBTOTAL or AGGREGATE anywhere in its tree —
=SUM(SUBTOTAL(9,A1:A3)) is excluded — and not when it merely references one: =A4, where A4
holds a subtotal, is ordinary data. function_num is irrelevant, and the two functions are
symmetric (either excludes either).
Exclusion is per CELL, not per row: with a subtotal in A4 and a plain 99 in B4,
SUBTOTAL(9,A1:B4) drops only A4 and keeps the 99.
One range over every cell of a ReportType slot's argument — the AREAS of a reference union
folded into a single column, or the range returned unchanged.
A union (A1:A5,B1:B2) reaches a IFunction.ParameterTypeJSON.reportType slot as one
IRange per area. Functions that answer over it do so because they walk MEMBERS and a union
simply has more of them — LARGE((DA1:DA5,DK1:DK2),2) is the second largest of all seven
cells. This is that walk, and it is a helper rather than a marshalling step on purpose: Excel
flattens in exactly the same place, a shared reducer its bodies opt into, which is why the
same union is flattened by LARGE, refused by SUMPRODUCT and answered #REF! by ROWS.
The result is a single column in area order, row-major within each area. SHAPE IS NOT PRESERVED — a union has none. A body that needs an extent must ask IFunctionArguments.getAreaCountAt and answer for itself.
Converts a serial date to a Date.
The serial date to convert.
OptionaldateOnly: boolean
If true, the fractional part is dropped and the time is midnight.
The serial becomes the date's local fields — see toSerialDate. For date arithmetic prefer getDateParts.
Returns coordinates as an address string.
Optionalcoords: Readonly<FixableCoords | FixableCellCoords>
The coordinates to get the address for. If not provided the current position is used.
Optionalr1c1: boolean
If true, the address will be in R1C1 format. Default is false (A1 format).
The current call's arguments — how many the caller supplied, in which positions, of what kind, and what each value was BEFORE coercion.
Argument REFLECTION, for the one thing a declaration cannot say: what happened at this call.
It exists to serve Excel's declared any, which means "no contract, the body decides" — every
other Excel type maps onto a type we already have and the marshaller handles it.
The returned view is valid ONLY while the body that asked for it is running. Views are pooled
by dispatch depth, so retaining one across an await, a callback, or a nested evaluation hands
you another call's arguments.
Returns the 0-based column of the cell being evaluated.
Splits a serial date into its calendar and clock fields.
The serial date to split.
OptionalreuseParts: boolean | DateParts
Avoids allocating a fresh IFormulaContext.DateParts:
true: the fields are written into a shared scratch object and returned — for a
one-off caller (a single-value function like YEAR/MONTH/DAY) that just wants to
avoid the allocation without owning a buffer itself. The result must be consumed
immediately (read a field, done) — a second true call anywhere overwrites it.Prefer this to fromSerialDate inside date functions. The spreadsheet calendar
has two days a JS Date cannot hold — serial 0 (1900-01-00) and the legacy
1900-02-29 at serial 60 — and converting to a Date silently shifts them.
IFormulaContext.DateParts is plain numbers and represents both.
Reads the formula text of a cell.
The row of the cell.
The column of the cell.
OptionalsheetName: string
The name of the sheet. If not provided the current sheet is used.
The formula text, or null if the cell has no formula.
Looks up another formula function so it can be called from yours.
The name of the function. Case-insensitive.
The function, or null if no function is registered under that name.
Returns the current date and time as a serial date.
The current date-time as a serial number.
Always read the clock through this, never Date.now(). The time is fixed for the whole
calculation pass, so every cell in one pass agrees, collaborators evaluating the same
workbook agree, and tests can pin it. Pair it with markVolatile so the function
picks up a fresh time on the next pass.
This is a calendar clock, not a stopwatch — use performance.now() to measure elapsed time.
Formats a value with a number format, as a cell would display it.
The number format to apply.
The value to format.
The formatted text, or null if the format cannot be applied.
This backs the TEXT function. For plain coercion to text use toText.
Returns a random number in [0, 1), like Math.random().
Always draw through this, never Math.random(). The source is the runtime's, so a test or a
replay can seed it, and the engine sees the draw: a draw is a PER-CELL read — two cells calling
the same function in one pass differ — where getNow is one value for the whole pass, and the
engine only shares an evaluation across the cells of a block when nothing per-cell was read.
Pair it with Volatile<T> so the function draws again on the next pass.
Under a seeded runtime (IRuntime.getRandomSeed) each draw is keyed by the cell, the pass and which draw it is within that cell, so a function that draws more than once gets distinct values and a cell gets the same ones however the pass was ordered. Take the draws where they are used rather than hoisting them, and the values follow the cells rather than the code path.
Resolves a range reference at runtime, returning an IReferenceRange.
Use this when the address is computed from arguments rather than known at parse time,
as INDIRECT, OFFSET, and SUMIF do.
An IReferenceRange representing the resolved range.
Each call adds a tracked dependency: the function is re-evaluated whenever the resolved range's data changes or its address moves. Prefer getValueAt when a plain value will do.
Resolves an ANCHOR-PLUS-LENGTH argument at runtime, returning an IReferenceRange clamped to the grid.
A few arguments honor only their top-left corner and are re-sized to the shape of the range
that drives them — SUMIF's sum_range, LOOKUP's result_vector. The window can therefore
run off the bottom or right of the grid: SUMIF(A1:A5,">0",A1048574) asks for five rows
starting three from the end. Excel reads the part that fits and treats the rest as blank, which
is what this returns; positions past the clamp read as undefined from getValueAt.
Use getReference for a reference that is the function's RESULT: an unformable window is
#REF! there rather than a short read. The clamp belongs to this handful of arguments only.
The extent clamped against is the ANCHOR's sheet, which is not necessarily the sheet being evaluated — getEntireCoords answers for the latter.
Returns the 0-based row of the cell being evaluated.
Returns information about the environment the formula is running in.
Builds a serial date from calendar and clock fields — the inverse of getDateParts.
Optionalhour: numberOptionalminute: numberOptionalsecond: numberOptionalmillis: numberFields may fall outside their normal range and carry, which is what makes DATE and
TIME arithmetic work: month 13 is January of the next year, hour 50 is two days plus
two hours, and getSerialDate(1900, 1, 0) is serial 0. The day carries in SERIAL
space, so it runs through the phantom 1900-02-29.
This validates nothing, and that is deliberate — a caller MUST choose its own
policy. No range check, no clamping, no bounds on any field, and no DATE-style
0..1899 -> +1900 year offset. Those belong to DATE's ARGUMENTS, not to the
calendar, and three things prove it:
DATE and DATEDIF's md/yd re-anchoring
want the raw carry above — DATEDIF("2020-01-31","2020-03-01","md") is -1
because Jan 31 rolls into March. EDATE/EOMONTH want the opposite and CLAMP to
the target month's length, so they pre-clamp before calling here. One conversion,
two opposite rules, both matching Excel. Baking either one in breaks the other.YEARFRAC basis 1 anchors a year
length on January 1 of laterYear + 1, which at the top of the calendar is
10000-01-01; Excel answers 1.9972602739726026 for
YEARFRAC(DATE(9998,1,1),DATE(9999,12,31),1) rather than #NUM!. A range check
here would break it.DATE's month errors outside
[-32767, 32766] while its day saturates to 32767, and TIME errors outside
[0, 32767] on every argument — same magic number, three different rules.Note the asymmetry with getDateParts, which DOES throw #NUM! outside the
calendar because it answers a user-facing question. An internal intermediate that
steps one day past the end must therefore not be round-tripped through it; compute
on the fields instead. That mistake made YEARFRAC(DATE(9999,1,1),DATE(9999,12,31),0)
#NUM! where Excel says 1.
Returns the number of sheets in the workbook.
Returns the 0-based index of a sheet.
Optionalsheet: string | IReferenceRange<Scalar>
The name of a sheet, or a reference to resolve to the sheet it is on. If not provided the current sheet is used.
-1 If the sheet does not exist.
Returns the range the formula anchored at a cell OCCUPIES — what the spill anchor operator
(A1#) resolves to.
The row the formula is anchored at.
The column the formula is anchored at.
OptionalsheetName: string
The name of the sheet. If not provided the current sheet is used.
The range, or null if no formula is anchored there.
The range is the formula's spill when it produced an array, the whole block for a legacy
array formula, and the cell ITSELF for an ordinary formula — every formula occupies at least
the cell it sits in. null means no formula is anchored at that cell: it holds a constant, it
is empty, or it merely lies INSIDE another cell's spill or array block.
Reads the value of a cell.
The row of the cell.
The column of the cell.
OptionalsheetName: string
The name of the sheet. If not provided the current sheet is used.
Optionaloptions: ValueOptions
Optional parameters for retrieving the value.
The value at the cell, or null if the cell is empty.
Unlike getReference, this does not add a tracked dependency.
Whether the call being evaluated is in Excel's VALUE mode, where a multi-cell reference at a
slot that reads ONE value is implicitly intersected with the formula's cell (the @ rule);
false is ARRAY mode, where such a slot reads the reference's first cell.
Only a body that accepts a whole reference at a single-value slot and reads it itself asks
(LINEST's const). The engine decides the mode from the formula around the call — a
scalar-valued root, an aggregator or a lookup above it are value mode; a spilling root, an
operator or lifting slot over an array-valued operand, a LAMBDA body and an array-entered
block are array mode — and the mode is inherited through every call that does not set it.
Outside a calculation this is false.
Returns true if the value is a usable date — either a Date or a number in the
workbook's serial date range.
Any value to check.
OptionaljsDateOnly: boolean
If true, only a Date without a time component is accepted.
Coerces a value to a JavaScript Date.
The serial number, text, or Date to parse.
OptionalrequireDate: boolean
If true, text must carry a date component; a time alone is rejected.
The date.
Prefer getDateParts for calendar arithmetic — see the note there.
Reads a typed literal in the workbook's language — the parse side of toText.
The text to read.
The boolean or error the text spells, or undefined when it spells neither and is
therefore just text.
Booleans and error labels are the two values whose SPELLING follows the spreadsheet language
rather than the workbook locale — WAHR is TRUE and #WERT! is #VALUE! in a German
workbook, the same plane toText writes them on. Reach for this wherever a function
interprets text the user typed; criteria (COUNTIF(range, "WAHR")) is the built-in case.
The canonical English spelling is ALWAYS accepted alongside the localized one, so text that came from storage or an API reads the same under every language. This is how the formula compiler reads a typed literal, and matching it is the point.
Numbers are not literals here. They follow the workbook LOCALE (the decimal separator) rather than the language, and belong to parseAsNumber.
Coerces a value to a number.
The text or number to parse.
Optionaloptions: ParseNumberOptions
Additional parsing options.
The number.
undefined passes straight through, so an optional argument that was omitted stays omitted.
Optionaloptions: ParseNumberOptionsCoerces a value to a serial date.
The text or number to parse.
OptionalrequireDate: boolean
If true, text must carry a date component; a time alone is rejected.
The serial date.
Converts a Date to a serial date.
The date to convert.
OptionaldateOnly: boolean
If true, the time of day is dropped and a whole-day serial is returned.
Use this for calendar arithmetic (day counts, coupon schedules) so a stray time component
cannot leak into the result.
Coerces a value to text — the same rendering & and CONCAT produce.
The value to render. Text passes through unchanged, a blank
(null/undefined) becomes '', and a boolean takes the workbook language's word
(TRUE, WAHR). Numbers get 15 significant digits, plain decimal while the digits fit
in 20 characters and scientific beyond that, using the workbook's decimal separator.
The rendered text.
Reach for this whenever a function needs to treat an argument as text.
This is coercion, not display. A cell's General format and TEXT(value, "General") are
fitted to the column and round much harder — TEXT(2^0.5, "General") is 1.414213562
where coercion gives 1.4142135623731. Use getNumberFormat when you want what
the user sees.
Everything a formula function is allowed to know about the world outside its arguments.
Remarks
Formula functions are pure — they cannot reach the workbook, the sheet they sit on, or the clock.
FormulaContextis the one channel that gives them sheet data, cell coordinates, date and number parsing, and evaluation hints without giving up that purity.Import it and call its methods directly; there is nothing to construct or thread through arguments. Every call is already scoped to the cell being evaluated:
Capabilities
markVolatile,getRow,getColumn,getEntireCoords,getRandomgetArgsformatResultscreateRange,flattenAreas,filterHiddengetReference,getValueAt,getFormulaAt,getSpillAtgetFunctiongetNow,toSerialDate,fromSerialDate,getDateParts,getSerialDate,parseAsDate,parseAsSerialDate,isValidDateparseAsNumber,parseAsLiteral,toText,comparecreateCriteriagetSheetCount,getSheetIndex,getAddress,getNumberFormatImplementors
The calculation engine provides the implementation. Third-party code (UDF packages, test harnesses) may supply its own for isolated evaluation or unit testing.