# How to Use the OfficeCLI FormulaEvaluator for Excel Formulas (350+ Functions, Spilling, and LAMBDA)

> Master OfficeCLI FormulaEvaluator to process 350+ Excel functions including spilling and LAMBDA directly from OpenXML data. No Excel installation needed for powerful formula evaluation.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-08-01

---

**OfficeCLI's built-in `FormulaEvaluator` class evaluates over 350 Excel functions—including modern dynamic-array spills and LAMBDA expressions—directly against OpenXML sheet data without requiring Excel installation.**

The `FormulaEvaluator` lives in the `OfficeCli.Core` namespace and ships as a partial class split across multiple implementation files. It provides a complete, standalone Excel formula engine that parses, computes, and returns results through a unified `FormulaResult` wrapper. Whether you need to calculate financial models, statistical regressions, or complex array transformations, this evaluator handles everything from scalar values to spilled arrays.

## Creating a FormulaEvaluator Instance

To begin evaluating formulas, instantiate the evaluator with the target worksheet's `SheetData` and the `WorkbookPart` from the OpenXML document. This initialization allows the engine to resolve cell references, cross-sheet lookups, and named ranges.

```csharp
using OfficeCli.Core;

// sheetData and workbookPart come from your OpenXML document
var evaluator = new FormulaEvaluator(sheetData, workbookPart);

```

The constructor stores references to the underlying OOXML objects, enabling the evaluator to read current cell values and write computed results back to the worksheet. According to the source code in [`ExcelHandler.View.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.View.cs), this is the standard pattern OfficeCLI uses internally when rendering formula results for display.

## Evaluating Basic Formulas

Call `EvalFormula()` with any valid Excel formula string. The method returns a `FormulaResult` object that encapsulates scalars, arrays, errors, or ranges.

```csharp
FormulaResult result = evaluator.EvalFormula("=SUM(A1:A10)");

if (!result.IsError)
{
    double total = result.AsNumber();
    Console.WriteLine($"Total: {total}");
}
else
{
    Console.WriteLine($"Error: {result.ErrorValue}");
}

```

The `FormulaResult` class (defined in [`FormulaResult.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaResult.cs)) provides strongly-typed accessors:
- `AsNumber()` returns a `double`
- `AsString()` returns the text representation
- `IsArray` indicates if the result spilled into multiple cells
- `ArrayValue` provides the underlying 2D array for spilled results

## Working with Dynamic Arrays and Spill Behavior

Modern Excel functions like `SEQUENCE`, `SORT`, `UNIQUE`, and `FILTER` return arrays that automatically spill into surrounding cells. The `FormulaEvaluator` implements this behavior in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) through generic array-spilling logic.

### Detecting Spilled Results

When a formula returns an array, the evaluator sets `IsArray = true` and populates `ArrayValue`:

```csharp
FormulaResult seq = evaluator.EvalFormula("=SEQUENCE(3, 2, 1, 10)");

if (seq.IsArray)
{
    double[,] matrix = seq.ArrayValue;
    // matrix dimensions: 3 rows × 2 columns
    // [ [1, 11],
    //   [2, 12],
    //   [3, 13] ]
    Console.WriteLine($"Spilled {matrix.GetLength(0)} rows × {matrix.GetLength(1)} columns");
}

```

### Array Functions

The evaluator supports lifting scalar functions to operate element-wise across arrays. Functions such as `MAP`, `REDUCE`, and `BYROW` are implemented in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) via `EvalMap`, `EvalReduce`, and `EvalByRow` respectively:

```csharp
// Double each value in a generated sequence
FormulaResult mapped = evaluator.EvalFormula(
    "=MAP(SEQUENCE(5, 1, 1, 1), LAMBDA(x, x * 2))");

// Result: {2, 4, 6, 8, 10}

```

## Using LAMBDA Functions

The `FormulaEvaluator` parses `LAMBDA` tokens into executable `Lambda` objects and invokes them via `InvokeLambda`. This functionality resides in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs).

### Defining and Calling LAMBDAs

You can define custom functions inline and invoke them immediately or pass them to higher-order functions:

```csharp
// Define and immediately invoke a LAMBDA
FormulaResult lambdaCall = evaluator.EvalFormula("=LAMBDA(x, y, x + y)(3, 5)");

// lambdaCall.AsNumber() == 8

// Store LAMBDA in a cell reference then invoke (advanced usage)
// The evaluator handles the binding automatically when parsing the formula

```

### LAMBDA with Spill Functions

Combine LAMBDA with dynamic-array functions for powerful transformations:

```csharp
// Calculate squares of a sequence
FormulaResult squares = evaluator.EvalFormula(
    "=MAP(SEQUENCE(1, 5, 1, 1), LAMBDA(x, x^2))");

// Returns: {1, 4, 9, 16, 25}

```

