EPPlus 5/6/7

Features and technical overview

Formula Calculation improvements

Improvements and new functions

  • Support for calculating dynamic array formulas and legacy array formulas. Using operators in combination with addresses/ranges. For example "A1:A3 + B1:B3 - 1" from EPPlus 7
  • Improved handling of addresses, support for the intersect operator and using functions in addresses from EPPlus 7
  • Support for implicit intersection in calculations from EPPlus 7
  • Improved handling of circular references
  • Reduced floating point errors in sums, averages, etc.
  • Added PrecisionAndRoundingStrategy enum parameter to the Calculate function. PrecisionAndRoundingStrategy.DotNet to preserve backward compatibility, PrecisionAndRoundingStrategy.Excel (default from EPPlus 7) mimics Excels rounding to 15 significant figures in the rounding functions.
  • Support for calculating external workbooks
  • Minor improvements and fixes - se our change log

Supported functions

Functions

Compared with EPPlus 4, there are 272 new functions implemented in EPPlus 7.0 - it supports in total 428 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
ERF
v5.2
IMEXP
v7.0
BESSELJ
v5.2
ERF.PRECISE
v5.2
IMLN
v7.0
BESSELK
v5.2
ERFC
v5.2
IMLOG10
v7.0
BESSELY
v5.2
ERFC.PRECISE
v5.2
IMLOG2
v7.0
BIN2DEC
v5.1
GESTEP
v7.0
IMPOWER
v7.0
BIN2HEX
v5.1
HEX2BIN
v5.1
IMPRODUCT
v7.0
BIN2OCT
v5.1
HEX2DEC
v5.1
IMREAL
v7.0
BITAND
v5.1
HEX2OCT
v5.1
IMSEC
v7.0
BITLSHIFT
v5.1
IMABS
v7.0
IMSECH
v7.0
BITOR
v5.1
IMAGINARY
v7.0
IMSIN
v7.0
BITRSHIFT
v5.1
IMARGUMENT
v7.0
IMSINH
v7.0
BITXOR
v5.1
IMCONJUGATE
v7.0
IMSQRT
v7.0
COMPLEX
v5.5
IMCOS
v7.0
IMSUB
v7.0
CONVERT
v5.1
IMCOSH
v7.0
IMSUM
v7.0
DEC2BIN
v5.1
IMCOT
v7.0
IMTAN
v7.0
DEC2HEX
v5.1
IMCSC
v7.0
OCT2BIN
v5.1
DEC2OCT
v5.1
IMCSCH
v7.0
OCT2DEC
v5.1
DELTA
v5.1
IMDIV
v7.0
OCT2HEX
v5.1

Financial

Functions
ACCRINT
v6.0
FVSCHEDULE
v5.2
PRICEDISC
v7.0
ACCRINTM
v6.0
INTRATE
v5.5
PRICEMAT
v7.0
COUPDAYBS
v5.2
IPMT
v5.2
PV
v5.2
COUPDAYS
v5.2
IRR
v5.2
RATE
v5.2
COUPDAYSNC
v5.2
ISPMT
v5.2
RECEIVED
v7.0
COUPNCD
v5.2
MDURATION
v5.5
RRI
v5.2
COUPNUM
v5.2
MIRR
v5.2
SLN
v5.2
COUPPCD
v5.2
NOMINAL
v5.2
SYD
v5.2
CUMIPMT
v5.2
NPER
v5.2
TBILLEQ
v6.0
CUMPRINC
v5.2
NPV
v5.2
TBILLPRICE
v6.0
DB
v5.5
ODDFPRICE
v7.0
TBILLYIELD
v6.0
DDB
v5.2
ODDFYIELD
v7.0
VDB
v7.0
DISC
v5.2
ODDLPRICE
v7
XIRR
v5.2
DOLLARDE
v5.5
ODDLYIELD
v7
XNPV
v5.2
DOLLARFR
v5.5
PDURATION
v5.2
YIELD
v5.2
DURATION
v5.2
PMT
v4
YIELDMAT
v5.5
EFFECT
v5.2
PPMT
v5.2
FV
v5.2
PRICE
v5.2

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
HSTACK
v7
TAKE
v7
ANCHORARRAY
v7
INDEX
v4
TOCOL
v7
CHOOSE
v4
INDIRECT
v4
TOROW
v7
CHOOSECOLS
v7
LOOKUP
v4
TRANSPOSE
v7
CHOOSEROWS
v7
MATCH
v4
UNIQUE
v7
COLUMN
v4
OFFSET
v4
VLOOKUP
v4
COLUMNS
v4
ROW
v4
VSTACK
v7
DROP
v7
ROWS
v4
XLOOKUP
v7
EXPAND
v7
SINGLE
v7
XMATCH
v7
FILTER
v7
SORT
v7
HLOOKUP
v4
SORTBY
v7

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
SEQUENCE
v7
ARABIC
v7.0
GCD
v5.1
SERIESSUM
v5.1
ASIN
v5.1
INT
v4
SIGN
v4
ASINH
v4
ISO.CEILING
v5.1
SIN
v4
ATAN
v4
LN
v4
SINH
v4
ATAN2
v4
LOG
v4
SQRT
v4
ATANH
v4
LOG10
v4
SQRTPI
v4
CEILING
v4
MOD
v4
SUBTOTAL
v4
CEILING.MATH
v5.1
MROUND
v5.1
SUM
v4
CEILING.PRECISE
v5.1
MULTINOMIAL
v5.5
SUMIF
v4
COMBIN
v5.1
ODD
v5.0
SUMIFS
v4
COMBINA
v5.1
PI
v4
SUMPRODUCT
v4
COS
v4
POWER
v4
SUMSQ
v4
COSH
v4
PRODUCT
v4
SUMX2MY2
v5.1
COT
v5.1
QUOTIENT
v4
SUMX2PY2
v5.1
COTH
v5.1
RADIANS
v5.1
SUMXMY2
v5.1
CSC
v5.1
RAND
v4
TAN
v4
CSCH
v5.1
RANDARRAY
v7
TANH
v4
DEGREES
v4
RANDBETWEEN
v4
TRUNC
v4
EVEN
v5.0
ROMAN
v5.1
EXP
v4
ROUND
v4

