EPPlus 5/6/7
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
- 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 - se our change log
Supported functions
Functions
Compared with EPPlus 4, there are 296 new functions implemented in EPPlus 7.3 - it supports in total 452 functions (see below).
Learn more about the formula calculation capabilities of EPPlus in our developer wiki
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 | ||
|---|---|---|
|
ERROR.TYPE v4 |
ISNA v4 |
N v4 |
|
ISBLANK v4 |
ISNONTEXT v4 |
NA v4 |
|
ISERR v4 |
ISNUMBER v4 |
SHEET v5.5 |
|
ISERROR v4 |
ISODD v4 |
TYPE v4 |
|
ISEVEN v4 |
ISREF v7 |
|
|
ISLOGICAL v4 |
ISTEXT v4 |
|
Logical
| Functions | ||
|---|---|---|
|
AND v4 |
IFS v5.0 |
OR v4 |
|
FALSE v4 |
LET v7.2 |
SWITCH v5.0 |
|
IF v4 |
MAXIFS v5.3 |
TRUE v4 |
|
IFERROR v4 |
MINIFS v5.3 |
XOR v5.5 |
|
IFNA v4 |
NOT v4 |
|
Lookup and reference
| Functions | ||
|---|---|---|
|
ADDRESS v4 |
HLOOKUP v4 |
SORTBY v7 |
|
ANCHORARRAY v7 |
HSTACK v7 |
TAKE v7 |
|
CHOOSE v4 |
INDEX v4 |
TOCOL v7 |
|
CHOOSECOLS v7 |
INDIRECT v4 |
TOROW v7 |
|
CHOOSEROWS v7 |
LOOKUP v4 |
TRANSPOSE v7 |
|
COLUMN v4 |
MATCH v4 |
UNIQUE v7 |
|
COLUMNS v4 |
OFFSET v4 |
VLOOKUP v4 |
|
DROP v7 |
ROW v4 |
VSTACK v7 |
|
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 |
ROMAN v5.1 |
|
ACOS v4 |
FLOOR v4 |
ROUND v4 |
|
ACOSH v4 |
FLOOR.MATH v5.1 |
ROUNDDOWN v4 |
|
ACOT v5.1 |
FLOOR.PRECISE v5.1 |
ROUNDUP v4 |
|
ACOTH v5.1 |
GCD v5.1 |
SEC v5.1 |
|
ARABIC v7.0 |
INT v4 |
SECH v5.1 |
|
ASIN v5.1 |
ISO.CEILING v5.1 |
SEQUENCE v7 |
|
ASINH v4 |
LN v4 |
SERIESSUM v5.1 |
|
ATAN v4 |
LOG v4 |
SIGN v4 |
|
ATAN2 v4 |
LOG10 v4 |
SIN v4 |
|
ATANH v4 |
MDETERM v7.2 |
SINH v4 |
|
CEILING v4 |
MINVERSE v7.2 |
SQRT v4 |
|
CEILING.MATH v5.1 |
MMULT v7.2 |
SQRTPI v4 |
|
CEILING.PRECISE v5.1 |
MOD v4 |
SUBTOTAL v4 |
|
COMBIN v5.1 |
MROUND v5.1 |
SUM v4 |
|
COMBINA v5.1 |
MULTINOMIAL v5.5 |
SUMIF v4 |
|
COS v4 |
MUNIT v7.2 |
SUMIFS v4 |
|
COSH v4 |
ODD v5.0 |
SUMPRODUCT v4 |
|
COT v5.1 |
PI v4 |
SUMSQ v4 |
|
COTH v5.1 |
POWER v4 |
SUMX2MY2 v5.1 |
|
CSC v5.1 |
PRODUCT v4 |
SUMX2PY2 v5.1 |
|
CSCH v5.1 |
QUOTIENT v4 |
SUMXMY2 v5.1 |
|
DEGREES v4 |
RADIANS v5.1 |
TAN v4 |
|
EVEN v5.0 |
RAND v4 |
TANH v4 |
|
EXP v4 |
RANDARRAY v7 |
TRUNC v4 |
|
FACT v4 |
RANDBETWEEN v4 |
|
Statistical
| Functions | ||
|---|---|---|
|
AGGREGATE v5.5 |
FREQUENCY v7.0 |
PERCENTRANK.INC v5.2 |
|
AVEDEV v5.5 |
FTEST v7.0 |
PERMUT v5.5 |
|
AVERAGE v4 |
GAMMA v5.5 |
PERMUTATIONA v5.5 |
|
AVERAGEA v4 |
GAMMA.DIST v7.0 |
PHI v6.0 |
|
AVERAGEIF v4 |
GAMMA.INV v7.2.1 |
POISSON v7.0 |
|
AVERAGEIFS v4 |
GAMMADIST v7.0 |
POISSON.DIST v7.0 |
|
BETA.DIST v6.0 |
GAMMAINV v7.2.1 |
PROB v7.0 |
|
BETA.INV v6.0 |
GAMMALN v5.5 |
QUARTILE v5.5 |
|
BETADIST v6.0 |
GAMMALN.PRECISE v5.5 |
QUARTILE.EXC v5.5 |
|
BETAINV v6.0 |
GAUSS v6.0 |
QUARTILE.INC v5.5 |
|
BINOM.DIST v7.0 |
GEOMEAN v6.0 |
RANK v4 |
|
BINOM.DIST.RANGE v7.0 |
GROWTH v7.3 |
RANK.AVG v4 |
|
BINOM.INV v7.0 |
HARMEAN v6.0 |
RANK.EQ v4 |
|
BINOMDIST v7.0 |
INTERCEPT v6.0 |
RSQ v6.0 |
|
CHIDIST v6.0 |
KURT v6.0 |
SKEW v6.0 |
|
CHIINV v6.0 |
LARGE v4 |
SKEW.P v6.0 |
|
CHISQ.DIST v7.0 |
LCM v5.1 |
SLOPE v7.0 |
|
CHISQ.DIST.RT v6.0 |
LINEST v7.2 |
SMALL v4 |
|
CHISQ.INV v6.0 |
LOGEST v7.2 |
STANDARDIZE v6.0 |
|
CHISQ.INV.RT v6.0 |
LOGINV v7.2.1 |
STDEV v4 |
|
CHISQ.TEST v7.0 |
LOGNORM.DIST v7.0 |
STDEV.P v4 |
|
CHITEST v7.2.1 |
LOGNORM.INV v7.0 |
STDEV.S v4 |
|
CONFIDENCE v5.5 |
MAX v4 |
STDEVA v5.5 |
|
CONFIDENCE.NORM v5.5 |
MAXA v4 |
STDEVP v4 |
|
CONFIDENCE.T v5.5 |
MEDIAN v4 |
STDEVPA v5.5 |
|
CORREL v6.0 |
MIN v4 |
STEYX v7.0 |
|
COUNT v4 |
MINA v4 |
T.DIST v7.0 |
|
COUNTA v4 |
MODE v5.2 |
T.DIST.2T v7.0 |
|
COUNTBLANK v4 |
MODE.MULT v7 |
T.DIST.RT v7.0 |
|
COUNTIF v4 |
MODE.SNGL v5.2 |
T.INV v7.0 |
|
COUNTIFS v4 |
NEGBINOM.DIST v7.0 |
T.INV.2T v7.0 |
|
COVAR v5.5 |
NEGBINOMDIST v7.0 |
T.TEST v7.0 |
|
COVARIANCE.P v5.5 |
NORM.DIST v5.8 |
TDIST v7.2.1 |
|
COVARIANCE.S v5.5 |
NORM.INV v5.8 |
TREND v7.2 |
|
DEVSQ v5.5 |
NORM.S.DIST v6.0 |
TRIMMEAN v7.0 |
|
EXPON.DIST v6.0 |
NORM.S.INV v5.8 |
TTEST v7.0 |
|
EXPONDIST v6.0 |
NORMDIST v5.8 |
VAR v4 |
|
F.DIST v7.0 |
NORMINV v5.8 |
VAR.P v5.2 |
|
F.INV v7.0 |
NORMSDIST v6.0 |
VAR.S v5.2 |
|
F.INV.RT v7.0 |
NORMSINV v5.8 |
VARA v5.5 |
|
F.TEST v7.0 |
PEARSON v6.0 |
VARP v4 |
|
FDIST v7.0 |
PERCENTILE v5.2 |
VARPA v5.5 |
|
FISHER v6.0 |
PERCENTILE.EXC v5.5 |
WEIBULL v7.0 |
|
FISHERINV v6.0 |
PERCENTILE.INC v5.2 |
WEIBULL.DIST v7.0 |
|
FORECAST v6.0 |
PERCENTRANK v5.2 |
Z.TEST v7.0 |
|
FORECAST.LINEAR v6.0 |
PERCENTRANK.EXC v5.5 |
ZTEST v7.0 |
Text
| Functions | ||
|---|---|---|
|
CHAR v4 |
LOWER v4 |
TEXTAFTER v7.2 |
|
CLEAN v5.0 |
MID v4 |
TEXTBEFORE v7.2 |
|
CONCAT v5.0 |
NUMBERVALUE v5.0 |
TEXTJOIN v5.2 |
|
CONCATENATE v4 |
PROPER v4 |
TEXTSPLIT v7.2 |
|
DOLLAR v5.5 |
REPLACE v4 |
TRIM v4 |
|
EXACT v4 |
REPT v4 |
UNICHAR v5.0 |
|
FIND v4 |
RIGHT v4 |
UNICODE v5.0 |
|
FIXED v4 |
SEARCH v4 |
UPPER v4 |
|
HYPERLINK v4 |
SUBSTITUTE v4 |
VALUE v4 |
|
LEFT v4 |
T v4 |
|
|
LEN v4 |
TEXT v4 |
|