Supported Excel Functions in OfficeCLI Formula Engine

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) 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. 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 provide numeric aggregation, statistical calculations, range flattening, and Excel-compatible error handling.

Cache Hygiene is managed by 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.

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.

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:

# 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:

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	Core parser, tokenizer, AST construction, and session management
FormulaEvaluator.Functions.cs	350+ function implementations and the EvalFunction dispatch switch
FormulaEvaluator.Helpers.cs	Numeric utilities, range flattening, and error handling
FormulaEvaluator.References.cs	Reference functions including INDIRECT, OFFSET, and INDEX
ExcelHandler.FormulaCache.cs	Cache-sweep algorithm for stale value detection
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, 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, 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}