EPPlus 6/7/8
Features and technical overview
Formula Calculation improvements
Improvements and new functions
- Support for calculating dynamic array formulas and legacy array formulas.
- Using operators in combination with addresses/ranges, for example
A1:A3 + B1:B3 - 1from EPPlus 7.0 - Improved handling of addresses, support for the intersect operator and using functions in addresses from EPPlus 7.0
- Pivot table calculation from EPPlus 7.2
- Support for implicit intersection in calculations from EPPlus 7.0
- Support for LAMBDA functions from EPPlus 8.2
- Improved handling of circular references
- Reduced floating point errors in sums, averages, etc.
- Added PrecisionAndRoundingStrategy enum parameter to the Calculate function. PrecisionAndRoundingStrategy.DotNet to preserve backward compatibility, PrecisionAndRoundingStrategy.Excel (default from EPPlus 7) mimics Excels rounding to 15 significant figures in the rounding functions.
- Added support for Precision as Displayed. This means that decimal numbers in cells can be rounded according to the number format of the cell.
- Support for calculating external workbooks
- Minor improvements and fixes - see our change log
Supported functions
Functions
Compared with EPPlus 4, there are 322 new functions implemented in EPPlus 8.6 - it supports in total 478 functions (see below).
Learn more about the formula calculation capabilities of EPPlus in our guides.
Database
| Functions | ||
|---|---|---|
|
DAVERAGE v4
|
DMAX v4
|
DSTDEVP v7.3
|
|
DCOUNT v4
|
DMIN v4
|
DSUM v4
|
|
DCOUNTA v4
|
DPRODUCT v7.3
|
DVAR v4
|
|
DGET v4
|
DSTDEV v7.3
|
DVARP v4
|
Date and time
| Functions | ||
|---|---|---|
|
DATE v4
|
ISOWEEKNUM v4
|
TODAY v4
|
|
DATEDIF v5.5
|
MINUTE v4
|
WEEKDAY v4
|
|
DATEVALUE v4
|
MONTH v4
|
WEEKNUM v4
|
|
DAY v4
|
NETWORKDAYS v4
|
WORKDAY v4
|
|
DAYS v4
|
NETWORKDAYS.INTL v4
|
WORKDAY.INTL v5.0
|
|
DAYS360 v4
|
NOW v4
|
YEAR v4
|
|
EDATE v4
|
SECOND v4
|
YEARFRAC v4
|
|
EOMONTH v4
|
TIME v4
|
|
|
HOUR v4
|
TIMEVALUE v4
|
|
Engineering
| Functions | ||
|---|---|---|
|
BESSELI v5.2
|
ERF.PRECISE v5.2
|
IMLOG10 v7.0
|
|
BESSELJ v5.2
|
ERFC v5.2
|
IMLOG2 v7.0
|
|
BESSELK v5.2
|
ERFC.PRECISE v5.2
|
IMPOWER v7.0
|
|
BESSELY v5.2
|
GESTEP v7.0
|
IMPRODUCT v7.0
|
|
BIN2DEC v5.1
|
HEX2BIN v5.1
|
IMREAL v7.0
|
|
BIN2HEX v5.1
|
HEX2DEC v5.1
|
IMSEC v7.0
|
|
BIN2OCT v5.1
|
HEX2OCT v5.1
|
IMSECH v7.0
|
|
BITAND v5.1
|
IMABS v7.0
|
IMSIN v7.0
|
|
BITLSHIFT v5.1
|
IMAGINARY v7.0
|
IMSINH v7.0
|
|
BITOR v5.1
|
IMARGUMENT v7.0
|
IMSQRT v7.0
|
|
BITRSHIFT v5.1
|
IMCONJUGATE v7.0
|
IMSUB v7.0
|
|
BITXOR v5.1
|
IMCOS v7.0
|
IMSUM v7.0
|
|
COMPLEX v5.5
|
IMCOSH v7.0
|
IMTAN v7.0
|
|
CONVERT v5.1
|
IMCOT v7.0
|
OCT2BIN v5.1
|
|
DEC2BIN v5.1
|
IMCSC v7.0
|
OCT2DEC v5.1
|
|
DEC2HEX v5.1
|
IMCSCH v7.0
|
OCT2HEX v5.1
|
|
DEC2OCT v5.1
|
IMDIV v7.0
|
YIELDDISC v7.2.1
|
|
DELTA v5.1
|
IMEXP v7.0
|
|
|
ERF v5.2
|
IMLN v7.0
|
|
Financial
| Functions | ||
|---|---|---|
|
ACCRINT v6.0
|
FVSCHEDULE v5.2
|
PRICEDISC v7.0
|
|
ACCRINTM v6.0
|
INTRATE v5.5
|
PRICEMAT v7.0
|
|
COUPDAYBS v5.2
|
IPMT v5.2
|
PV v5.2
|
|
COUPDAYS v5.2
|
IRR v5.2
|
RATE v5.2
|
|
COUPDAYSNC v5.2
|
ISPMT v5.2
|
RECEIVED v7.0
|
|
COUPNCD v5.2
|
MDURATION v5.5
|
RRI v5.2
|
|
COUPNUM v5.2
|
MIRR v5.2
|
SLN v5.2
|
|
COUPPCD v5.2
|
NOMINAL v5.2
|
SYD v5.2
|
|
CUMIPMT v5.2
|
NPER v5.2
|
TBILLEQ v6.0
|
|
CUMPRINC v5.2
|
NPV v5.2
|
TBILLPRICE v6.0
|
|
DB v5.5
|
ODDFPRICE v7.0
|
TBILLYIELD v6.0
|
|
DDB v5.2
|
ODDFYIELD v7.0
|
VDB v7.0
|
|
DISC v5.2
|
ODDLPRICE v7
|
XIRR v5.2
|
|
DOLLARDE v5.5
|
ODDLYIELD v7
|
XNPV v5.2
|
|
DOLLARFR v5.5
|
PDURATION v5.2
|
YIELD v5.2
|
|
DURATION v5.2
|
PMT v4
|
YIELDMAT v5.5
|
|
EFFECT v5.2
|
PPMT v5.2
|
|
|
FV v5.2
|
PRICE v5.2
|
|
Information
| Functions | ||
|---|---|---|
|
BASE v8.3
|
ISFORMULA v8.6
|
ISREF v7
|
|
ERROR.TYPE v4
|
ISLOGICAL v4
|
ISTEXT v4
|
|
ISBLANK v4
|
ISNA v4
|
N v4
|
|
ISERR v4
|
ISNONTEXT v4
|
NA v4
|
|
ISERROR v4
|
ISNUMBER v4
|
SHEET v5.5
|
|
ISEVEN v4
|
ISODD v4
|
TYPE v4
|
Logical
| Functions | ||
|---|---|---|
|
AND v4
|
ISOMITTED v8.2
|
OR v4
|
|
BYCOL v8.2
|
LAMBDA v8.2
|
REDUCE v8.2
|
|
BYROW v8.2
|
LET v7.2
|
SCAN v8.2
|
|
FALSE v4
|
MAKEARRAY v8.2
|
SWITCH v5.0
|
|
IF v4
|
MAP v8.1
|
TRUE v4
|
|
IFERROR v4
|
MAXIFS v5.3
|
XOR v5.5
|
|
IFNA v4
|
MINIFS v5.3
|
|
|
IFS v5.0
|
NOT v4
|
|
Lookup and reference
| Functions | ||
|---|---|---|
|
_TRO_ALL v8.6
|
GROUPBY v8.6
|
SORTBY v7
|
|
_TRO_LEADING v8.6
|
HLOOKUP v4
|
TAKE v7
|
|
_TRO_TRAILING v8.6
|
HSTACK v7
|
TOCOL v7
|
|
ADDRESS v4
|
IMAGE v8
|
TOROW v7
|
|
ANCHORARRAY v7
|
INDEX v4
|
TRANSPOSE v7
|
|
CHOOSE v4
|
INDIRECT v4
|
TRIMRANGE v8
|
|
CHOOSECOLS v7
|
LOOKUP v4
|
UNIQUE v7
|
|
CHOOSEROWS v7
|
MATCH v4
|
VLOOKUP v4
|
|
COLUMN v4
|
OFFSET v4
|
VSTACK v7
|
|
COLUMNS v4
|
PIVOTBY v8.6
|
WRAPCOLS v8.6
|
|
DROP v7
|
ROW v4
|
WRAPROWS v8.6
|
|
EXPAND v7
|
ROWS v4
|
XLOOKUP v7
|
|
FILTER v7
|
SINGLE v7
|
XMATCH v7
|
|
GETPIVOTDATA v7.2
|
SORT v7
|
|
Math and trig
| Functions | ||
|---|---|---|
|
ABS v4
|
FACTDOUBLE v5.1
|
RANDBETWEEN v4
|
|
ACOS v4
|
FLOOR v4
|
ROMAN v5.1
|
|
ACOSH v4
|
FLOOR.MATH v5.1
|
ROUND v4
|
|
ACOT v5.1
|
FLOOR.PRECISE v5.1
|
ROUNDDOWN v4
|
|
ACOTH v5.1
|
GCD v5.1
|
ROUNDUP v4
|
|
ARABIC v7.0
|
INT v4
|
SEC v5.1
|
|
ASIN v5.1
|
ISO.CEILING v5.1
|
SECH v5.1
|
|
ASINH v4
|
LN v4
|
SEQUENCE v7
|
|
ATAN v4
|
LOG v4
|
SERIESSUM v5.1
|
|
ATAN2 v4
|
LOG10 v4
|
SIGN v4
|
|
ATANH v4
|
MDETERM v7.2
|
SIN v4
|
|
CEILING v4
|
MINVERSE v7.2
|
SINH v4
|
|
CEILING.MATH v5.1
|
MMULT v7.2
|
SQRT v4
|
|
CEILING.PRECISE v5.1
|
MOD v4
|
SQRTPI v4
|
|
COMBIN v5.1
|
MROUND v5.1
|
SUBTOTAL v4
|
|
COMBINA v5.1
|
MULTINOMIAL v5.5
|
SUM v4
|
|
COS v4
|
MUNIT v7.2
|
SUMIF v4
|
|
COSH v4
|
ODD v5.0
|
SUMIFS v4
|
|
COT v5.1
|
PERCENTOF v8.3
|
SUMPRODUCT v4
|
|
COTH v5.1
|
PI v4
|
SUMSQ v4
|
|
CSC v5.1
|
POWER v4
|
SUMX2MY2 v5.1
|
|
CSCH v5.1
|
PRODUCT v4
|
SUMX2PY2 v5.1
|
|
DEGREES v4
|
QUOTIENT v4
|
SUMXMY2 v5.1
|
|
EVEN v5.0
|
RADIANS v5.1
|
TAN v4
|
|
EXP v4
|
RAND v4
|
TANH v4
|
|
FACT v4
|
RANDARRAY v7
|
TRUNC v4
|
Statistical
| Functions | ||
|---|---|---|
|
AGGREGATE v5.5
|
FREQUENCY v7.0
|
PERMUT v5.5
|
|
AREAS v7.2.1
|
FTEST v7.0
|
PERMUTATIONA v5.5
|
|
AVEDEV v5.5
|
GAMMA v5.5
|
PHI v6.0
|
|
AVERAGE v4
|
GAMMA.DIST v7.0
|
POISSON v7.0
|
|
AVERAGEA v4
|
GAMMA.INV v7.2.1
|
POISSON.DIST v7.0
|
|
AVERAGEIF v4
|
GAMMADIST v7.0
|
PROB v7.0
|
|
AVERAGEIFS v4
|
GAMMAINV v7.2.1
|
QUARTILE v5.5
|
|
BETA.DIST v6.0
|
GAMMALN v5.5
|
QUARTILE.EXC v5.5
|
|
BETA.INV v6.0
|
GAMMALN.PRECISE v5.5
|
QUARTILE.INC v5.5
|
|
BETADIST v6.0
|
GAUSS v6.0
|
RANK v4
|
|
BETAINV v6.0
|
GEOMEAN v6.0
|
RANK.AVG v4
|
|
BINOM.DIST v7.0
|
GROWTH v7.3
|
RANK.EQ v4
|
|
BINOM.DIST.RANGE v7.0
|
HARMEAN v6.0
|
RSQ v6.0
|
|
BINOM.INV v7.0
|
INTERCEPT v6.0
|
SKEW v6.0
|
|
BINOMDIST v7.0
|
KURT v6.0
|
SKEW.P v6.0
|
|
CHIDIST v6.0
|
LARGE v4
|
SLOPE v7.0
|
|
CHIINV v6.0
|
LCM v5.1
|
SMALL v4
|
|
CHISQ.DIST v7.0
|
LINEST v7.2
|
STANDARDIZE v6.0
|
|
CHISQ.DIST.RT v6.0
|
LOGEST v7.2
|
STDEV v4
|
|
CHISQ.INV v6.0
|
LOGINV v7.2.1
|
STDEV.P v4
|
|
CHISQ.INV.RT v6.0
|
LOGNORM.DIST v7.0
|
STDEV.S v4
|
|
CHISQ.TEST v7.0
|
LOGNORM.INV v7.0
|
STDEVA v5.5
|
|
CHITEST v7.2.1
|
MAX v4
|
STDEVP v4
|
|
CONFIDENCE v5.5
|
MAXA v4
|
STDEVPA v5.5
|
|
CONFIDENCE.NORM v5.5
|
MEDIAN v4
|
STEYX v7.0
|
|
CONFIDENCE.T v5.5
|
MIN v4
|
T.DIST v7.2.1
|
|
CORREL v6.0
|
MINA v4
|
T.DIST.2T v7.0
|
|
COUNT v4
|
MODE v5.2
|
T.DIST.RT v7.0
|
|
COUNTA v4
|
MODE.MULT v7
|
T.INV v7.0
|
|
COUNTBLANK v4
|
MODE.SNGL v5.2
|
T.INV.2T v7.0
|
|
COUNTIF v4
|
NEGBINOM.DIST v7.0
|
T.TEST v7.0
|
|
COUNTIFS v4
|
NEGBINOMDIST v7.0
|
TDIST v7.0
|
|
COVAR v5.5
|
NORM.DIST v5.8
|
TREND v7.2
|
|
COVARIANCE.P v5.5
|
NORM.INV v5.8
|
TRIMMEAN v7.0
|
|
COVARIANCE.S v5.5
|
NORM.S.DIST v6.0
|
TTEST v7.0
|
|
DEVSQ v5.5
|
NORM.S.INV v5.8
|
VAR v4
|
|
EXPON.DIST v6.0
|
NORMDIST v5.8
|
VAR.P v5.2
|
|
EXPONDIST v6.0
|
NORMINV v5.8
|
VAR.S v5.2
|
|
F.DIST v7.0
|
NORMSDIST v6.0
|
VARA v5.5
|
|
F.INV v7.0
|
NORMSINV v5.8
|
VARP v4
|
|
F.INV.RT v7.0
|
PEARSON v6.0
|
VARPA v5.5
|
|
F.TEST v7.0
|
PERCENTILE v5.2
|
WEIBULL v7.0
|
|
FDIST v7.0
|
PERCENTILE.EXC v5.5
|
WEIBULL.DIST v7.0
|
|
FISHER v6.0
|
PERCENTILE.INC v5.2
|
Z.TEST v7.0
|
|
FISHERINV v6.0
|
PERCENTRANK v5.2
|
ZTEST v7.0
|
|
FORECAST v6.0
|
PERCENTRANK.EXC v5.5
|
|
|
FORECAST.LINEAR v6.0
|
PERCENTRANK.INC v5.2
|
|
Text
| Functions | ||
|---|---|---|
|
ARRAYTOTEXT v8
|
LEFT v4
|
TEXT v4
|
|
CHAR v4
|
LEN v4
|
TEXTAFTER v7.2
|
|
CLEAN v5.0
|
LOWER v4
|
TEXTBEFORE v7.2
|
|
CODE v8.6
|
MID v4
|
TEXTJOIN v5.2
|
|
CONCAT v5.0
|
NUMBERVALUE v5.0
|
TEXTSPLIT v7.2
|
|
CONCATENATE v4
|
PROPER v4
|
TRIM v4
|
|
DOLLAR v5.5
|
REPLACE v4
|
UNICHAR v5.0
|
|
ENCODEURL v8.6
|
REPT v4
|
UNICODE v5.0
|
|
EXACT v4
|
RIGHT v4
|
UPPER v4
|
|
FIND v4
|
SEARCH v4
|
USDOLLAR v8.6
|
|
FIXED v4
|
SUBSTITUTE v4
|
VALUE v4
|
|
HYPERLINK v4
|
T v4
|
VALUETOTEXT v8.3
|