Formula Calculation (original) (raw)

EPPlus includes a powerful, built-in formula calculation engine — no Excel installation required. It evaluates formulas entirely in .NET and covers the vast majority of real-world spreadsheet scenarios.

What the engine supports

478 built-in functions — from basic math and text functions to advanced statistical, financial and engineering functions. See the full list of supported functions.
Dynamic array formulas — including automatic recalculation of dirty cells, spill ranges and implicit intersection, fully compatible with Excel's modern array behavior.
Legacy array formulas — the classic Ctrl+Shift+Enter style formulas are fully supported alongside the modern dynamic array model.
LAMBDA and LET — including recursion, currying and LAMBDAs as defined names, enabling functional programming patterns directly in spreadsheet formulas.
Pivot table calculation — including support for the GETPIVOTDATA function.
External workbook references — formulas that reference cells in other workbooks can be resolved and calculated.
High numerical precision — the engine uses techniques such as Kahan summation and precision-as-displayed rounding to minimize floating point errors and stay consistent with Excel's results.

Getting started

Call Calculate() on the workbook, worksheet or range level. EPPlus will evaluate each formula and store the result as the cell value — just as Excel does.

EPPlus automatically resolves dependencies between cells before calculating. It builds a dependency chain across all formulas and evaluates them in the correct order, regardless of how they are arranged in the workbook. This is handled entirely by the engine — there is nothing you need to configure or consider.

using (var package = new ExcelPackage(@"c:\temp\MyWorkbook.xlsx")) { // Calculate all formulas in the workbook package.Workbook.Calculate();

// Calculate a single worksheet
package.Workbook.Worksheets["my sheet"].Calculate();

// Calculate a single cell
package.Workbook.Worksheets["my sheet"].Cells["A1"].Calculate();

}

Things to keep in mind

Use English function names. Only English names such as SUM, IF and VLOOKUP are supported — localized names will not work.
Use comma as the argument separator. Do not use semicolons or other localized separators between function arguments.
Use comma or semicolon as the array separator. Other characters, such as backslash, are not supported.
Do not include the leading = sign. Write SUM(A1:A2), not =SUM(A1:A2).
Table formulas use OOXML syntax. EPPlus follows the OOXML specification, not the syntax shown in the Excel UI. See Referencing tables in formulas.

Troubleshooting & Logging

If a formula produces an unexpected result such as #VALUE or #NAME, attaching a logger before calling Calculate() can help you identify the problem.

var excelFile = new FileInfo(@"c:\myExcelFile.xlsx"); using (var package = new ExcelPackage(excelFile)) { // Output from the logger will be written to the following file var logfile = new FileInfo(@"c:\logfile.txt");

// Attach the logger before the calculation is performed
package.Workbook.FormulaParserManager.AttachLogger(logfile);

// Calculate — can also be executed on sheet or range level
package.Workbook.Calculate();

// Remove the logger when done
package.Workbook.FormulaParserManager.DetachLogger();

}

A #NAME error typically means the formula contains an unsupported function. In versions prior to EPPlus 7 it can also occur due to missing function prefixes.

Floating point precision

Internally Excel stores numbers in the IEEE 754 binary 64-bit floating point format. EPPlus uses the .NET double struct, which conforms to the same standard. Since some decimal numbers cannot be exactly represented as floating point values, calculations can occasionally produce results that differ slightly from what Excel displays.

From EPPlus 5.5, a PrecisionAndRoundingStrategy option is available that rounds .NET double values to 15 significant figures — matching Excel's behavior. This matters most in rounding functions, where a tiny floating point deviation can push the result in the wrong direction.

using (var package = new ExcelPackage()) { var sheet = package.Workbook.Worksheets.Add("test"); sheet.Cells["A1"].Value = 120253.8749999999d; sheet.Cells["A2"].Formula = "ROUND(A1,2)";

sheet.Calculate(opt => opt.PrecisionAndRoundingStrategy = PrecisionAndRoundingStrategy.Excel);
Assert.AreEqual(120253.88, sheet.Cells["A2"].Value);

sheet.Calculate(opt => opt.PrecisionAndRoundingStrategy = PrecisionAndRoundingStrategy.DotNet);
Assert.AreEqual(120253.87, sheet.Cells["A2"].Value);

}

The default value from EPPlus 7 and later is PrecisionAndRoundingStrategy.Excel. For earlier versions the default is PrecisionAndRoundingStrategy.DotNet. This can also be configured via your application configuration file:

.NET Core / .NET 5+ — appsettings.json

{ "EPPlus": { "ExcelPackage": { "LicenseContext": "Commercial", "PrecisionAndRoundingStrategy": "Excel" } } }

.NET Framework — app.config / web.config

See this article for a deeper dive into floating point arithmetic in Excel.

Excel compatibility

EPPlus covers the vast majority of Excel's formula functionality, but a few areas are not yet supported:

Iterative calculation — circular references that rely on iterative recalculation are not supported.
Pivot table cell values — pivot table formulas are calculated but the results are not written back to individual cells. See Calculating pivot tables.