Improved support for Excel formula functions

[gunnsth]

Description

The coverage of Excel formulas needs improving. Below is an overview of functions that are missing. We will create further tickets to implement those in stages.

Currently the following functions have been reported as not working:

ACCRINT
ACCRINTM
ADDRESS
AGGREGATE
AMORDEGRC
AMORLINC
ARABIC
AREAS
ASC
AVEDEV
AVERAGEIF
AVERAGEIFS

B
BAHTTEXT
BESSELI
BESSELJ
BESSELK
BESSELY
BETA.DIST
BETA.INV
BETADIST
BETAINV
BIN2DEC
BIN2HEX
BIN2OCT
BINOM.DIST
BINOM.INV
BINOMDIST
BITAND
BITLSHIFT
BITXOR

CELL
CHIDIST
CHIINV
CHISQ.DIST
CHISQ.DIST.RT
CHISQ.INV
CHISQ.INV.RT
CHISQ.TEST
CHISQDIST
CHISQINV
CHITEST
CHOOSE
CLEAN
COLOR (LibreOffice)
COLUMN
COLUMNS
COMPLEX
CONCAT
CONFIDENCE
CONFIDENCE.NORM
CONFIDENCE.T
CONVERT
CORREL
COUNTIF
COUNTIFS
COUPDAYBS
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
COVAR
COVARIANCE.P
COVARIANCE.S
CRITBINOM
CUMIPMT
CUMIPMT_ADD
CUMPRINC
CUMPRINC_ADD

DATE
DATEDIF
DATEVALUE
DAY
DAYS
DAYSINMONTH
DDB
DEC2BIN
DEC2HEX
DEC2OCT
DELTA
DEVSQ
DISC
DOLLAR
DOLLARDE
DOLLARFR

EASTERSUNDAY
EDATE
EFFECT
EFFECT_ADD
ENCODEURL
EOMONTH
ERF
ERF.PRECISE
ERFC
ERFC.PRECISE
ERROR.TYPE
ERRORTYPE
EUROCONVERT
EXPON.DIST
EXPONDIST

F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST
FDIST
FIND
FINDB
FINV
FISHER
FISHERINV
FIXED
FORECAST
FORECAST.LINEAR
FORMULA
FREQUENCY
FTEST
FV
FVSCHEDULE

GAMMA
GAMMA.DIST
GAMMA.INV
GAMMADIST
GAMMAINV
GAMMALN
GAMMALN.PRECISE
GAUSS
GEOMEAN
GESTEP
GROWTH

HARMEAN
HEX2BIN
HEX2DEC
HLOOKUP
HOUR
HYPERGEOM.DIST
HYPERGEOMDIST
HYPERLINK

IFS
IMABS
IMAGINARY
IMARGUMENT
IMCONJUGATE
IMCOS
IMCOSH
IMCOT
IMCSC
IMCSCH
IMDIV
IMEXP
IMLN
IMLOG2
IMLOG10
IMPOWER
IMPRODUCT
IMREAL
IMSEC
IMSIN
IMSINH
IMSQRT
IMSUB
IMSUM
IMTAN
INDEX
INDIRECT
INFO
INTERCEPT
INTRATE
IPMT
IRR
ISBLANK
ISERR
ISERROR
ISEVEN
ISEVEN_ADD
ISFORMULA
ISLEAPYEAR
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISODD
ISODD_ADD
ISREF
ISTEXT

JIS

KURT

LARGE
LINEST
LOGEST
LOGINV
LOGNORM.DIST
LOGNORM.INV
LOGNORMDIST
LOOKUP
LOWER

MATCH
MAXA
MAXIFS
MDURATION
MID
MIDB
MINA
MINIFS
MINUTE
MINVERSE
MIRR
MMULT
MODE
MODE.MULT
MODE.SNGL
MONTH
MONTHS

N
NEGBINOM.DIST
NEGBINOMDIST
NETWORKDAYS
NETWORKDAYS_EXCEL2003
NETWORKDAYS.INTL
NOMINAL
NOMINAL_ADD
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
NORMDIST
NORMINV
NORMSDIST
NORMSINV
NOW
NPER
NPV
NUMBERVALUE

OCT2BIN
OCT2DEC
OCT2HEX
OFFSET
OPT_BARRIER
OPT_PROB_HIT
OPT_PROB_INMONEY
OPT_TOUCH

PDURATION
PEARSON
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK
PERCENTRANK.EXC
PERCENTRANK.INC
PERMUT
PERMUTATIONA
PHI
PMT
POISSON
POISSON.DIST
PPMT
PRICE
PRICEDISC
PRICEMAT
PROB
PV

QUARTILE
QUARTILE.EXC
QUARTILE.INC

RANK
RANK.AVG
RANK.EQ
RATE
RAWSUBTRACT
RECEIVED
REGEX
REPLACE
ROT13
ROUNDSIG
ROW
ROWS
RRI
RSQ

