Functions
ClosedXML can evaluate formula functions.
Note
Excel has a a list of functions that are defined in ECMA-376 and a newer
ones that are added in some subsequent version (future functions).
The future functions generally have a prefix _xlfn. The prefix is hidden
in the GUI, but is present in the file (e.g. _xlfn.CONCAT(A1:A2) is
displayed as a =CONCAT(A1:B1) in the Excel).
The cell formula that uses a future functions that were added in later version of Excel must use a correct name of a function, including the prefix.
ws.Cell(1,1).FormulaA1 = "_xlfn.CONCAT(A1:A2)";
Excel won’t recognize future functions without a prefix! It will try
to match the function, but won’t find anything and it will display
a #NAME? error.
See the list of future functions
Note
ClosedXML doesn’t calculate and save values of a formula cells by default. The saved cell contains only formula and when the file is opened in the Excel, it recalculates values of formulas.
You can save values by setting SaveOptions.EvaluateFormulasBeforeSaving
to true and passing the options to the XLWorkbook.SaveAs or
XLWorkbook.Save method.
Workbook without formula values can exhibit slighly odd behavior in some cases:
IXLCell.Style.Alignment.WrapTextdoesn’t correctly auto-size cell height when opened in Excel (#1833).
Standard functions
Category |
Function |
Implemented |
|---|---|---|
Cube |
CUBEKPIMEMBER |
No |
CUBEMEMBER |
No |
|
CUBEMEMBERPROPERTY |
No |
|
CUBERANKEDMEMBER |
No |
|
CUBESET |
No |
|
CUBESETCOUNT |
No |
|
CUBEVALUE |
No |
|
Database |
DAVERAGE |
No |
DCOUNT |
No |
|
DCOUNTA |
No |
|
DGET |
No |
|
DMAX |
No |
|
DMIN |
No |
|
DPRODUCT |
No |
|
DSTDEV |
No |
|
DSTDEVP |
No |
|
DSUM |
No |
|
DVAR |
No |
|
DVARP |
No |
|
Date and Time |
DATE |
Yes |
DATEDIF |
Yes |
|
DATEVALUE |
Yes |
|
DAY |
Yes |
|
DAYS360 |
Yes |
|
EDATE |
Yes |
|
EOMONTH |
Yes |
|
HOUR |
Yes |
|
MINUTE |
Yes |
|
MONTH |
Yes |
|
NETWORKDAYS |
Yes |
|
NETWORKDAYS.INTL |
No |
|
NOW |
Yes |
|
SECOND |
Yes |
|
TIME |
Yes |
|
TIMEVALUE |
Yes |
|
TODAY |
Yes |
|
WEEKDAY |
Yes |
|
WEEKNUM |
Yes |
|
WORKDAY |
Yes |
|
WORKDAY.INTL |
No |
|
YEAR |
Yes |
|
YEARFRAC |
Yes |
|
Engineering |
BESSELI |
No |
BESSELJ |
No |
|
BESSELK |
No |
|
BESSELY |
No |
|
BIN2DEC |
No |
|
BIN2HEX |
No |
|
BIN2OCT |
No |
|
COMPLEX |
No |
|
CONVERT |
No |
|
DEC2BIN |
No |
|
DEC2HEX |
No |
|
DEC2OCT |
No |
|
DELTA |
No |
|
ERF |
No |
|
ERFC |
No |
|
GESTEP |
No |
|
HEX2BIN |
No |
|
HEX2DEC |
No |
|
HEX2OCT |
No |
|
IMABS |
No |
|
IMAGINARY |
No |
|
IMARGUMENT |
No |
|
IMCONJUGATE |
No |
|
IMCOS |
No |
|
IMDIV |
No |
|
IMEXP |
No |
|
IMLN |
No |
|
IMLOG10 |
No |
|
IMLOG2 |
No |
|
IMPOWER |
No |
|
IMPRODUCT |
No |
|
IMREAL |
No |
|
IMSIN |
No |
|
IMSQRT |
No |
|
IMSUB |
No |
|
IMSUM |
No |
|
OCT2BIN |
No |
|
OCT2DEC |
No |
|
OCT2HEX |
No |
|
Financial |
ACCRINT |
No |
ACCRINTM |
No |
|
AMORDEGRC |
No |
|
AMORLINC |
No |
|
COUPDAYBS |
No |
|
COUPDAYS |
No |
|
COUPDAYSNC |
No |
|
COUPNCD |
No |
|
COUPNUM |
No |
|
COUPPCD |
No |
|
CUMIPMT |
No |
|
CUMPRINC |
No |
|
DB |
No |
|
DDB |
No |
|
DISC |
No |
|
DOLLARDE |
No |
|
DOLLARFR |
No |
|
DURATION |
No |
|
EFFECT |
No |
|
FV |
No |
|
FVSCHEDULE |
No |
|
INTRATE |
No |
|
IPMT |
No |
|
IRR |
No |
|
ISPMT |
No |
|
MDURATION |
No |
|
MIRR |
No |
|
NOMINAL |
No |
|
NPER |
No |
|
NPV |
No |
|
ODDFPRICE |
No |
|
ODDFYIELD |
No |
|
ODDLPRICE |
No |
|
ODDLYIELD |
No |
|
PMT |
Yes |
|
PPMT |
No |
|
PRICE |
No |
|
PRICEDISC |
No |
|
PRICEMAT |
No |
|
PV |
No |
|
RATE |
No |
|
RECEIVED |
No |
|
SLN |
No |
|
SYD |
No |
|
TBILLEQ |
No |
|
TBILLPRICE |
No |
|
TBILLYIELD |
No |
|
VDB |
No |
|
XIRR |
No |
|
XNPV |
No |
|
YIELD |
No |
|
YIELDDISC |
No |
|
YIELDMAT |
No |
|
Information |
CELL |
No |
ERROR.TYPE |
YES |
|
INFO |
No |
|
ISBLANK |
YES |
|
ISERR |
YES |
|
ISERROR |
YES |
|
ISEVEN |
YES |
|
ISLOGICAL |
YES |
|
ISNA |
YES |
|
ISNONTEXT |
YES |
|
ISNUMBER |
YES |
|
ISODD |
YES |
|
ISREF |
YES |
|
ISTEXT |
YES |
|
N |
YES |
|
NA |
YES |
|
TYPE |
YES |
|
Logical |
AND |
YES |
FALSE |
YES |
|
IF |
YES |
|
IFERROR |
YES |
|
NOT |
YES |
|
OR |
YES |
|
TRUE |
YES |
|
Lookup and Reference |
ADDRESS |
No |
AREAS |
No |
|
CHOOSE |
No |
|
COLUMN |
YES |
|
COLUMNS |
No |
|
GETPIVOTDATA |
No |
|
HLOOKUP |
YES |
|
HYPERLINK |
YES |
|
INDEX |
YES |
|
INDIRECT |
No |
|
LOOKUP |
No |
|
MATCH |
YES |
|
OFFSET |
No |
|
ROW |
YES |
|
ROWS |
No |
|
RTD |
No |
|
TRANSPOSE |
No |
|
VLOOKUP |
YES |
|
Math and Trig |
ABS |
YES |
ACOS |
YES |
|
ACOSH |
YES |
|
ASIN |
YES |
|
ASINH |
YES |
|
ATAN |
YES |
|
ATAN2 |
YES |
|
ATANH |
YES |
|
CEILING |
YES |
|
COMBIN |
YES |
|
COS |
YES |
|
COSH |
YES |
|
DEGREES |
YES |
|
ECMA.CEILING |
No |
|
EVEN |
YES |
|
EXP |
YES |
|
FACT |
YES |
|
FACTDOUBLE |
YES |
|
FLOOR |
YES |
|
GCD |
YES |
|
INT |
YES |
|
ISO.CEILING |
No |
|
LCM |
YES |
|
LN |
YES |
|
LOG |
YES |
|
LOG10 |
YES |
|
MDETERM |
YES |
|
MINVERSE |
YES |
|
MMULT |
YES |
|
MOD |
YES |
|
MROUND |
YES |
|
MULTINOMIAL |
YES |
|
ODD |
YES |
|
PI |
YES |
|
POWER |
YES |
|
PRODUCT |
YES |
|
QUOTIENT |
YES |
|
RADIANS |
YES |
|
RAND |
YES |
|
RANDBETWEEN |
YES |
|
ROMAN |
YES |
|
ROUND |
YES |
|
ROUNDDOWN |
YES |
|
ROUNDUP |
YES |
|
SERIESSUM |
YES |
|
SIGN |
YES |
|
SIN |
YES |
|
SINH |
YES |
|
SQRT |
YES |
|
SQRTPI |
YES |
|
SUBTOTAL |
YES |
|
SUM |
YES |
|
SUMIF |
YES |
|
SUMIFS |
YES |
|
SUMPRODUCT |
YES |
|
SUMSQ |
YES |
|
SUMX2MY2 |
No |
|
SUMX2PY2 |
No |
|
SUMXMY2 |
No |
|
TAN |
YES |
|
TANH |
YES |
|
TRUNC |
YES |
|
Statistical |
AVEDEV |
No |
AVERAGE |
YES |
|
AVERAGEA |
YES |
|
AVERAGEIF |
No |
|
AVERAGEIFS |
No |
|
BETADIST |
No |
|
BETAINV |
No |
|
BINOMDIST |
No |
|
CHIDIST |
No |
|
CHIINV |
No |
|
CHITEST |
No |
|
CONFIDENCE |
No |
|
CORREL |
No |
|
COUNT |
YES |
|
COUNTA |
YES |
|
COUNTBLANK |
YES |
|
COUNTIF |
YES |
|
COUNTIFS |
YES |
|
COVAR |
No |
|
CRITBINOM |
No |
|
DEVSQ |
YES |
|
EXPONDIST |
No |
|
FDIST |
No |
|
FINV |
No |
|
FISHER |
YES |
|
FISHERINV |
No |
|
FORECAST |
No |
|
FREQUENCY |
No |
|
FTEST |
No |
|
GAMMADIST |
No |
|
GAMMAINV |
No |
|
GAMMALN |
No |
|
GEOMEAN |
YES |
|
GROWTH |
No |
|
HARMEAN |
No |
|
HYPGEOMDIST |
No |
|
INTERCEPT |
No |
|
KURT |
No |
|
LARGE |
No |
|
LINEST |
No |
|
LOGEST |
No |
|
LOGINV |
No |
|
LOGNORMDIST |
No |
|
MAX |
YES |
|
MAXA |
YES |
|
MEDIAN |
YES |
|
MIN |
YES |
|
MINA |
YES |
|
MODE |
No |
|
NEGBINOMDIST |
No |
|
NORMDIST |
No |
|
NORMINV |
No |
|
NORMSDIST |
No |
|
NORMSINV |
No |
|
PEARSON |
No |
|
PERCENTILE |
No |
|
PERCENTRANK |
No |
|
PERMUT |
No |
|
POISSON |
No |
|
PROB |
No |
|
QUARTILE |
No |
|
RANK |
No |
|
RSQ |
No |
|
SKEW |
No |
|
SLOPE |
No |
|
SMALL |
No |
|
STANDARDIZE |
No |
|
STDEV |
YES |
|
STDEVA |
YES |
|
STDEVP |
YES |
|
STDEVPA |
YES |
|
STEYX |
No |
|
TDIST |
No |
|
TINV |
No |
|
TREND |
No |
|
TRIMMEAN |
No |
|
TTEST |
No |
|
VAR |
YES |
|
VARA |
YES |
|
VARP |
YES |
|
VARPA |
YES |
|
WEIBULL |
No |
|
ZTEST |
No |
|
Text and Data |
ASC |
YES |
BAHTTEXT |
No |
|
CHAR |
YES |
|
CLEAN |
YES |
|
CODE |
YES |
|
CONCATENATE |
YES |
|
DOLLAR |
YES |
|
EXACT |
YES |
|
FIND |
YES |
|
FINDB |
No |
|
FIXED |
YES |
|
JIS |
No |
|
LEFT |
YES |
|
LEFTB |
No |
|
LEN |
YES |
|
LENB |
No |
|
LOWER |
YES |
|
MID |
YES |
|
MIDB |
No |
|
PHONETIC |
No |
|
PROPER |
YES |
|
REPLACE |
YES |
|
REPLACEB |
No |
|
REPT |
YES |
|
RIGHT |
YES |
|
RIGHTB |
No |
|
SEARCH |
YES |
|
SEARCHB |
No |
|
SUBSTITUTE |
YES |
|
T |
YES |
|
TEXT |
YES |
|
TRIM |
YES |
|
UPPER |
YES |
|
VALUE |
YES |
Future functions
Category |
Function |
Implemented |
|---|---|---|
Date and Time |
_xlfn.ISOWEEKNUM |
Yes |
Math and Trig |
_xlfn.ACOT |
Yes |
_xlfn.ACOTH |
Yes |
|
_xlfn.ARABIC |
Yes |
|
_xlfn.BASE |
Yes |
|
_xlfn.CEILING.MATH |
Yes |
|
_xlfn.COMBINA |
Yes |
|
_xlfn.COT |
Yes |
|
_xlfn.COTH |
Yes |
|
_xlfn.CSC |
Yes |
|
_xlfn.CSCH |
Yes |
|
_xlfn.DECIMAL |
Yes |
|
_xlfn.FLOOR.MATH |
Yes |
|
_xlfn.SEC |
Yes |
|
_xlfn.SECH |
Yes |
|
Statistical |
_xlfn.STDEV.S |
Yes |
_xlfn.STDEV.P |
Yes |
|
Text and Data |
_xlfn.CONCAT |
Yes |
_xlfn.NUMBERVALUE |
Yes |
|
_xlfn.TEXTJOIN |
Yes |