# Supported Excel Functions in OfficeCLI Formula Engine

> Discover the extensive list of over 350 supported Excel functions within the OfficeCLI formula engine, covering math, stats, text, and logic. Integrate powerful calculations seamlessly.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: api-reference
- Published: 2026-07-12

---

**OfficeCLI implements a full-featured Excel formula evaluator that supports over 350 functions across mathematics, statistics, trigonometry, text manipulation, and logical operations.**

The OfficeCLI project provides a command-line interface for manipulating Excel workbooks without requiring Microsoft Excel to be installed. At its core, the `OfficeCli.Core` namespace contains a robust formula engine that can parse and evaluate Excel-compatible formulas using pure C# implementations.

## Architecture of the Formula Engine

The formula engine is architected as a **partial class** distributed across multiple files to handle distinct concerns: parsing, function dispatch, and helper utilities.

### Core Components

**`FormulaEvaluator`** (defined in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)) serves as the entry point. It tokenizes formula strings, resolves cell references, and manages a `FormulaEvalSession` to memoize results across sheets. This prevents O(n²) re-evaluation cycles when calculating interdependent cells.

**Function Dispatch** resides in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs). The `EvalFunction` method receives a case-insensitive function name and a list of evaluated arguments, then routes execution via a C# `switch` statement to the appropriate implementation.

**Helper Utilities** in [`FormulaEvaluator.Helpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Helpers.cs) provide numeric aggregation, statistical calculations, range flattening, and Excel-compatible error handling.

**Cache Hygiene** is managed by [`ExcelHandler.FormulaCache.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.FormulaCache.cs). The `RefreshStaleFormulaCaches` method performs an on-persist sweep that checks cached `<v>` values against freshly computed results, applying an L1/L2 policy to decide whether to keep or drop cached data.

## Complete List of Supported Excel Functions

OfficeCLI implements **350+ Excel functions** organized by category. The definitive implementation list resides in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs).

### Mathematical and Aggregation Functions

Core arithmetic and aggregation operations include:

- **Aggregation**: `SUM`, `SUBTOTAL`, `AGGREGATE`, `SUMPRODUCT`, `AVERAGE`, `COUNT`, `COUNTA`, `COUNTBLANK`, `MIN`, `MAX`, `PRODUCT`
- **Rounding**: `ROUND`, `ROUNDUP`, `ROUNDDOWN`, `CEILING`, `CEILING_MATH`, `FLOOR`, `FLOOR_MATH`, `MROUND`, `INT`, `TRUNC`
- **Arithmetic**: `ABS`, `SIGN`, `MOD`, `POWER`, `SQRT`, `QUOTIENT`, `RAND`, `RANDBETWEEN`
- **Combinatorics**: `FACT`, `COMBIN`, `PERMUT`, `PERMUTATIONA`, `GCD`, `LCM`
- **Number Theory**: `EVEN`, `ODD`, `ROMAN`, `ARABIC`, `BASE`, `DECIMAL`
- **Logarithmic**: `LOG`, `LOG10`, `LN`, `EXP`

### Trigonometric Functions

Full trigonometric support includes angular conversion and hyperbolic functions:

`PI`, `SIN`, `COS`, `TAN`, `ASIN`, `ACOS`, `ATAN`, `ATAN2`, `SINH`, `COSH`, `TANH`, `ASINH`, `ACOSH`, `ATANH`, `DEGREES`, `RADIANS`

### Statistical and Distribution Functions

The engine implements comprehensive statistical analysis capabilities:

