EPPlus 5
Features and technical overview
Formula Calculation improvements
Improvements and new functions
- We have rewritten parts of the Tokenizer
- 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(...)"
Supported functions
Functions
Compared with EPPlus 4, there are 141 new functions implemented in EPPlus 5.5 - it supports in total 297 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 |
BITOR v5.1 |
ERF.PRECISE v5.2 |
BESSELJ v5.2 |
BITRSHIFT v5.1 |
ERFC v5.2 |
BESSELK v5.2 |
BITXOR v5.1 |
ERFC.PRECISE v5.2 |
BESSELY v5.2 |
CONVERT v5.1 |
HEX2BIN v5.1 |
BIN2DEC v5.1 |
DEC2BIN v5.1 |
HEX2DEC v5.1 |
BIN2HEX v5.1 |
DEC2HEX v5.1 |
HEX2OCT v5.1 |
BIN2OCT v5.1 |
DEC2OCT v5.1 |
OCT2BIN v5.1 |
BITAND v5.1 |
DELTA v5.1 |
OCT2DEC v5.1 |
BITLSHIFT v5.1 |
ERF v5.2 |
OCT2HEX v5.1 |
Financial
Functions | ||
---|---|---|
COUPDAYBS v5.2 |
DURATION v5.2 |
PDURATION v5.2 |
COUPDAYS v5.2 |
EFFECT v5.2 |
PMT v4 |
COUPDAYSNC v5.2 |
FV v5.2 |
PPMT v5.2 |
COUPNCD v5.2 |
FVSCHEDULE v5.2 |
PRICE v5.2 |
COUPNUM v5.2 |
INTRATE v5.5 |
PV v5.2 |
COUPPCD v5.2 |
IPMT v5.2 |
RATE v5.2 |
CUMIPMT v5.2 |
IRR v5.2 |
RRI v5.2 |
CUMPRINC v5.2 |
ISPMT v5.2 |
SLN v5.2 |
DB v5.5 |
MDURATION v5.5 |
SYD v5.2 |
DDB v5.2 |
MIRR v5.2 |
XIRR v5.2 |
DISC v5.2 |
NOMINAL v5.2 |
XNPV v5.2 |
DOLLARDE v5.5 |
NPER v5.2 |
YIELD v5.2 |
DOLLARFR v5.5 |
NPV v5.2 |
YIELDMAT v5.5 |
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 |
ROMAN v5.1 |
ACOS v4 |
FACTDOUBLE v5.1 |
ROUND v4 |
ACOSH v4 |
FLOOR v4 |
ROUNDDOWN v4 |
ACOT v5.1 |
FLOOR.MATH v5.1 |
ROUNDUP v4 |
ACOTH v5.1 |
FLOOR.PRECISE v5.1 |
SEC v5.1 |
ASIN v5.1 |
GCD v5.1 |
SECH v5.1 |
ASINH v4 |
INT v4 |
SERIESSUM v5.1 |
ATAN v4 |
ISO.CEILING v5.1 |
SIGN v4 |
ATAN2 v4 |
LN v4 |
SIN v4 |
ATANH v4 |
LOG v4 |
SINH v4 |
CEILING v4 |
LOG10 v4 |
SQRT v4 |
CEILING.MATH v5.1 |
MOD v4 |
SQRTPI v4 |
CEILING.PRECISE v5.1 |
MROUND v5.1 |
SUBTOTAL v4 |
COMBIN v5.1 |
MULTINOMIAL v5.5 |
SUM v4 |
COMBINA v5.1 |
ODD v5.0 |
SUMIF v4 |
COS v4 |
PERMUT v5.5 |
SUMIFS v4 |
COSH v4 |
PERMUTATIONA v5.5 |
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 |
RANDBETWEEN v4 |
TRUNC v4 |
Statistical
Functions | ||
---|---|---|
AGGREGATE v5.5 |
GAMMA v5.5 |
QUARTILE.EXC v5.5 |
AVEDEV v5.5 |
GAMMALN v5.5 |
QUARTILE.INC v5.5 |
AVERAGE v4 |
GAMMALN.PRECISE v5.5 |
RANK v4 |
AVERAGEA v4 |
LARGE v4 |
RANK.AVG v4 |
AVERAGEIF v4 |
LCM v5.1 |
RANK.EQ v4 |
AVERAGEIFS v4 |
MAX v4 |
SMALL v4 |
COMPLEX v5.5 |
MAXA v4 |
STDEV v4 |
CONFIDENCE v5.5 |
MEDIAN v4 |
STDEV.P v4 |
CONFIDENCE.NORM v5.5 |
MIN v4 |
STDEV.S v4 |
CONFIDENCE.T v5.5 |
MINA v4 |
STDEVA v5.5 |
COUNT v4 |
MODE v5.2 |
STDEVP v4 |
COUNTA v4 |
MODE.SNGL v5.2 |
STDEVPA v5.5 |
COUNTBLANK v4 |
PERCENTILE v5.2 |
VAR v4 |
COUNTIF v4 |
PERCENTILE.EXC v5.5 |
VAR.P v5.2 |
COUNTIFS v4 |
PERCENTILE.INC v5.2 |
VAR.S v5.2 |
COVAR v5.5 |
PERCENTRANK v5.2 |
VARA v5.5 |
COVARIANCE.P v5.5 |
PERCENTRANK.EXC v5.5 |
VARP v4 |
COVARIANCE.S v5.5 |
PERCENTRANK.INC v5.2 |
VARPA v5.5 |
DEVSQ v5.5 |
QUARTILE 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 |