# How OfficeCLI's Excel Formula Engine Evaluates Dynamic Array Functions

> Discover how OfficeCLI's Excel formula engine evaluates dynamic array functions using tokenization, parsing, array lifting, and spill-range handling. Unlock advanced Excel capabilities in C#.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: internals
- Published: 2026-07-29

---

**OfficeCLI implements a full-featured Excel formula engine in C# that evaluates dynamic array functions through tokenization, recursive-descent parsing, function dispatch with automatic array lifting, and spill-range handling with implicit intersection.**

OfficeCLI is an open-source command-line interface for manipulating Excel workbooks without requiring Microsoft Office installation. Its **Excel formula engine** evaluates dynamic array functions such as `SEQUENCE`, `FILTER`, and `SORT` by tokenizing formulas, building an abstract syntax tree, and executing functions that return rectangular spill ranges. According to the iOfficeAI/OfficeCLI source code, the engine supports over 350 Excel functions through a layered architecture that includes automatic array lifting and implicit intersection handling.

## The Three-Layer Evaluation Architecture

OfficeCLI's formula evaluation splits into distinct logical layers: tokenization and parsing, session management, and function dispatch. This separation enables efficient handling of complex workbook calculations while supporting modern Excel features.

### Tokenization and Recursive-Descent Parsing

The entry point for formula evaluation resides in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs), where the `Tokenize` method (lines 91-150) scans formula strings into discrete tokens including numbers, cell references, operators, and the `TT.ArrayLit` type for array literals. A recursive-descent parser then constructs an abstract syntax tree through methods like `ParseExpression`, `ParseComparison`, and `ParseAtom`, respecting operator precedence and parentheses along the way.

For array constants such as `{1,2;3,4}`, the parser invokes `ParseArrayConstant` (lines 800-825) to distinguish row separators (`;`) from column separators (`,`), ultimately building a `RangeData` object that represents the rectangular grid in memory.

### Session Management and Memoization

To prevent exponential re-evaluation when formulas reference each other across worksheets, the engine creates a `FormulaEvalSession` (lines 94-114 in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)) for each workbook. This session stores memoized cell results, tracks visited nodes for cycle detection, and caches range materializations. When a dynamic array function spills into dependent cells, the session ensures those values remain available for subsequent formula evaluations without recalculation.

### Function Dispatch System

After parsing reduces an expression to an AST, function calls route through `EvalFunction` in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) (lines 13-108). This method implements a dispatch table via a `switch` statement covering more than 350 Excel functions, forwarding each to dedicated helpers like `EvalSumProduct`, `EvalIf`, or `EvalSequence`. The dynamic array functions reside in a dedicated region of this file, specifically lines 92-108, where they return `RangeData` objects wrapped as `FormulaResult.Area` instances.

## Dynamic Array (Spill) Evaluation Mechanisms

Modern Excel's dynamic array behavior requires specific handling for spilled ranges, array literals, and scalar-to-array operations. OfficeCLI mirrors Excel 365's behavior through four key mechanisms.

### Array Literal Recognition

During tokenization, the engine recognizes array literals through `TT.ArrayLit` tokens. When `ParseArrayConstant` encounters these tokens, it constructs a multi-dimensional `RangeData` structure that represents the literal grid. This allows formulas like `={1,2,3}` to evaluate immediately as a spilled horizontal array without requiring a surrounding function call.

### Spill-Aware Function Implementation

Functions specifically designed to return dynamic arrays—such as `SEQUENCE`, `FILTER`, `SORT`, and `UNIQUE`—are implemented in the dynamic arrays region of `EvalFunction` (lines 92-108). These functions return `RangeData` objects representing their full rectangular output. The top-level `EvaluateFormula` method automatically handles **implicit intersection** when a downstream scalar context consumes the spill, extracting the first element (top-left cell) to match Excel's legacy behavior.

### Automatic Scalar Lifting

Scalar functions that do not natively handle arrays can still operate on dynamic arrays through **automatic lifting**. The engine maintains a `LiftableScalarFunctions` list and applies `TryLiftOverArrays` (lines 30-45 in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)) to map functions like `ABS` or `SIN` over each element of an input array. This produces a spilled result without requiring explicit array logic inside each function implementation.

### Implicit Intersection and Broadcasting

When binary operators encounter mixed scalar and array operands, `ApplyBinaryOp` (lines 176-194) implements broadcasting rules matching Excel's behavior. If one operand is a grid (`RangeData`) and the other is scalar, the scalar broadcasts across the grid. When a spill result appears where a single value is required, `EvaluateFormula` (lines 60-66) collapses the array to its first element. Mismatched dimensions during broadcasting emit `#N/A` errors for out-of-range cells, ensuring compatibility with Excel's error propagation.

