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 |
|