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