- **Central Tendency**: `MEDIAN`, `MODE`, `MODE_SNGL`, `GEOMEAN`, `HARMEAN`, `AVEDEV`, `DEVSQ`, `TRIMMEAN`
- **Ranking**: `RANK`, `RANK_EQ`, `LARGE`, `SMALL`, `PERCENTILE`, `PERCENTILE_INC`, `PERCENTILE_EXC`, `PERCENTRANK`, `PERCENTRANK_INC`, `QUARTILE`, `QUARTILE_INC`, `QUARTILE_EXC`
- **Standard Deviation and Variance**: `STDEV`, `STDEV_S`, `STDEVP`, `STDEV_P`, `VAR`, `VAR_S`, `VARP`, `VAR_P`
- **Correlation and Regression**: `CORREL`, `PEARSON`, `COVARIANCE_P`, `COVARIANCE_S`, `COVAR`, `SLOPE`, `INTERCEPT`, `RSQ`, `STEYX`, `FORECAST`, `FORECAST_LINEAR`, `TREND`, `GROWTH`, `LINEST`, `LOGEST`
- **Normal Distribution**: `NORM_DIST`, `NORMDIST`, `NORM_S_DIST`, `NORMSDIST`, `NORM_INV`, `NORMINV`, `NORM_S_INV`, `NORMSINV`, `STANDARDIZE`, `GAUSS`, `PHI`
- **Other Distributions**: `CONFIDENCE`, `CONFIDENCE_NORM`, `GAMMA`, `GAMMA_DIST`, `GAMMADIST`, `GAMMA_INV`, `GAMMALN`, `GAMMALN_PRECISE`, `CHISQ_DIST`, `CHISQ_DIST_RT`, `CHIDIST`, `CHISQ_INV`, `CHISQ_INV_RT`, `POISSON_DIST`, `POISSON`, `FISHER`, `FISHERINV`, `BETA_DIST`, `BETADIST`, `BETA_INV`, `BETAINV`, `T_DIST`, `T_DIST_2T`, `T_DIST_RT`, `TDIST`, `T_INV`, `TINV`, `T_INV_2T`, `F_DIST`, `FDIST`, `F_INV`, `FINV`, `BINOM_DIST`, `BINOMDIST`, `BINOM_INV`, `CRITBINOM`, `NEGBINOM_DIST`, `NEGBINOMDIST`, `WEIBULL_DIST`, `WEIBULL`, `LOGNORM_DIST`, `LOGNORMDIST`, `LOGNORM_INV`, `LOGINV`, `HYPGEOM_DIST`, `HYPGEOMDIST`
- **Error Functions**: `ERF`, `ERFC`, `ERF_PRECISE`, `ERFC_PRECISE`
- **Shape and Tests**: `SKEW`, `SKEW_P`, `KURT`, `T_TEST`, `TTEST`, `CHISQ_TEST`, `CHITEST`, `F_TEST`, `FTEST`, `Z_TEST`, `ZTEST`

### Logical Functions

Boolean logic and conditional operations:

`IF`, `IFS`, `AND`, `OR`, `NOT`, `XOR`, `TRUE`, `FALSE`, `IFERROR`, `IFNA`, `SWITCH`, `CHOOSE`, `REDUCE`, `ISOMITTED`

### Text Functions

String manipulation and regular expression support:

`CONCATENATE`, `CONCAT`, `TEXTJOIN`, `LEFT`, `RIGHT`, `MID`, `LEN`, `TRIM`, `CLEAN`, `UPPER`, `LOWER`, `PROPER`, `REPT`, `CHAR`, `CODE`, `FIND`, `SEARCH`, `REPLACE`, `SUBSTITUTE`, `EXACT`, `VALUE`, `TEXT`, `TEXTBEFORE`, `TEXTAFTER`, `REGEXTEST`, `REGEXEXTRACT`

## How Formula Evaluation Works

When OfficeCLI encounters a formula, it proceeds through five distinct phases:

1. **Parsing**: `FormulaEvaluator` tokenizes the formula string and builds an abstract syntax tree (AST), resolving cell and range references.

2. **Dispatch**: For function nodes, `EvalFunction` performs a case-insensitive lookup in the switch statement defined in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs).

3. **Range Handling**: Functions accepting ranges (such as `SUM` or `AVERAGE`) receive arguments wrapped as `RangeData` objects. Helper methods like `RangeData.ToDoubleArray()` flatten numeric cells while ignoring errors.

