# Excel Functions Supported by OfficeCLI: Complete Guide to 350+ Formula Implementations

> Explore OfficeCLI's support for over 350 Excel functions like SUM, AVERAGE, and VLOOKUP. The iOfficeAI OfficeCLI repository offers a complete formula implementation guide.

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

---

**OfficeCLI supports over 350 Excel functions—including SUM, AVERAGE, VLOOKUP, IF, and complex statistical distributions—implemented via the `OfficeCli.Core` namespace in the iOfficeAI/OfficeCLI repository.**

The iOfficeAI/OfficeCLI project provides a command-line interface for manipulating Excel workbooks without requiring Microsoft Excel installation. At its core lies a comprehensive formula engine capable of parsing and evaluating Excel functions supported by OfficeCLI, enabling users to compute everything from simple aggregations to advanced statistical models directly from the terminal.

## Architecture of the OfficeCLI Formula Engine

### Core Implementation Files

The evaluator is architected as a **partial class** split across specialized files in `src/officecli/Core/Formula/`:

- **[`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs)** – Core parser, tokenizer, and AST builder that manages `FormulaEvalSession` for memoization across sheets.
- **[`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs)** – Contains 350+ Excel function implementations accessed via the `EvalFunction` method.
- **[`FormulaEvaluator.Helpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Helpers.cs)** – Numeric aggregation utilities, range flattening, and error handling helpers.
- **[`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 hygiene logic implementing L1/L2 policies for stale value detection.

### Evaluation and Dispatch Process

When evaluating a formula, the engine tokenizes the input string, resolves cell references, and builds an abstract syntax tree (AST). Function dispatch occurs through the `EvalFunction` method, which routes **case-insensitive** function names to their implementations using a C# switch statement. Arguments arrive as pre-evaluated objects, with range references wrapped in `RangeData` structures that provide methods like `ToDoubleArray()` for flattening numeric cells while ignoring errors.

## Supported Excel Functions by Category

### Math and Aggregation Functions

OfficeCLI implements fundamental arithmetic and aggregation operations:

`SUM`, `SUBTOTAL`, `AGGREGATE`, `SUMPRODUCT`, `AVERAGE`, `COUNT`, `COUNTA`, `COUNTBLANK`, `MIN`, `MAX`, `PRODUCT`, `QUOTIENT`, `MROUND`, `MOD`, `POWER`, `SQRT`, `FACT`, `COMBIN`, `PERMUT`, `GCD`, `LCM`, `RAND`, `RANDBETWEEN`, `EVEN`, `ODD`, `ROMAN`, `ARABIC`, `BASE`, `DECIMAL`

Rounding and precision functions include:

`ABS`, `SIGN`, `INT`, `TRUNC`, `ROUND`, `ROUNDUP`, `ROUNDDOWN`, `CEILING`, `CEILING_MATH`, `FLOOR`, `FLOOR_MATH`

Logarithmic and exponential operations:

`LOG`, `LOG10`, `LN`, `EXP`

### Trigonometric Functions

Complete trigonometric and hyperbolic support includes:

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

### Statistical and Distribution Functions

The engine provides comprehensive statistical analysis capabilities:

**Central tendency and dispersion**: `MEDIAN`, `MODE`, `MODE_SNGL`, `GEOMEAN`, `HARMEAN`, `TRIMMEAN`, `AVEDEV`, `STDEV`, `STDEV_S`, `STDEVP`, `STDEV_P`, `VAR`, `VAR_S`, `VARP`, `VAR_P`, `DEVSQ`, `SKEW`, `SKEW_P`, `KURT`

**Ranking and percentiles**: `RANK`, `RANK_EQ`, `LARGE`, `SMALL`, `PERCENTRANK`, `PERCENTRANK_INC`, `QUARTILE`, `QUARTILE_INC`, `QUARTILE_EXC`, `PERCENTILE`, `PERCENTILE_INC`, `PERCENTILE_EXC`

**Distributions**: `NORM_DIST`, `NORMDIST`, `NORM_S_DIST`, `NORMSDIST`, `NORM_INV`, `NORMINV`, `NORM_S_INV`, `NORMSINV`, `STANDARDIZE`, `GAUSS`, `PHI`, `CONFIDENCE`, `CONFIDENCE_NORM`, `GAMMA`, `GAMMALN`, `GAMMALN_PRECISE`, `GAMMA_DIST`, `GAMMADIST`, `GAMMA_INV`, `CHISQ_DIST`, `CHISQ_DIST_RT`, `CHIDIST`, `CHISQ_INV`, `CHISQ_INV_RT`, `POISSON_DIST`, `POISSON`, `T_DIST`, `T_DIST_2T`, `T_DIST_RT`, `TDIST`, `T_INV`, `TINV`, `T_INV_2T`, `F_DIST`, `FDIST`, `F_INV`, `FINV`, `BETA_DIST`, `BETADIST`, `BETA_INV`, `BETAINV`, `BINOM_DIST`, `BINOMDIST`, `BINOM_INV`, `CRITBINOM`, `NEGBINOM_DIST`, `NEGBINOMDIST`, `WEIBULL_DIST`, `WEIBULL`, `LOGNORM_DIST`, `LOGNORMDIST`, `LOGNORM_INV`, `LOGINV`, `HYPGEOM_DIST`, `HYPGEOMDIST`

**Regression, correlation, and testing**: `CORREL`, `PEARSON`, `COVARIANCE_P`, `COVAR`, `COVARIANCE_S`, `SLOPE`, `INTERCEPT`, `RSQ`, `STEYX`, `FORECAST`, `FORECAST_LINEAR`, `TREND`, `GROWTH`, `LINEST`, `LOGEST`, `T_TEST`, `TTEST`, `CHISQ_TEST`, `CHITEST`, `F_TEST`, `FTEST`, `Z_TEST`, `ZTEST`

**Special functions**: `ERF`, `ERFC`, `ERF_PRECISE`, `ERFC_PRECISE`, `FISHER`, `FISHERINV`, `PERMUTATIONA`

### Logical Functions

Conditional logic and Boolean operations:

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

### Text Functions

String manipulation capabilities:

`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`

## Formula Caching and Cache Hygiene

The engine implements a two-level cache system defined in [`ExcelHandler.FormulaCache.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.FormulaCache.cs). During workbook persistence, the `RefreshStaleFormulaCaches` method walks every formula cell and compares cached `<v>` values against freshly evaluated results. 

Functions listed in `FormulaCacheL1Allowlist` retain their cached values through the **L1 path**, while unverified functions trigger the **L2 path**, which clears cached values to prevent stale data persistence. This ensures that unsupported or newly added functions do not corrupt the workbook with outdated calculations.

## Practical Usage Examples

### Command Line Usage

```bash

# Write a SUM formula referencing a range

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

# Import data that feeds the formula

officecli import Sheet1 data.csv

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

officecli view Sheet1/A1

# → 42.5

# Attempt to use an unsupported function

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

# → #NAME!

# Save workbook with cache hygiene

officecli save workbook.xlsx

```

### Programmatic C# Integration

```csharp
using OfficeCli.Core;

// Instantiate evaluator with workbook context
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}");

```

## Summary

- OfficeCLI implements **350+ Excel functions** across mathematical, statistical, trigonometric, logical, and text categories in the `OfficeCli.Core` namespace.
- The engine uses a **partial class architecture** with core parsing in [`FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.cs) and function dispatch in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs).
- **Case-insensitive function names** are routed via the `EvalFunction` switch statement, with range references handled through `RangeData` objects.
- **Formula caching** employs an L1/L2 policy where verified functions retain cached values during persistence, while unverified functions trigger cache invalidation via `RefreshStaleFormulaCaches`.
- The evaluator propagates **Excel-compatible errors** (`#DIV/0!`, `#NAME!`, `#NUM!`) matching native Excel behavior.
- Both **CLI commands** and **C# programmatic APIs** support formula evaluation with memoization via `FormulaEvalSession`.