## Handling Errors and Special Functions

The evaluator mimics Excel's error propagation rules. In [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), the `EvalFunction` method filters error values unless the function is marked as error-transparent in the `ErrorTransparentFns` list.

### Error Transparency

Functions like `IF` and `IFERROR` evaluate lazily and handle errors gracefully:

```csharp
// Returns "fallback" instead of #DIV/0!
FormulaResult safe = evaluator.EvalFormula("=IFERROR(1/0, \"fallback\")");

```

Most functions abort on the first error argument, while error-transparent functions can suppress or trap errors. The implementation checks the error-transparent flag before deciding whether to propagate errors to the result.

## Complete C# Implementation Example

The following example demonstrates evaluating multiple formula types and handling both scalar and array results:

```csharp
using OfficeCli.Core;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

// Load workbook
using (SpreadsheetDocument doc = SpreadsheetDocument.Open("data.xlsx", false))
{
    WorkbookPart wbPart = doc.WorkbookPart;
    SheetData sheetData = wbPart.Workbook.Descendants<SheetData>().First();
    
    // Initialize evaluator
    var evaluator = new FormulaEvaluator(sheetData, wbPart);
    
    // 1. Statistical function
    FormulaResult avg = evaluator.EvalFormula("=AVERAGE(B2:B100)");
    Console.WriteLine($"Average: {avg.AsNumber()}");
    
    // 2. Financial function with solver (uses FormulaEvaluator.Solver.cs)
    FormulaResult irr = evaluator.EvalFormula("=IRR(C1:C10)");
    Console.WriteLine($"IRR: {irr.AsNumber():P4}");
    
    // 3. Dynamic array with spill
    FormulaResult unique = evaluator.EvalFormula("=SORT(UNIQUE(D2:D50))");
    if (unique.IsArray)
    {
        double[,] values = unique.ArrayValue;
        for (int i = 0; i < values.GetLength(0); i++)
        {
            Console.WriteLine($"Rank {i+1}: {values[i, 0]}");
        }
    }
    
    // 4. Custom LAMBDA calculation
    FormulaResult hypotenuse = evaluator.EvalFormula(
        "=LAMBDA(a, b, SQRT(a^2 + b^2))(3, 4)");
    Console.WriteLine($"Hypotenuse: {hypotenuse.AsNumber()}");
}

```

## Summary

- The `FormulaEvaluator` class in `OfficeCli.Core` provides a standalone Excel formula engine with no dependency on Excel installation.
- Instantiate the evaluator with `SheetData` and `WorkbookPart` to enable cross-reference resolution and OOXML integration.
- Access results through the `FormulaResult` wrapper, checking `IsArray` to handle spilled dynamic-array outputs.
- Implementations in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) enable full support for `LAMBDA` definitions and modern spill functions like `SEQUENCE`, `MAP`, and `REDUCE`.
- The function dispatch system in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) covers 350+ built-in functions across statistical, financial, and mathematical domains.
- Error handling follows Excel semantics, with special treatment for error-transparent functions defined in the evaluator's internal lists.

## Frequently Asked Questions

### What Excel functions does FormulaEvaluator support?

The evaluator supports over 350 built-in functions spanning mathematics, statistics, finance, text manipulation, and date/time calculations. According to the source code in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), this includes modern dynamic-array functions like `SEQUENCE`, `SORT`, `FILTER`, and `UNIQUE`, as well as legacy functions like `VLOOKUP`, `INDEX`, and `MATCH`. Financial iterative functions such as `XIRR` and `IRR` utilize numerical solvers defined in [`FormulaEvaluator.Solver.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Solver.cs).

### How does automatic array spilling work in the evaluator?

When a formula returns multiple values, the evaluator automatically spills the result into a 2D array accessible via `result.ArrayValue`. The spill logic is implemented in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) through methods like `EvalMap` and generic array-spilling handlers. The `FormulaResult.IsArray` property indicates whether the result represents a spilled range, allowing your code to handle scalar and array outputs appropriately.

### Can I define and reuse custom LAMBDA functions programmatically?

Yes. The evaluator parses `LAMBDA` expressions into executable objects via `InvokeLambda` as implemented in [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs). You can define LAMBDAs inline within formula strings and immediately invoke them, or pass them as arguments to higher-order functions like `MAP` and `REDUCE`. The evaluator handles parameter binding and recursion according to Excel's LAMBDA specification.

### Do I need Microsoft Excel installed to use FormulaEvaluator?

No. The `FormulaEvaluator` is a pure .NET implementation contained entirely within the `OfficeCli.Core` namespace. It operates directly on OpenXML `SheetData` objects without COM interop or external Excel processes. This makes it suitable for server-side document processing, CLI automation, and environments where Excel desktop is unavailable.