Function prefixes

Some newer functions in Excel needs to have prefixes when added to the Formula property via EPPlus. The most common prefix is "_xlfn.". EPPlus 7 and up will handle these attribute when calculating workbook that already contains them (as the workbook most likely was created by Excel), but when you add your own formulas via EPPlus v6 and below you need to be aware of this.

Here is an example:

sheet.Cells["A1"].Formula = "DAYS($B$1+1,H5)";
sheet.Calculate(); // this will result in a #NAME error in cell A1 when opened in Excel

The DAYS function is one of the functions that needs to have an _xlfn. prefix

sheet.Cells["A1"].Formula = "_xlfn.DAYS($B+1,H5)";
sheet.Calculate(); // this will work

Here is one page where Microsoft describes the prefixes.

Epplus 7

From version 7 and up, EPPlus will add the prefixes for you if they are not included. You can see which functions EPPlus adds prefixes to in our supported functions list (column Prefix).

See also

Formula calculation