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

> Discover how OfficeCLI's C# Excel formula evaluator handles dynamic arrays and 350+ functions with its advanced tokenization, AST parsing, and function dispatch architecture. Get precise results easily.

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

---

**OfficeCLI implements a full-featured Excel formula engine in C# that parses, evaluates, and spills dynamic arrays across more than 350 functions through a layered architecture of tokenization, AST parsing, and function dispatch.**

The iOfficeAI/OfficeCLI repository provides a command-line interface for manipulating Excel workbooks without Microsoft Office installed. At its core, the OfficeCLI Excel formula evaluator replicates modern Excel's calculation engine, supporting dynamic array spill behavior and an extensive function library entirely in managed code.

## Three-Layer Architecture of the Formula Engine

The evaluation system is organized into three distinct logical layers that transform formula strings into calculated results.

### Tokenization and Parsing

The process begins in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) where the `Tokenize` method scans formula strings and emits tokens for numbers, strings, cell references, ranges, operators, and functions. A recursive-descent parser (`ParseExpression → ParseComparison → … → ParseAtom`) constructs an abstract syntax tree while respecting operator precedence and handling special tokens such as the `%` postfix operator.

According to the source code 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#L91-L150), this layer handles array literals like `{1,2;3,4}` through dedicated token types and parsing logic.

### Evaluation Session Management

Each workbook utilizes a `FormulaEvalSession` instance that persists across `FormulaEvaluator` instances. This session stores memoized cell results, cross-sheet evaluators, visited-node sets for cycle detection, and cached range materializations.

As implemented in lines 94-114 of [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs), this caching layer prevents exponential re-evaluation when formulas reference each other across multiple worksheets.

### Function Dispatch System

After parsing, function calls route through the `EvalFunction` method, which contains a switch statement with over 350 cases covering Excel's function library. Each case forwards to dedicated helpers such as `EvalSumProduct`, `EvalIf`, `EvalDate`, or `EvalTextSplit` located in the partial class file [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs).

The dispatch table resides 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#L13-L108), where modern functions qualified with the `_xlfn.` namespace are handled via [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs).

## Dynamic Array and Spill Behavior

OfficeCLI mirrors Excel 365's dynamic array capabilities through several specialized mechanisms that handle array literals, spill ranges, and implicit intersections.

### Array Literals and Parsing

Array constants like `{1,2;3,4}` are recognized during tokenization via the `TT.ArrayLit` token type. The `ParseArrayConstant` method parses semicolon-separated rows and comma-separated columns, building a `RangeData` object that returns an `Area` result.

This parsing logic appears in lines 334-346 and 800-825 of [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs), enabling the engine to treat array literals as first-class range objects.

### Spill-Aware Functions

Native dynamic array functions—including `SEQUENCE`, `FILTER`, and `SORT`—are implemented in the *Dynamic arrays / spill* region of `EvalFunction`. These functions return `RangeData` objects wrapped as `FormulaResult.Area` instances.

When a downstream scalar context consumes the result, the top-level `EvaluateFormula` method automatically performs implicit intersection, returning the first element when a single value is expected. This behavior is defined in lines 92-108 of [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs).

### Element-Wise Lifting

Scalar functions marked as *liftable* (such as `SIN` and `ABS`) automatically map over each element of an array argument through the `TryLiftOverArrays` method. The `LiftableScalarFunctions` registry in lines 30-45 of [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) identifies which functions support this vectorization.

This mechanism produces spilled arrays without requiring explicit array logic in each function implementation.

### Implicit Intersection and Broadcasting

When an `Area` or array result appears where a scalar is required, `EvaluateFormula` collapses the spill to its top-left cell, matching Excel's pre-dynamic-array intersect behavior. For binary operations, the `ApplyBinaryOp` method (lines 176-194) broadcasts scalars across grid operands and emits `#N/A` for dimension mismatches.

## Supporting 350+ Excel Functions

The engine's breadth comes from the extensive `EvalFunction` switch statement that dispatches to specialized evaluation logic. Helper methods reside across three primary files:

- [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs): Core function implementations including dynamic array verbs
- [`FormulaEvaluator.Helpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Helpers.cs): Numeric utilities, comparison helpers, and error propagation
- [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs): Namespace handling for newer Excel functions prefixed with `_xlfn.`

Each function returns a `FormulaResult` structure providing `NumericValue`, `StringValue`, `BoolValue`, `ErrorValue`, `ArrayValue`, or `RangeValue`, with `ToCellValueText` rendering final values for XML serialization or CLI display.

## Practical Usage Examples

The CLI exposes the evaluator through commands that demonstrate the engine's capabilities:

```bash

# Evaluate a simple arithmetic formula referencing cells

officecli view myworkbook.xlsx --cell C3

# Internally: new FormulaEvaluator(sheetData, workbookPart)

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

```

```bash

# Write a dynamic array spill range using SEQUENCE

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

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

# ExcelHandler writes spill values into appropriate cells

```

```bash

# Demonstrate automatic array lifting with ABS

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

# ABS is in LiftableScalarFunctions, so the engine maps over each element

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

```

```bash

# Implicit intersection when multiplying range by scalar

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

# ApplyBinaryOp sees RangeData × scalar

# EvaluateFormula collapses to first cell (B1) before writing

```

## Summary

- **OfficeCLI** implements a complete Excel formula evaluator in C# with three logical layers: tokenization/parsing, session management, and function dispatch.

- The engine supports **dynamic arrays** through array literal parsing, spill-aware functions, automatic element-wise lifting, and implicit intersection.
- **350+ functions** are dispatched via a switch statement in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), with modern functions handled through namespace qualification.
- **Evaluation sessions** cache results and detect cycles to prevent exponential re-evaluation across complex workbooks.
- Binary operators support **broadcasting rules** that match Excel's element-wise arithmetic behavior.

## Frequently Asked Questions

### How does OfficeCLI handle Excel's new dynamic array functions like SEQUENCE and FILTER?

The `EvalFunction` method in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) contains dedicated cases for dynamic array functions. When `SEQUENCE` or `FILTER` is called, the corresponding helper (e.g., `EvalSequence`) returns a `RangeData` object wrapped as `FormulaResult.Area`. The top-level `EvaluateFormula` method then manages spill behavior, automatically intersecting or broadcasting results based on the consuming context.

### What prevents circular reference errors when evaluating complex workbooks?

The `FormulaEvalSession` class maintains a visited-node set that tracks cells currently under evaluation. Before recursing into a referenced cell, the engine checks this set in `ResolveCellResult`. If a cycle is detected, the evaluation halts appropriately, preventing infinite loops and stack overflow errors during cross-sheet formula resolution.

### Can scalar functions like SIN or ABS work with array inputs automatically?

Yes. The engine maintains a `LiftableScalarFunctions` registry in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs). When a function in this set receives an array argument, the `TryLiftOverArrays` method automatically maps the scalar operation over each element, producing a spilled array result without requiring explicit array handling code in each function implementation.

### How does the evaluator manage performance with interdependent formulas?

A shared `FormulaEvalSession` instance caches memoized cell results across all `FormulaEvaluator` instances within a workbook. When `ResolveCellResult` or `ResolveSheetCellResult` encounters a previously calculated cell, it returns the cached value rather than re-evaluating the formula tree, eliminating exponential re-evaluation penalties in deeply referenced worksheets.