# How OfficeCLI's Formula Engine Evaluates Excel Functions and Handles Dynamic Arrays

> Discover how OfficeCLI's formula engine evaluates Excel functions and handles dynamic arrays. Learn about its AST parsing, built-in functions, and support for spill ranges in this technical deep dive.

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

---

**OfficeCLI implements a three-layered C# formula engine that tokenizes and parses Excel syntax into an AST, dispatches over 350 built-in functions through a centralized switch table, and supports modern dynamic array behaviors including spill ranges, implicit intersection, and element-wise lifting.**

OfficeCLI's formula engine provides a complete Excel-compatible calculation environment written in C#, enabling the CLI to evaluate workbook formulas without requiring Microsoft Excel. The engine handles everything from basic arithmetic to complex dynamic array functions like `SEQUENCE` and `FILTER`, mirroring Excel 365's behavior through a sophisticated tokenization, parsing, and evaluation pipeline.

## Architecture of the Formula Engine

The engine separates concerns into three distinct logical layers that transform formula strings into calculated values while preventing exponential re-evaluation through intelligent memoization.

### Tokenization and Parsing

The evaluation pipeline begins in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) where the `Tokenize` method scans formula strings and emits typed tokens for numbers, strings, cell references, ranges, operators, functions, and array literals. A recursive-descent parser then constructs an abstract syntax tree (AST) through methods like `ParseExpression`, `ParseComparison`, and `ParseAtom`, properly handling operator precedence, parentheses, and special tokens such as the `%` postfix operator.

The parser recognizes Excel-specific syntax including array literals like `{1,2;3,4}` through dedicated handling in `ParseArrayConstant`, which builds a `RangeData` object representing the grid structure.

### Evaluation Session Management

To prevent infinite recursion and redundant calculations when formulas reference cells across multiple worksheets, the engine uses a `FormulaEvalSession` instance created per workbook. This session, defined in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs), stores memoized cell results, maintains cross-sheet evaluator references, tracks visited nodes for cycle detection, and caches range materializations.

When a formula references another cell that hasn't been evaluated yet, `ResolveCellResult` or `ResolveSheetCellResult` lookups consult this session cache first, ensuring that complex dependency chains evaluate efficiently without exponential overhead.

### Function Dispatch System

After AST construction, function calls route through `EvalFunction` in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), which implements a massive switch statement containing over 350 Excel functions. Each case forwards to dedicated evaluation helpers such as `EvalSumProduct`, `EvalIf`, `EvalDate`, or `EvalTextSplit`, all residing in the same partial class.

The dispatcher recognizes modern Excel functions through [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs), which handles the `_xlfn.` namespace prefix used by newer functions to ensure backward compatibility when workbooks store qualified names.

## Dynamic Array and Spill Range Support

OfficeCLI fully implements Excel 365's dynamic array behaviors, allowing functions to return rectangular spill ranges that automatically populate adjacent cells rather than single scalar values.

### Array Literals and Parser Support

The tokenizer recognizes array literal syntax (`{1,2;3,4}`) through the `TT.ArrayLit` token type. The `ParseArrayConstant` method (lines 800-825 in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)) parses semicolons as row separators and commas as column separators, constructing a `RangeData` object wrapped in a `FormulaResult.Area` return type.

This allows users to define inline matrices that participate in calculations exactly as they would in Excel's grid.

### Spill-Aware Functions (SEQUENCE, FILTER, etc.)

Dynamic array functions such as `SEQUENCE`, `FILTER`, and `SORT` live in the *Dynamic arrays / spill* region of `EvalFunction`. These implementations return `RangeData` objects (or arrays) wrapped as `FormulaResult.Area` instances rather than single values.

When the top-level `EvaluateFormula` method encounters these area results in contexts expecting scalar values, it performs **implicit intersection** by returning the first element, matching Excel's behavior when referencing a spill range from a single cell.

### Element-Wise Lifting for Scalar Functions

Scalar functions marked as *liftable* in the `LiftableScalarFunctions` set (such as `SIN`, `COS`, and `ABS`) automatically map over each element of array arguments through the `TryLiftOverArrays` method. This automatic vectorization produces spilled arrays without requiring each function to implement explicit array logic.

For example, `=ABS({-1,2;-3,4})` evaluates to `{1,2;3,4}` through automatic lifting rather than requiring array-specific code in the `ABS` implementation.

### Implicit Intersection and Broadcasting