SEARCH
SEARCHB
SECOND
SHEET
SHEETS
SKEW
SKEWP
SLN
SLOPE
SMALL
STANDARDIZE
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVP
STDEVPA
STEYX
SUBSTITUTE
SUBTOTAL
SUMIF
SUMIFS
SUMX2MY2
SUMX2PY2
SUMXMY2
SWITCH
SYD

T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
T.TEST
TBILLEQ
TBILLPRICE
TBILLYIELD
TDIST
TEXT
TEXTJOIN
TIME
TIMEVALUE
TINV
TODAY
TREND
TRIMMEAN
TTEST
TYPE

VALUE
VAR
VAR.P
VAR.S
VARA
VARP
VARPA
VDB

WEBSERVICE
WEEKDAY
WEEKNUM
WEEKNUM_EXCEL2003
WEEKS
WEEKSINYEAR
WEIBULL
WEIBULL.DIST
WORKDAY
WORKDAY.INTL

XIRR
XNPV

YEAR
YEARFRAC
YEARS
YIELD
YIELDDISC
YIELDMAT

Z.TEST
ZTEST

Based on tests, the following functions were reported as working:

ABS
ACOS
ACOSH
ACOT
ACOTH
AND
ASIN
ASINH
ATAN
ATAN2
ATANH
AVERAGE
AVERAGEA

BASE

CEILING
CEILING.MATH
CEILING.PRECISE
CEILING.XCL
CHAR
CODE
COMBIN
COMBINA
CONCATENATE
COS
COSH
COT
COUNT
COUNTA
COUNTBLANK
CSC
CSCH

DECIMAL
DEGREES

EVEN
EXACT
EXP

FACT
FACTDOUBLE
FALSE
FLOOR
FLOOR.MATH
FLOOR.PRECISE
FLOOR.XCL

GCD
GCD_EXCEL2003

IF
IFERROR
IFNA
INT
ISO.CEILING

LCM
LCM_EXCEL2003
LEFT
LEFTB
LEN
LENB
LN
LOG
LOG10

MAX
MDETERM
MEDIAN
MIN
MOD
MROUND
MULTINOMIAL
MUNIT

NA
NOT

ODD
OR

PI
POWER
PRODUCT
PROPER

QUOTIENT

RADIANS
RAND
RANDBETWEEN
REPT
RIGHT
RIGHTB
ROMAN
ROUND
ROUNDDOWN
ROUNDUP

SEC
SECH
SERIESSUM
SIGN
SIN
SINH
SQRT
SQRTPI
SUM
SUMPRODUCT
SUMSQ

T
TAN
TANH
TRANSPOSE
TRIM
TRUE
TRUNC

UNICHAR
UNICODE
UPPER

VLOOKUP

XOR

and the following ones need more investigation:

CUBEKPIMEMBER
CUBEMEMBER
CUBEMEMBERPROPERTY
CUBERANKEDMEMBER
CUBESET
CUBESETCOUNT
CUBEVALUE
DAVERAGE
DBCS
DDE
DGET
DMAX
DMIN
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DVAR
DVARP
FILTERXML
FORECAST.SEASONALITY
GETPIVOTDATA
ODDFPRICE
ODDFYIELD
STYLE

Expected Behavior

Expect all functions to work.

Actual Behavior

Need to implement the missing functions.

[gunnsth]

Categorization of functions:
https://support.office.com/en-us/article/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

- 10 most popular functions
SUM
IF
LOOKUP
VLOOKUP
MATCH
CHOOSE
DATE
DAYS
FIND
FINDB
INDEX

- Date and time functions
DATE
DATEDIF
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
HOUR
ISOWEEKNUM
MINUTE
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
NOW
SECOND
TIME
TIMEVALUE
TODAY
WEEKDAY
WEEKNUM
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC

- Engineering functions
BESSELI
BESSELJ
BESSELK
BESSELY
BIN2DEC
BIN2HEX
BIN2OCT
BITAND
BITLSHIFT
BITOR
BITRSHIFT
BITXOR
COMPLEX
CONVERT
DEC2BIN
DEC2HEX
DEC2OCT
DELTA
ERF
ERF.PRECISE
ERFC
ERFC.PRECISE
GESTEP
HEX2BIN
HEX2DEC
HEX2OCT
IMABS
IMAGINARY
IMARGUMENT
IMCONJUGATE
IMCOS
IMCOSH
IMCOT
IMCSC
IMCSCH
IMDIV
IMEXP
IMLN
IMLOG10
IMLOG2
IMPOWER
IMPRODUCT
IMREAL
IMSEC
IMSECH
IMSIN
IMSINH
IMSQRT
IMSUB
IMSUM
IMTAN
OCT2BIN
OCT2DEC
OCT2HEX

