EPPlus 5/6
Features and technical overview
Formula Calculation improvements
Improvements and new functions
- Improved handling of circular references
- Bugs in the rounding functions of previous versions of the library have been fixed.
- Added PrecisionAndRoundingStrategy enum parameter to the Calculate function. PrecisionAndRoundingStrategy.DotNet (default) to preserve backward compatibility, PrecisionAndRoundingStrategy.Excel mimics Excels rounding to 15 significant figures in the rounding functions.
- Support for using the OFFSET function in ranges, such as: "A1:OFFSET(...)", "OFFSET(...):A1" or "OFFSET(...):OFFSET(...)"
- Support for calculating external workbooks
- Minor improvements and fixes - se our change log
Supported functions
Functions
Compared with EPPlus 4, there are 181 new functions implemented in EPPlus 6.0 - it supports in total 337 functions (see below).
Learn more about the formula calculation capabilities of EPPlus in our developer wiki
Database
Functions | ||
---|---|---|
DAVERAGE v4 |
DGET v4 |
DSUM v4 |
DCOUNT v4 |
DMAX v4 |
DVAR v4 |
DCOUNTA v4 |
DMIN v4 |
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 |
BITRSHIFT v5.1 |
ERFC v5.2 |
BESSELJ v5.2 |
BITXOR v5.1 |
ERFC.PRECISE v5.2 |
BESSELK v5.2 |
COMPLEX v5.5 |
HEX2BIN v5.1 |
BESSELY v5.2 |
CONVERT v5.1 |
HEX2DEC v5.1 |
BIN2DEC v5.1 |
DEC2BIN v5.1 |
HEX2OCT v5.1 |
BIN2HEX v5.1 |
DEC2HEX v5.1 |
OCT2BIN v5.1 |
BIN2OCT v5.1 |
DEC2OCT v5.1 |
OCT2DEC v5.1 |
BITAND v5.1 |
DELTA v5.1 |
OCT2HEX v5.1 |
BITLSHIFT v5.1 |
ERF v5.2 |
|
BITOR v5.1 |
ERF.PRECISE v5.2 |
Financial
Functions | ||
---|---|---|
ACCRINT v6.0 |
DURATION v5.2 |
PPMT v5.2 |
ACCRINTM v6.0 |
EFFECT v5.2 |
PRICE v5.2 |
COUPDAYBS v5.2 |
FV v5.2 |
PV v5.2 |
COUPDAYS v5.2 |
FVSCHEDULE v5.2 |
RATE v5.2 |
COUPDAYSNC v5.2 |
INTRATE v5.5 |
RRI v5.2 |
COUPNCD v5.2 |
IPMT v5.2 |
SLN v5.2 |
COUPNUM v5.2 |
IRR v5.2 |
SYD v5.2 |
COUPPCD v5.2 |
ISPMT v5.2 |
TBILLEQ v6.0 |
CUMIPMT v5.2 |
MDURATION v5.5 |
TBILLPRICE v6.0 |
CUMPRINC v5.2 |
MIRR v5.2 |
TBILLYIELD v6.0 |
DB v5.5 |
NOMINAL v5.2 |
XIRR v5.2 |
DDB v5.2 |
NPER v5.2 |
XNPV v5.2 |
DISC v5.2 |
NPV v5.2 |
YIELD v5.2 |
DOLLARDE v5.5 |
PDURATION v5.2 |
YIELDMAT v5.5 |
DOLLARFR v5.5 |
PMT v4 |
Information
Functions | ||
---|---|---|
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 |
IFS v5.0 |
SWITCH v5.0 |
FALSE v4 |
MAXIFS v5.3 |
TRUE v4 |
IF v4 |
MINIFS v5.3 |
XOR v5.5 |
IFERROR v4 |
NOT v4 |
|
IFNA v4 |
OR v4 |
Lookup and reference
Functions | ||
---|---|---|
ADDRESS v4 |
INDEX v4 |
ROW v4 |
CHOOSE v4 |
INDIRECT v4 |
ROWS v4 |
COLUMN v4 |
LOOKUP v4 |
VLOOKUP v4 |
COLUMNS v4 |
MATCH v4 |
|
HLOOKUP v4 |
OFFSET v4 |
Math and trig
Functions | ||
---|---|---|
ABS v4 |
FACT v4 |
ROUNDDOWN v4 |
ACOS v4 |
FACTDOUBLE v5.1 |
ROUNDUP v4 |
ACOSH v4 |
FLOOR v4 |
SEC v5.1 |
ACOT v5.1 |
FLOOR.MATH v5.1 |
SECH v5.1 |
ACOTH v5.1 |
FLOOR.PRECISE v5.1 |
SERIESSUM v5.1 |
ASIN v5.1 |
GCD v5.1 |
SIGN v4 |
ASINH v4 |
INT v4 |
SIN v4 |
ATAN v4 |
ISO.CEILING v5.1 |
SINH v4 |
ATAN2 v4 |
LN v4 |
SQRT v4 |
ATANH v4 |
LOG v4 |
SQRTPI v4 |
CEILING v4 |
LOG10 v4 |
SUBTOTAL v4 |
CEILING.MATH v5.1 |
MOD v4 |
SUM v4 |
CEILING.PRECISE v5.1 |
MROUND v5.1 |
SUMIF v4 |
COMBIN v5.1 |
MULTINOMIAL v5.5 |
SUMIFS v4 |
COMBINA v5.1 |
ODD v5.0 |
SUMPRODUCT v4 |
COS v4 |
PI v4 |
SUMSQ v4 |
COSH v4 |
POWER v4 |
SUMX2MY2 v5.1 |
COT v5.1 |
PRODUCT v4 |
SUMX2PY2 v5.1 |
COTH v5.1 |
QUOTIENT v4 |
SUMXMY2 v5.1 |
CSC v5.1 |
RADIANS v5.1 |
TAN v4 |
CSCH v5.1 |
RAND v4 |
TANH v4 |
DEGREES v4 |
RANDBETWEEN v4 |
TRUNC v4 |
EVEN v5.0 |
ROMAN v5.1 |
|
EXP v4 |
ROUND v4 |
Statistical
Functions | ||
---|---|---|
AGGREGATE v5.5 |
FISHERINV v6.0 |
PERCENTILE.INC v5.2 |
AVEDEV v5.5 |
FORECAST v6.0 |
PERCENTRANK v5.2 |
AVERAGE v4 |
FORECAST.LINEAR v6.0 |
PERCENTRANK.EXC v5.5 |
AVERAGEA v4 |
GAMMA v5.5 |
PERCENTRANK.INC v5.2 |
AVERAGEIF v4 |
GAMMALN v5.5 |
PERMUT v5.5 |
AVERAGEIFS v4 |
GAMMALN.PRECISE v5.5 |
PERMUTATIONA v5.5 |
BETA.DIST v6.0 |
GAUSS v6.0 |
PHI v6.0 |
BETA.INV v6.0 |
GEOMEAN v6.0 |
QUARTILE v5.5 |
BETADIST v6.0 |
HARMEAN v6.0 |
QUARTILE.EXC v5.5 |
BETAINV v6.0 |
INTERCEPT v6.0 |
QUARTILE.INC v5.5 |
CHIDIST v6.0 |
KURT v6.0 |
RANK v4 |
CHIINV v6.0 |
LARGE v4 |
RANK.AVG v4 |
CHISQ.DIST.RT v6.0 |
LCM v5.1 |
RANK.EQ v4 |
CHISQ.INV v6.0 |
MAX v4 |
RSQ v6.0 |
CHISQ.INV.RT v6.0 |
MAXA v4 |
SKEW v6.0 |
CONFIDENCE v5.5 |
MEDIAN v4 |
SKEW.P v6.0 |
CONFIDENCE.NORM v5.5 |
MIN v4 |
SMALL v4 |
CONFIDENCE.T v5.5 |
MINA v4 |
STANDARDIZE v6.0 |
CORREL v6.0 |
MODE v5.2 |
STDEV v4 |
COUNT v4 |
MODE.SNGL v5.2 |
STDEV.P v4 |
COUNTA v4 |
NORM.DIST v5.8 |
STDEV.S v4 |
COUNTBLANK v4 |
NORM.INV v5.8 |
STDEVA v5.5 |
COUNTIF v4 |
NORM.S.DIST v6.0 |
STDEVP v4 |
COUNTIFS v4 |
NORM.S.INV v5.8 |
STDEVPA v5.5 |
COVAR v5.5 |
NORMDIST v5.8 |
VAR v4 |
COVARIANCE.P v5.5 |
NORMINV v5.8 |
VAR.P v5.2 |
COVARIANCE.S v5.5 |
NORMSDIST v6.0 |
VAR.S v5.2 |
DEVSQ v5.5 |
NORMSINV v5.8 |
VARA v5.5 |
EXPON.DIST v6.0 |
PEARSON v6.0 |
VARP v4 |
EXPONDIST v6.0 |
PERCENTILE v5.2 |
VARPA v5.5 |
FISHER v6.0 |
PERCENTILE.EXC v5.5 |
Text
Functions | ||
---|---|---|
CHAR v4 |
LEN v4 |
T v4 |
CLEAN v5.0 |
LOWER v4 |
TEXT v4 |
CONCAT v5.0 |
MID v4 |
TEXTJOIN v5.2 |
CONCATENATE v4 |
NUMBERVALUE v5.0 |
TRIM v4 |
DOLLAR v5.5 |
PROPER v4 |
UNICHAR v5.0 |
EXACT v4 |
REPLACE v4 |
UNICODE v5.0 |
FIND v4 |
REPT v4 |
UPPER v4 |
FIXED v4 |
RIGHT v4 |
VALUE v4 |
HYPERLINK v4 |
SEARCH v4 |
|
LEFT v4 |
SUBSTITUTE v4 |