Formula Calculation

EPPlus supports formula calculation. This means that you can let EPPlus calculate the results of the formulas in a workbook.

This is done by calling the Calculate() method, which is available on Workbook, Worksheet and Range level. When Calculate() is called EPPlus will evaluate the result of the formula and store the result as the Value of the cell - just like Excel does.

Example

using(var package = new ExcelPackage(@"c:\temp\MyWorkbook.xlsx"))
{
   // calculate all formulas in the workbook
   package.Workbook.Calculate();
   // calculate one worksheet
   package.Workbook.Worksheets["my sheet"].Calculate();
  // calculate a range
  package.Workbook.Worksheets["my sheet"].Cells["A1"].Calculate();
}

Some common mistakes

• Don't use localized function names. Only english names (such as SUM, IF, VLOOKUP, etc) are supported. See our full list of supported functions.
• Don't use localized parameter separators (like for example semicolon) between function arguments. Only comma is supported.
• Don't use localized array separators (like for example semicolon or backslash) between array items. Only comma and semi-colon is supported.
• Don't add the leading = sign in your formula. "=SUM(A1:A2)" is wrong, "SUM(A1:A2)" is correct.
• Table formulas - EPPlus uses the OOXML syntax, not the syntax used in the Excel user interface. See this wiki page.

Trouble shooting/Logging

If the formula calculation fails the calculated cells might contain excel errors (#VALUE, #NAME, etc) instead of the expected results. You can attach a logger to the formula parser before you call the Calculate() method - the logfile might be helpful to analyze the error/errors.

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();
         // The following method removes any logger attached to the workbook.
         package.Workbook.FormulaParserManager.DetachLogger();
}

The #NAME will occur if your formula contains an unsupported function. In versions prior to EPPlus 7 it can also occur due to missing function prefixes.

Excel compatibility

The Formula calculation capabilities of EPPlus is a subset of Excel's - here are some examples that are currently not supported yet by EPPlus:

• Iterative calculation
• Calculating pivot tables is supported but the values will not be written to the cells, see our wiki page on calculating pivot tables

See this page for a list of supported functions in calculations - Supported functions. You can also add your own implementation of functions in runtime, see the Samples project (available in a separate repository).

Floating point precision

From EPPlus 7 we have improved the precision in sums, averages, etc in functions in order to minimize floating point errors on largers number series.

Internally Excel stores numbers in the IEEE 754 binary 64-bit floating point format. In EPPlus we use the .NET double struct, which is same size and conforms to the same standard. Since some decimal numbers can't be exactly represented with a float number, these might look like a small error when you see the result of a calculation.

Here is a simple example where two doubles are added that you can try yourself in Visual Studios Immediate Window:

1.1d + 2.2d
3.3000000000000003

As you can see the result is not 3.3 as one would expect.

So when you see these small deviations from what Excel shows you, it is because EPPlus returns the "raw" result from the calculation based on mathematical operations on floating point numbers, whilst Excel rounds it to 15 significant figures. From version 5.5 we have added a parameter that rounds the .NET double type to 15 significant figures in the rounding functions (this is where the results can differ the most as a small deviation can cause the rounding to go in the "wrong" direction). The code below illustrates how this can generate different results.

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 up is PrecisionAndRoundingStrategy.Excel, for previous versions it is PrecisionAndRoundingStrategy.DotNet. You can also configure this behaviour via your application configuration file:

.NET Core/5+ - appsettings.json

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

.NET Framework (app.config/web.config)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="EPPlus:ExcelPackage.LicenseContext" value="Commercial" />
    <add key="EPPlus:ExcelPackage.PrecisionAndRoundingStrategy " value="DotNet" />
  </appSettings>
</configuration>

Here is a good article on this topic.

See also

• Circular references
• Referencing tables in formulas
• Dynamic array formulas
• Legacy array formulas
• Supported functions
• Regression analysis functions
• Custom functions for calculations
• Function prefixes

For code samples, see Sample 6 (C#) or Sample 6 (VB)