When binary operators encounter mixed operands (one grid and one scalar), the `ApplyBinaryOp` method broadcasts the scalar across the grid dimensions. If dimensions mismatch, out-of-range cells emit `#N/A` errors, replicating Excel's element-wise arithmetic rules.

In scalar contexts consuming an array result (such as `=B1:B3*1` in a single cell), `EvaluateFormula` collapses the spill to its top-left cell value through implicit intersection, maintaining compatibility with pre-dynamic-array Excel behavior while supporting modern spill semantics.

## Evaluation Flow and Result Handling

The complete evaluation process follows five distinct phases:

1. **Parse** – `Tokenize` converts the formula string into tokens, then `ParseExpression` builds the AST.
2. **Resolve references** – `ResolveCellResult` checks the session memo cache or recursively evaluates dependencies.
3. **Execute** – `EvalFunction` dispatches to the appropriate implementation, with automatic lifting applied to eligible scalar functions.
4. **Spill handling** – Array-producing functions return `RangeData`; downstream scalar contexts trigger implicit intersection or broadcasting via `ApplyBinaryOp`.
5. **Result conversion** – `FormulaResult` provides typed accessors (`NumericValue`, `StringValue`, `BoolValue`, `ErrorValue`, `ArrayValue`, `RangeValue`) with `ToCellValueText` rendering final output for XML serialization or CLI display.

This pipeline ensures that [`ExcelHandler.View.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.View.cs) can create evaluators for specific worksheets and format results for the `officecli view` command while maintaining full fidelity with Excel's calculation engine.

## Code Examples and CLI Usage

The following examples demonstrate how to interact with the formula engine through OfficeCLI commands:

```bash

# Evaluate a simple arithmetic formula in cell C3

officecli view myworkbook.xlsx --cell C3

# Internally: new FormulaEvaluator(sheetData, workbookPart)

# 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 3×2 RangeData

# Values spill into D1:E3 starting at 10 with step 5

```

```bash

# Demonstrate automatic array lifting with ABS

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

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

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

```

```bash

# Show implicit intersection behavior

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

# ApplyBinaryOp sees RangeData × scalar

# EvaluateFormula collapses to first cell (B1) for scalar context

```

## Summary

- **Three-layer architecture**: Tokenization/parser ([`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)), session-based memoization (`FormulaEvalSession`), and function dispatch (`EvalFunction` switch with 350+ functions).
- **Dynamic array support**: Full implementation of spill ranges, array literals (`{1,2;3,4}`), and modern functions like `SEQUENCE` and `FILTER` through `RangeData` objects.
- **Automatic vectorization**: Scalar functions in `LiftableScalarFunctions` automatically lift over arrays via `TryLiftOverArrays`, while `ApplyBinaryOp` handles broadcasting.
- **Excel compatibility**: Implicit intersection and error propagation match Excel 365 behavior, with [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs) handling `_xlfn.` prefixes for newer functions.
- **CLI integration**: [`ExcelHandler.View.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.View.cs) exposes the engine through the `officecli view` command, supporting both formula evaluation and dynamic array generation.

## Frequently Asked Questions

### Does OfficeCLI support all Excel 365 dynamic array functions?

OfficeCLI implements the core dynamic array infrastructure including `SEQUENCE`, `FILTER`, `SORT`, and spill range handling. The `EvalFunction` dispatcher in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) contains a dedicated region for these functions, and the engine automatically handles spill behaviors through `RangeData` objects and implicit intersection logic.

### How does the engine prevent infinite loops when cells reference each other?

The `FormulaEvalSession` class maintains a visited-node set that tracks which cells are currently being evaluated. Before recursing into a cell reference, `ResolveCellResult` checks this set; if a cycle is detected, the engine halts evaluation and returns an appropriate error value rather than entering infinite recursion.

### Can OfficeCLI evaluate formulas containing custom user-defined functions?

Currently, the engine supports the 350+ built-in Excel functions implemented in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) through the `EvalFunction` switch table. Custom user-defined functions would require extending the dispatcher, though the architecture supports adding new function cases to the switch statement or implementing additional handlers in the partial class files.

### Why does multiplying a range by a scalar only show one result in OfficeCLI?

When a formula like `=B1:B3*1` appears in a single cell context, `EvaluateFormula` performs **implicit intersection** per Excel's backward-compatibility rules, returning only the first element of the spill range. To see the full array result, the formula must be entered in a cell with sufficient adjacent space to accommodate the spill, or viewed using the `--eval` flag which displays the complete `RangeData` output.