## Evaluation Flow from Parse to Result

The complete evaluation lifecycle follows five distinct phases:

1. **Parse** – `Tokenize` generates tokens, then `ParseExpression` builds the AST.
2. **Resolve** – `ResolveCellResult` and `ResolveSheetCellResult` look up cached values or recursively evaluate dependencies using the session's memoization cache.
3. **Execute** – `EvalFunction` dispatches to specific implementations, with scalar functions potentially lifted via `TryLiftOverArrays`.
4. **Spill handling** – Functions returning arrays produce `RangeData` objects; downstream scalar contexts trigger implicit intersection through `EvaluateFormula`.
5. **Result conversion** – The engine converts final values through `FormulaResult` types (`NumericValue`, `StringValue`, `BoolValue`, `ArrayValue`, `RangeValue`) into cell output via `ToCellValueText`, rendering values for XML or CLI display.

## Working with Dynamic Arrays in OfficeCLI

The `officecli view` command exposes the formula engine's capabilities for testing dynamic array behavior directly from the terminal.

Evaluate a simple arithmetic formula:

```bash
officecli view myworkbook.xlsx --cell C3

# Internally: new FormulaEvaluator(sheetData, workbookPart)

# evaluator.TryEvaluateFull("=A1+B2") returns a numeric FormulaResult

```

Create a spilled sequence using the `SEQUENCE` function:

```bash
officecli view myworkbook.xlsx --cell D1 \
  --set "D1=SEQUENCE(3,2,10,5)"

# Writes a 3×2 spill range starting at 10 with step 5

# EvalFunction("SEQUENCE", args) → EvalSequence → returns RangeData (3×2)

```

Demonstrate automatic array lifting with the `ABS` function:

```bash
officecli view myworkbook.xlsx --eval "ABS({-1,2;-3,4})"

# ABS is in LiftableScalarFunctions, so TryLiftOverArrays maps over each element

# Result: a 2×2 spill with {1,2;3,4}

```

Show implicit intersection behavior:

```bash
officecli view myworkbook.xlsx --cell E5 \
  --set "E5=B1:B3*1"

# ApplyBinaryOp sees RangeData × scalar

# EvaluateFormula collapses to first cell (B1) before writing to E5

```

## Summary

- **OfficeCLI's formula engine** separates concerns into tokenization/parsing, session management, and function dispatch to handle complex Excel calculations efficiently.
- **Dynamic array support** includes spill-aware functions like `SEQUENCE` and `FILTER`, automatic scalar lifting for functions like `ABS`, and proper handling of array literals through `ParseArrayConstant`.
- **Implicit intersection** automatically collapses spilled arrays to single values when scalar contexts require it, while broadcasting rules allow arithmetic between scalars and grids.
- **Memoization via `FormulaEvalSession`** prevents exponential re-evaluation and detects circular references across worksheet dependencies.

## Frequently Asked Questions

### How does OfficeCLI handle implicit intersection when a scalar references a spilled array?

When a formula expects a single value but receives a spilled array, the `EvaluateFormula` method (lines 60-66 in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)) automatically performs implicit intersection by returning the first element (top-left cell) of the spill range. This matches Excel's backward-compatible behavior where pre-dynamic-array formulas referencing an entire range would receive only the intersecting cell value.

### What mechanism allows scalar functions like ABS to operate on dynamic arrays?

OfficeCLI implements **automatic array lifting** through the `TryLiftOverArrays` method (lines 30-45 in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)). Functions listed in `LiftableScalarFunctions` automatically map across each element of an array argument, producing a new array result without requiring explicit array logic inside the function implementation. This allows scalar mathematical functions to produce spilled outputs when given array inputs.

### How does the evaluation session prevent circular references during formula calculation?

The `FormulaEvalSession` class (lines 94-114 in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)) maintains a visited-node set that tracks cells currently undergoing evaluation. When a formula references another cell that is already in the evaluation stack, the engine detects the cycle and can halt evaluation or return a circular reference error, preventing infinite recursion in interdependent dynamic array formulas.

### Which source file handles modern Excel function names prefixed with _xlfn.?

The [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs) file in `src/officecli/Core/Formula/` manages the `_xlfn.` namespace prefix used by newer Excel functions. This utility ensures that functions stored with qualified names in modern workbooks are correctly recognized and dispatched to their appropriate handlers in `EvalFunction`, bridging compatibility between different Excel versions.