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