Supported Features in Excel

In this topic, you can find both supported and unsupported features in Excel for Basic Engine Robots.

Formula Support

See the Apache POI website for details on supported formula.

Features

Supported

  • References: single cell & area, 2D & 3D, relative & absolute

  • Literals: Number, text, boolean, error and array

  • Operators: arithmetic and logical, some region operators

  • Built-in functions: over 350 recognized, 280 evaluatable

  • Add-in functions: 3 from Analysis Toolpack

  • Font color using the format string such as [red]

  • Conditional font color, such as in the example where negative numbers are red: #.##0;[Red]-#.##0

  • Date formatting

Unsupported

  • Manipulating array/table formulas (In Excel, formulas that look like "{=...}" as opposed to "=...")

  • Region operators: union, intersection

  • Parsing of previously uncalled add-in functions

  • Preservation of whitespace in formulas (when POI manipulates them)

  • Font changes, for example bold, size, etc.

  • Background color in cell

  • External file references from formulas

  • Excluding hidden values

  • Converting strings to a date with the FLOOR, HOUR, and MINUTE functions. Only decimal values are supported.

  • SUBTOTAL function

Function in POI

Supported Functions

See the Apache POI website for details on supported functions and function limitations.

ABS, ACOS, ACOSH, ADDRESS, AND, AREAS, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, BIN2DEC, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, COMPLEX, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DAY, DAYS360, DEC2BIN, DEC2HEX, DEGREES, DELTA, DEVSQ, DGET, DMAX, DMIN, DOLLAR, DSUM, EOMONTH, EDATE, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FACTDOUBLE, FALSE, FIND, FIXED, FLOOR, FREQUENCY, FV, HEX2DEC, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IMAGINARY, IMREAL, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MDETERM, MID, MIN, MINA, MINUTE, MINVERSE, MIRR, MOD, MODE, MONTH, MROUND, MMULT, NA, NETWORKDAYS, NOT, NOW, NPER, NPV, OCT2DEC, ODD, OFFSET, OR, PERCENTILE, PI, PMT, POISSON, POWER, PPMT, PRODUCT, PROPER, PV, QUOTIENT, RADIANS, RAND, RANDBETWEEN, RANK, RATE, REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH, TEXT, TIME, TODAY, TRANSPOSE, TREND, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, WEEKDAY, WEEKNUM, WORKDAY, YEAR, YEARFRAC

Unsupported Functions

ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, ASC, AVERAGEA, AVERAGEIF, AVERAGEIFS, BAHTTEXT, BESSELI, BESSELJ, BESSELK, BESSELY, BETADIST, BETAINV, BIN2HEX, BIN2OCT, BINOMDIST, CELL, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CONVERT, CORREL, COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, COVAR, CRITBINOM, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE, CUMIPMT, CUMPRINC, DATEDIF, DATESTRING, DATEVALUE, DAVERAGE, DB, DBCS, DCOUNT, DCOUNTA, DDB, DEC2OCT, DISC, DOLLARDE, DOLLARFR, DPRODUCT, DSTDEV, DSTDEVP, DURATION, DVAR, DVARP, EFFECT, ERF, ERFC, EXPONDIST, FDIST, FINDB, FINV, FISHER, FISHERINV, FORECAST, FORMULATEXT, FTEST, FVSCHEDULE, GAMMADIST, GAMMAINV, GAMMALN, GCD, GEOMEAN, GESTEP, GETPIVOTDATA, GROWTH, HARMEAN, HEX2BIN, HEX2OCT, HYPGEOMDIST, IFNA, IMABS, IMARGUMENT, IMCONJUGATE, IMCOS, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMSIN, IMSQRT, IMSUB, IMSUM, INFO, INTRATE, ISPMT, JIS, KURT, LCM, LEFTB, LENB, LINEST, LOGEST, LOGINV, LOGNORMDIST, MDURATION, MIDB, MMULT, MULTINOMIAL, N, NEGBINOMDIST, NOMINAL, NORMDIST, NORMINV, NORMSDIST, NORMSINV, NUMBERSTRING, OCT2BIN, OCT2HEX, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PEARSON, PERCENTRANK, PERMUT, PHONETIC, PRICE, PRICEDISC, PRICEMAT, PROB, QUARTILE, RECEIVED, REPLACEB, RIGHTB, RSQ, RTD, SEARCHB, SEQUENCE, SERIESSUM, SKEW, SLN, SQRTPI, STANDARDIZE, STDEVA, STDEVP, STDEVPA, STEYX, SUBTOTAL, SYD, TBILLEQ, TBILLPRICE, TBILLYIELD, TDIST, TIMEVALUE, TINV, TRIMMEAN, TTEST, TYPE, USDOLLAR, VARA, VARPA, VDB, WEIBULL, XIRR, XNPV, YIELD, YIELDDISC, YIELDMAT, ZTEST

Also, it is not supported to create spilled formulas in Excel in Design Studio. However, Design Studio supports handling of Excel documents with spilled formulas; you can open, edit, and save such documents.