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

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 – Core parser, tokenizer, and AST builder that manages FormulaEvalSession for memoization across sheets.
• FormulaEvaluator.Functions.cs – Contains 350+ Excel function implementations accessed via the EvalFunction method.
• FormulaEvaluator.Helpers.cs – Numeric aggregation utilities, range flattening, and error handling helpers.
• FormulaEvaluator.References.cs – Reference functions including INDIRECT, OFFSET, and INDEX.
• 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. 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

# 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

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 and function dispatch in 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 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, 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.

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