## Frequently Asked Questions

### How many Excel functions does OfficeCLI support?

OfficeCLI supports over 350 Excel functions spanning mathematics, statistics, trigonometry, text manipulation, and logical operations. The complete implementation resides in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs) as a switch-based dispatch system that handles case-insensitive function names.

### What happens if I use an Excel function that OfficeCLI doesn't recognize?

If you reference an unsupported function, the evaluator returns `#NAME!` to match Excel's error propagation behavior. Additionally, if the function is not on the `FormulaCacheL1Allowlist`, the cache hygiene system will clear any stale cached values for that cell during workbook persistence through the L2 path.

### Does OfficeCLI support array formulas and range references?

Yes, functions accepting ranges (such as `SUM`, `AVERAGE`, and `VLOOKUP`) receive arguments wrapped as `RangeData` objects. Helper methods like `RangeData.ToDoubleArray()` flatten numeric cells while ignoring errors, enabling proper calculation across cell ranges without O(n²) re-evaluation.

### Can I extend OfficeCLI with custom Excel functions?

While the source code shows that functions are data-driven through the `EvalFunction` switch blocks in [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.Functions.cs), adding new functions requires modifying the source and rebuilding the project. Any function added to the evaluator automatically becomes available to CLI users, though you should update the `FormulaCacheL1Allowlist` if you want the new function to support cached value retention rather than triggering L2 cache clearing.