Statistical

Functions
AGGREGATE
v5.5
FORECAST
v6.0
PERMUT
v5.5
AVEDEV
v5.5
FORECAST.LINEAR
v6.0
PERMUTATIONA
v5.5
AVERAGE
v4
FREQUENCY
v7.0
PHI
v6.0
AVERAGEA
v4
FTEST
v7.0
POISSON
v7.0
AVERAGEIF
v4
GAMMA
v5.5
POISSON.DIST
v7.0
AVERAGEIFS
v4
GAMMA.DIST
v7.0
PROB
v7.0
BETA.DIST
v6.0
GAMMADIST
v7.0
QUARTILE
v5.5
BETA.INV
v6.0
GAMMALN
v5.5
QUARTILE.EXC
v5.5
BETADIST
v6.0
GAMMALN.PRECISE
v5.5
QUARTILE.INC
v5.5
BETAINV
v6.0
GAUSS
v6.0
RANK
v4
BINOM.DIST
v7.0
GEOMEAN
v6.0
RANK.AVG
v4
BINOM.DIST.RANGE
v7.0
HARMEAN
v6.0
RANK.EQ
v4
BINOM.INV
v7.0
INTERCEPT
v6.0
RSQ
v6.0
BINOMDIST
v7.0
KURT
v6.0
SKEW
v6.0
CHIDIST
v6.0
LARGE
v4
SKEW.P
v6.0
CHIINV
v6.0
LCM
v5.1
SLOPE
v7.0
CHISQ.DIST
v7.0
LOGNORM.DIST
v7.0
SMALL
v4
CHISQ.DIST.RT
v6.0
LOGNORM.INV
v7.0
STANDARDIZE
v6.0
CHISQ.INV
v6.0
MAX
v4
STDEV
v4
CHISQ.INV.RT
v6.0
MAXA
v4
STDEV.P
v4
CHISQ.TEST
v7.0
MEDIAN
v4
STDEV.S
v4
CONFIDENCE
v5.5
MIN
v4
STDEVA
v5.5
CONFIDENCE.NORM
v5.5
MINA
v4
STDEVP
v4
CONFIDENCE.T
v5.5
MODE
v5.2
STDEVPA
v5.5
CORREL
v6.0
MODE.MULT
v7
STEYX
v7.0
COUNT
v4
MODE.SNGL
v5.2
T.DIST
v7.0
COUNTA
v4
NEGBINOM.DIST
v7.0
T.DIST.2T
v7.0
COUNTBLANK
v4
NEGBINOMDIST
v7.0
T.DIST.RT
v7.0
COUNTIF
v4
NORM.DIST
v5.8
T.INV
v7.0
COUNTIFS
v4
NORM.INV
v5.8
T.INV.2T
v7.0
COVAR
v5.5
NORM.S.DIST
v6.0
T.TEST
v7.0
COVARIANCE.P
v5.5
NORM.S.INV
v5.8
TRIMMEAN
v7.0
COVARIANCE.S
v5.5
NORMDIST
v5.8
VAR
v4
DEVSQ
v5.5
NORMINV
v5.8
VAR.P
v5.2
EXPON.DIST
v6.0
NORMSDIST
v6.0
VAR.S
v5.2
EXPONDIST
v6.0
NORMSINV
v5.8
VARA
v5.5
F.DIST
v7.0
PEARSON
v6.0
VARP
v4
F.INV
v7.0
PERCENTILE
v5.2
VARPA
v5.5
F.INV.RT
v7.0
PERCENTILE.EXC
v5.5
WEIBULL
v7.0
F.TEST
v7.0
PERCENTILE.INC
v5.2
WEIBULL.DIST
v7.0
FDIST
v7.0
PERCENTRANK
v5.2
Z.TEST
v7.0
FISHER
v6.0
PERCENTRANK.EXC
v5.5
ZTEST
v7.0
FISHERINV
v6.0
PERCENTRANK.INC
v5.2

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