- Financial functions
ACCRINT
ACCRINTM
AMORDEGRC
AMORLINC
COUPDAYBS
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
CUMIPMT
CUMPRINC
DB
DDB
DISC
DOLLARDE
DOLLARFR
DURATION
EFFECT
FV
FVSCHEDULE
INTRATE
IPMT
IRR
ISPMT
MDURATION
MIRR
NOMINAL
NPER
NPV
ODDFRPRICE
ODDFYIELD
ODDLPRICE
ODDLYIELD
PDURATION
PMT
PPMT
PRICE
PRICEDISC
PRICEMAT
PV
RATE
RECEIVED
RRI
SLN
SYD
TBILLEQ
TBILLPRICE
TBILLYIELD
VDB
XIRR
XNPV
YIELD
YIELDDISC
YIELDMAT

- Information functions:
CELL
ERROR.TYPE
INFO
ISBLANK
ISERR
ISERROR
ISEVEN
ISFORMULA
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISODD
ISREF
ISTEXT
N
NA
SHEET
SHEETS
TYPE

- Logical functions
AND
FALSE
IF
IFERROR
IFNA
IFS
NOT
OR
SWITCH
TRUE
XOR

- Lookup and reference
ADDRESS
AREAS
CHOOSE
COLUMN
COLUMNS
FILTER
FORMULATEXT
GETPIVOTDATA
HLOOKUP
HYPERLINK
INDEX
INDIRECT
LOOKUP
MATCH
OFFSET
ROW
ROWS
RTD
SORT
SORTBY
TRANSPOSE
UNIQUE
VLOOKUP
XLOOKUP
XMATCH

- Math and trigonometry
ABS
ACOS
ACOSH
ACOT
ACOTH
AGGREGATE
ARABIC
ASIN
ASINH
ATAN
ATAN2
ATANH
BASE
CEILING
CEILING.MATH
CEILING.PRECISE
COMBIN
COMBINA
COS
COSH
COT
COTH
CSC
CSCH
DECIMAL
DEGREES
EVEN
EXP
FACT
FACTDOUBLE
FLOOR
FLOOR.MATH
FLOOR.PRECISE
GCD
INT
ISO.CEILING
LCM
LN
LOG
LOG10
MDETERM
MINVERSE
MMULT
MOD
MROUND
MULTINOMIAL
MUNIT
ODD
PI
POWER
PRODUCT
QUOTIENT
RADIANS
RAND
RANDARRAY
RANDBETWEEN
ROMAN
ROUND
ROUNDDOWN
ROUNDUP
SEC
SECH
SERIESSUM
SEQUENCE
SIGN
SIN
SINH
SQRT
SQRTPI
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
TAN
TANH
TRUNC

- Statistical functions
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
BETA.DIST
BETA.INV
BINOM.DIST
BINOM.DIST.RANGE
BINOM.INV
CHISQ.DIST
CHISQ.DIST.RT
CHISQ.INV
CHISQ.INV.RT
CHISQ.TEST
CONFIDENCE.NORM
CONFIDENCE.T
CORREL
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
COVARIANCE.P
COVARIANCE.S
DEVSQ
EXPON.DIST
F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST
FISHER
FISHERINV
FORECAST
FORECAST.ETS
FORECAST.ETS.CONFINT
FORECAST.ETS.SEASONALITY
FORECAST.ETS.STAT
FORECAST.LINEAR
FREQUENCY
GAMMA
GAMMA.DIST
GAMMA.INV
GAMMALN
GAMMALN.PRECISE
GAUSS
GEOMEAN
GROWTH
HARMEAN
HYPGEOM.DIST
INTERCEPT
KURT
LARGE
LINEST
LOGEST
LOGNORM.DIST
LOGNORM.INV
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
MODE.MULT
MODE.SNGL
NEGBINOM.DIST
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
PEARSON
PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK.EXC
PERCENTRANK.INC
PERMUT
PERMUTATIONA
PHI
POISSON.DIST
PROB
QUARTILE.EXC
QUARTILE.INC
RANK.AVG
RANK.EQ
RSQ
SKEW
SKEW.P
SLOPE
SMALL
STANDARDIZE
STDEV.P
STDEV.S
STDEVA
STDEVPA
STEYX
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
T.TEST
TREND
TRIMMEAN
VAR.P
VAR.S
VARA
VARPA
WEIBULL.DIST
Z.TEST

- Text functions
ASC
BAHTTEXT
CHAR
CLEAN
CODE
CONCAT
CONCATENATE
DBCS
DOLLAR
EXACT
FIND, FINDB
FIXED
LEFT, LEFTB
LEN, LENB
LOWER
MID, MIDB
NUMBERVALUE
PHONETIC
PROPER
REPLACE, REPLACEB
REPT
RIGHT, RIGHTB
SEARCH, SEARCHB
SUBSTITUTE
T
TEXT
TEXTJOIN
TRIM
UNICHAR
UNICODE
UPPER
VALUE

- Web functions
ENCODEURL