4. **Error Propagation**: If any argument evaluates to an Excel error (`#DIV/0!`, `#NUM!`, etc.), the function returns that error unchanged, matching Excel's behavior exactly.

5. **Caching**: Results are memoized in `FormulaEvalSession.CellMemo`. During workbook persistence, `RefreshStaleFormulaCaches` compares cached `<v>` values against fresh evaluations. Functions not on the `FormulaCacheL1Allowlist` trigger cache invalidation (the L2 path) to prevent stale data persistence.

## Working with Formulas in OfficeCLI

### Command-Line Interface

The CLI supports direct formula entry and evaluation:

```bash

# Write a SUM formula referencing a range

officecli set Sheet1/A1 =SUM(B1:B5)

# Import dependent data

officecli import Sheet1 data.csv

# View computed result (evaluated on-the-fly)

officecli view Sheet1/A1

# → 42.5

# Handle unsupported functions gracefully

officecli set Sheet1/C1 =FOOBAR(D1)
officecli view Sheet1/C1

# → #NAME!

# Persist with cache hygiene

officecli save workbook.xlsx

```

### Programmatic API

For .NET applications, instantiate `FormulaEvaluator` directly:

```csharp
using OfficeCli.Core;

// Create evaluator for an open workbook part
var evaluator = new FormulaEvaluator(sheetData, workbookPart);
var result = evaluator.EvaluateForReport("=SUM(B1:B5)");

// Inspect the result
if (result.IsNumeric) 
    Console.WriteLine($"Sum = {result.NumericValue}");
else 
    Console.WriteLine($"Error = {result.ErrorValue}");

```

## Key Implementation Files

| File | Purpose |
|------|---------|
| [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) | Core parser, tokenizer, AST construction, and session management |
| [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) | 350+ function implementations and the `EvalFunction` dispatch switch |
| [`FormulaEvaluator.Helpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Helpers.cs) | Numeric utilities, range flattening, and error handling |
| [`FormulaEvaluator.References.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.References.cs) | Reference functions including `INDIRECT`, `OFFSET`, and `INDEX` |
| [`ExcelHandler.FormulaCache.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.FormulaCache.cs) | Cache-sweep algorithm for stale value detection |
| [`ExcelHandler.Set.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.cs) | CLI command handler for writing formulas to cells |

## Summary

- OfficeCLI supports **over 350 Excel functions** across mathematics, statistics, trigonometry, logic, and text processing.
- The engine is implemented as a **partial class** spanning [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs), [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), and helper files.
- `EvalFunction` provides **case-insensitive dispatch** to function implementations via a C# switch statement.

- **Range handling** converts cell ranges to numeric arrays using `RangeData` helpers while preserving Excel error semantics.
- **Cache hygiene** ensures stale formula values are dropped during workbook persistence unless explicitly allowed by the L1 allow-list.

## Frequently Asked Questions

### What happens if I use an Excel function that OfficeCLI does not support?

OfficeCLI returns `#NAME!` for unrecognized functions, consistent with Excel's error behavior. The evaluator checks the function name against the internal switch statement in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), and if no match is found, it propagates the error to the caller without crashing the evaluation session.

### Does OfficeCLI support array formulas or dynamic arrays?

The current implementation evaluates single-cell formulas and range references. While functions like `SUMPRODUCT` and aggregation functions handle array-like inputs through `RangeData` objects, dynamic array spill behavior (as in Excel 365) is handled through the standard cell-by-cell evaluation model in `FormulaEvalSession`.

### How does OfficeCLI handle circular references?

The `FormulaEvalSession` tracks visited cells during evaluation. If a formula references its own cell directly or indirectly through a dependency chain, the engine detects the cycle and returns an appropriate error value, preventing infinite recursion during the evaluation phase.

### Are function names case-sensitive in OfficeCLI?

No. The `EvalFunction` method in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) performs case-insensitive comparisons when routing function calls. This means `=SUM(A1:A5)`, `=sum(a1:a5)`, and `=Sum(A1:A5)` are all valid and equivalent in OfficeCLI's formula engine.