# How OfficeCLI's Excel Formula Evaluator Supports Dynamic Arrays and 350+ Functions

> Discover how OfficeCLI's FormulaEvaluator supports dynamic arrays and 350+ Excel functions. Learn about its tokenization, parsing, and dispatch-based evaluation architecture.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: deep-dive
- Published: 2026-08-10

---

**OfficeCLI's `FormulaEvaluator` class implements a complete Excel-compatible calculation engine with dynamic-array spill support and over 350 built-in functions through a modular architecture spanning tokenization, recursive-descent parsing, and dispatch-based function evaluation.**

The OfficeCLI project's spreadsheet calculation capabilities live in the `OfficeCli.Core.Formula` namespace. At its core sits the `FormulaEvaluator` partial class, which delivers near-identical formula semantics to Microsoft Excel—including the modern dynamic-array functions introduced in Excel 2021 and Microsoft 365.

## Architecture of the Formula Engine

The evaluation pipeline follows a classic compiler-like structure with four distinct stages, each implemented across focused source files.

### Tokenization: Converting Formula Strings to Tokens

The `Tokenize` method in [[`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L73-L114) scans raw formula strings into a stream of `Token` objects. This lexer recognizes:

- Numeric literals, string literals, and boolean values
- Cell references (`A1`, `R1C1` style) and range operators (`:`, `,`, union)
- Array constants bracketed with `{…}`
- The new **LET** and **LAMBDA** keywords
- Quoted sheet names for cross-sheet references

### Recursive-Descent Parser

The parser builds a syntax tree through precedence-climbing methods defined in [[`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L41-L66):

- `ParseExpression` → `ParseComparison` → `ParseConcat` → `ParseAddSub` → `ParseMulDiv` → `ParsePower` → `ParseUnary` → `ParsePostfix`

The parser handles operator precedence, implicit intersection, the `%` postfix operator, and array/range coercion rules. This structure directly mirrors Excel's own calculation behavior.

### Evaluation Engine with Result Wrapping

The `EvaluateFormula` method converts parsed expressions into `FormulaResult` objects. These results encapsulate:

- **Scalars**: numbers, strings, booleans, errors
- **Arrays**: `double[]` for 1-D arrays
- **Ranges**: `RangeData` for 2-D spill results

Implicit intersection automatically collapses arrays to single cells in scalar contexts—matching Excel's legacy behavior for formulas like `=B1:B3*1`.

### Function Dispatch for 350+ Excel Functions

The `EvalFunction` method in [[`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Functions.cs) routes function calls to implementations. Each returns a `FormulaResult` with appropriate type tagging.

## Dynamic Array and Spill Support

OfficeCLI's dynamic-array implementation centers on **range-returning functions** that produce `FormulaResult.RangeValue` with preserved origin coordinates (`BaseRow`, `BaseCol`, `BaseSheet`).

### Spill-Capable Functions

The engine implements the complete modern Excel dynamic-array toolkit:

| Category | Functions |
|----------|-----------|
| Sequence generation | `SEQUENCE`, `MAKEARRAY` |
| Transformation | `SORT`, `SORTBY`, `UNIQUE`, `FILTER` |
| Restructuring | `EXPAND`, `HSTACK`, `VSTACK`, `WRAPROWS`, `WRAPCOLS` |
| Text splitting | `TEXTSPLIT`, `TEXTJOIN` (array-aware) |
| Functional programming | `MAP`, `BYROW`, `BYCOL`, `SCAN`, `REDUCE` |
| Lookup (modern) | `XLOOKUP`, `XMATCH` |

These functions create `RangeData` objects that occupy multiple cells, with downstream references automatically resolving `ROW`, `COLUMN`, and `ADDRESS` relative to the spill origin.

### LET and LAMBDA Implementation

User-defined functions arrive through two cooperating mechanisms in [[`FormulaEvaluator.SpecialFunctions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpecialFunctions.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.SpecialFunctions.cs):

- **`EvalLet`**: Binds named variables to intermediate results, enabling formula reuse without repetition
- **`MakeLambda`**: Captures parameter names plus tokenized body; `InvokeLambda` evaluates with arguments bound

The `Lambda` record (defined at [lines 28-30](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L28-L30) in the core file) stores these values in `FormulaResult.LambdaValue`, enabling higher-order operations like `REDUCE`, `MAP`, and `SCAN`.

## Cross-Sheet Evaluation and Performance

The `FormulaEvalSession` class provides per-session memoization for:

- Cell evaluation results
- Range materializations  
- Sheet evaluator instances

This cache prevents O(n²) blow-up when formulas reference other sheets. Defined names are inlined or tokenized on-the-fly through `GetDefinedNames` and `TryDefinedNameAsSimpleRef`.

The engine includes stack-overflow guards via `MaxSameSheetDepth` and `RuntimeHelpers.TryEnsureSufficientExecutionStack` ([lines 48-50](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L48-L50)).

## Error Handling and Excel Compatibility

Errors propagate through `FormulaResult.Error` with standard Excel codes: `#DIV/0!`, `#NUM!`, `#VALUE!`, `#REF!`, `#NAME?`, `#N/A`. The engine mimics Excel's coercion rules:

- Blank cells evaluate to `0` in arithmetic contexts
- Numeric-looking strings parse on demand
- `Infinity` maps to `#NUM!`

## Practical Usage Examples

### Basic Scalar Evaluation

```csharp
var sheet = workbook.WorkbookPart!.WorksheetParts.First().Worksheet.GetFirstChild<SheetData>()!;
var evaluator = new FormulaEvaluator(sheet, workbook.WorkbookPart);
var result = evaluator.TryEvaluateFull("=SUM(A1:A5) * 2");
Console.WriteLine(result?.ToCellValueText()); // → "1234"

```

### Dynamic Array Spill

```csharp
var spill = evaluator.TryEvaluateFull("=SEQUENCE(3,2,1,2)");
if (spill?.IsRange == true)
{
    var area = spill.RangeValue!;
    for (int r = 0; r < area.Rows; r++)
    {
        for (int c = 0; c < area.Cols; c++)
            Console.Write(area.Cells[r, c]?.AsNumber() + "\t");
        Console.WriteLine();
    }
    // Output:
    // 1   3
    // 2   4
    // 3   5
}

```

### LET and LAMBDA

```csharp
var lambdaResult = evaluator.TryEvaluateFull(
    "LET(x, A1, y, A2, LAMBDA(z, x+z)(y))");
Console.WriteLine(lambdaResult?.AsNumber()); // → A1 + A2

```

### Cross-Sheet with Memoization

```csharp
var session = new FormulaEvalSession();
var evalSheet1 = new FormulaEvaluator(sheet1, wbPart, session, 0, "Sheet1");
var evalSheet2 = new FormulaEvaluator(sheet2, wbPart, session, 0, "Sheet2");
var cross = evalSheet1.TryEvaluateFull("=Sheet2!B3 + 10");

```

## Key Source Files

| File | Responsibility |
|------|---------------|
| [[`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs) | Tokenizer, parser, evaluation engine, result types |
| [[`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Functions.cs) | 350+ function implementations |
| [[`FormulaEvaluator.SpecialFunctions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpecialFunctions.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.SpecialFunctions.cs) | `LET`, `LAMBDA`, higher-order functions |
| [[`FormulaEvaluator.Helpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Helpers.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Helpers.cs) | Numeric utilities, array helpers |
| [[`FormulaEvaluator.Solver.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Solver.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Solver.cs) | Iterative solvers (`RATE`, `IRR`) |
| [[`FormulaEvaluator.References.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.References.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.References.cs) | Cross-sheet logic, defined names |
| [[`FormulaEvaluator.Statistics.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Statistics.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Statistics.cs) | Statistical functions |
| [[`FormulaEvaluator.Regression.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Regression.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Regression.cs) | `LINEST`, `TREND`, `GROWTH` |
| [[`FormulaEvaluator.Securities.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Securities.cs)](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Securities.cs) | Financial calculations |

## Summary

- **Tokenization and parsing** in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) handle Excel's full formula syntax including array literals and modern operators
- **350+ functions** dispatch through `EvalFunction` in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) with consistent `FormulaResult` wrapping
- **Dynamic arrays** spill via `RangeData` objects with preserved origin coordinates for `ROW`/`COLUMN` resolution
- **LET/LAMBDA** enable user-defined functions and functional programming patterns
- **Session-based memoization** delivers efficient cross-sheet evaluation without recursive re-evaluation

## Frequently Asked Questions

### Which dynamic array functions does OfficeCLI implement?

OfficeCLI implements the complete modern Excel dynamic-array suite including `SEQUENCE`, `SORT`, `SORTBY`, `UNIQUE`, `FILTER`, `EXPAND`, `HSTACK`, `VSTACK`, `WRAPROWS`, `WRAPCOLS`, `TEXTSPLIT`, `XLOOKUP`, `XMATCH`, and the functional programming helpers `MAP`, `BYROW`, `BYCOL`, `SCAN`, and `REDUCE`. These return `RangeData` objects that automatically spill into adjacent cells.

### How does the formula evaluator prevent stack overflow on complex workbooks?

The evaluator implements two safeguards: `MaxSameSheetDepth` limits recursion depth within a single sheet, and `RuntimeHelpers.TryEnsureSufficientExecutionStack` checks available stack space before deep recursive calls. Additionally, the `FormulaEvalSession` cache eliminates re-evaluation cycles that could otherwise cause exponential recursion.

### Can OfficeCLI evaluate formulas that reference other worksheets?

Yes. The `FormulaEvalSession` class maintains memoized evaluators for each referenced sheet. Cross-sheet formulas like `=Sheet2!B3 + 10` resolve through [`FormulaEvaluator.References.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.References.cs), with defined names inlined via `TryDefinedNameAsSimpleRef` for optimal performance.

### Does OfficeCLI support Excel's new LAMBDA and LET functions?

Full support exists through `EvalLet` and `MakeLambda` in [`FormulaEvaluator.SpecialFunctions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpecialFunctions.cs). `LET` binds variables to intermediate results; `LAMBDA` captures参数 names and tokenized bodies for later invocation. The `Lambda` record stores these in `FormulaResult.LambdaValue`, enabling higher-order operations compatible with `MAP`, `REDUCE`, and `SCAN`.