FunctionsNumeric — compatibility and validation
FunctionsNumeric registers 296 worksheet functions into a FunctionRegistry (math & trigonometry,
conditional aggregates, statistics and distributions, information, engineering, database) through
NumericFunctions.register(into:). Target semantics: Microsoft Excel (argument coercion, error codes, edge cases).
Nothing in this document comes from running Excel: Excel's behaviour is taken from Microsoft's function documentation (the worked examples of the help pages are tests), LibreOffice is used as an executable spreadsheet reference and scipy / numpy / mpmath / statsmodels as independent numerical references.
How the functions are validated
| Source | Cases | What it vouches for |
|---|---|---|
LibreOffice 26.8 (headless recalculation of generated .xlsx workbooks, en-US locale) |
4 687 | spreadsheet semantics: coercion, blanks, criteria, ranges, array results, text results |
| scipy 1.17 + mpmath 1.3 (40 digits) | 5 440 | distributions, quantiles, special functions, Bessel, trigonometry, unit conversions (scipy.constants) |
Python decimal |
4 082 | ROUND / ROUNDUP / ROUNDDOWN / TRUNC on the 15-digit decimal reading, MOD, QUOTIENT |
| numpy + scipy.stats | 606 | descriptive statistics, percentiles, ranks, correlation, t / F / z / χ² tests |
| statsmodels OLS + numpy.linalg + mpmath | 143 | LINEST / LOGEST statistics blocks, TREND, GROWTH, MINVERSE, MDETERM, MMULT |
Python cmath |
420 | complex-number functions |
| Reference corpus (JSON fixtures) | 15 378 | Tests/FunctionsNumericTests/Fixtures/*.json |
| Microsoft documentation examples (hand-written) | ≈ 370 | ExcelDocumentationTests.swift: the examples of Excel's help pages, to the printed digits |
| Hand-written edge cases | ≈ 500 | EdgeCaseTests.swift: 201 Excel error codes, logical values, arrays, blanks, whole columns, hidden rows |
Tolerances: numbers are compared with a relative tolerance of 1e-12 (scipy / mpmath / numpy fixtures; 1e-11 for
HYPGEOM.DIST and 2e-11 for BESSELJ / BESSELY, which rely on libm's jn / yn), exactly (tolerance 0) for decimal
rounding, 1e-9 to 1e-13 against LibreOffice (LibreOffice writes 15 significant digits; its distribution functions
are compared at 1e-8 because they are themselves less accurate), text and logical values exactly. For LibreOffice
cases whose result is an error only the fact that an error is returned is compared, because LibreOffice's error
codes differ from Excel's; the exact Excel codes are asserted by the hand-written tests.
The generators are in Tests/FunctionsNumericTests/Generators/ (gen_libreoffice.py, gen_scipy.py;
overrides.py holds the 51 cases where the fixture stores Excel's documented result instead of LibreOffice's;
coverage.py builds the table below). FN_DUMP=<dir> swift test writes every mismatch of a regenerated corpus.
While building the corpus the references disagreed with each other in three places; each was arbitrated with
mpmath at 40–400 digits: scipy's poisson.pmf (4e-12 off for large means), t.isf / f.isf (up to 1e-8 off in
the tails) — the fixtures use the mpmath values.
Numerical methods
- ROUND family: the number is read at 15 significant digits (which removes binary noise: 1.005 is
1.00499999999999989…) and rounded on that decimal expansion, half away from zero. A fast path scales by an exact
power of ten when the scaled value is far from a rounding boundary. CEILING / FLOOR / MROUND / EVEN / ODD snap a
quotient that is an integer to 15 digits before rounding it (
CEILING(1.1,0.1)is 1.1, not 1.2). - Distributions: probability masses and densities use Loader's saddle-point expansion (
stirlerr/bd0); the regularized incomplete gamma and beta functions use a power series or a continued fraction (modified Lentz) with the mass as prefactor; the normal quantile is Acklam's approximation polished by Halley steps onerfc; the t / χ² / F / beta / gamma quantiles use Brent's method on whichever tail is smaller, so both tails keep full relative accuracy. Measured accuracy against 40-digit references: ≤ 1e-12 relative over the test grid. - Linear algebra: MINVERSE and MDETERM call LAPACK (
dgetrf,dgetri) and LINEST / LOGEST / TREND / GROWTH the LAPACK SVD (dgesvd) of the centered design matrix, through a small C shim (CNumericLAPACK) compiled withACCELERATE_NEW_LAPACK. - Bessel:
jn/ynof libm; I_n by its all-positive power series; K_n by the trapezoidal rule on ∫₀^∞ exp(−x·cosh t)·cosh(nt) dt (geometric convergence).
Known deviations from Excel
Deliberate or unavoidable differences, and behaviours that could not be checked against a reference.
Rounding and arithmetic
- ROUND / ROUNDUP / ROUNDDOWN / TRUNC apply the 15-digit rule uniformly (
ROUND(2.4999999999999996,0)is 3). This matches Excel on every documented tie; Excel's behaviour on values that differ from a tie only beyond the 15th digit is not documented. - MOD has no
#NUM!limit on the size of the quotient. SIN, COS, TAN… accept arguments above 2^27 (Excel returns#NUM!). COMBINA(0,0)is 1;FACTDOUBLEof a negative number is#NUM!(as documented).- LARGE / SMALL round a non-integer
kup. Not verified (LibreOffice truncates for SMALL).
Criteria (COUNTIF, SUMIF, …IFS, database functions)
- A numeric criterion with
=/<>also matches numeric text (COUNTIF(range,5)counts"5"); inequalities only look at numeric cells; wildcards only match text cells. This follows Excel and differs from LibreOffice. - Date criteria are understood as ISO (
2024-03-01) or US (3/1/2024) dates only, independent of the locale. - Database functions: a text criterion without operator matches as a prefix (
DavfindsDavid),="=Dave"is exact. Computed criteria (a formula under a blank or unknown header) are not supported and return#VALUE!.
Statistics
- PERCENTRANK* truncate the result to
significancedecimals (Excel's documented examples: 0.333, 0.666, 0.583); LibreOffice rounds to significant digits instead. - MODE / MODE.SNGL return the first mode encountered; MODE.MULT lists the modes in order of first appearance.
- LINEST / LOGEST with collinear regressors return the minimum-norm least-squares solution with zero standard
errors (Excel sets the coefficient of a redundant column to 0). With no degrees of freedom F is
#NUM!. Non-numeric cells in the regression ranges are#VALUE!. - Degrees of freedom of the t, χ² and F functions are truncated to integers (T.TEST type 3 keeps the fractional Welch degrees of freedom).
- Densities that are infinite at the edge of the support (
GAMMA.DIST(0,0.5,1,FALSE)…) return#NUM!.CHISQ.DIST(0,2,FALSE)is 0.5 (LibreOffice: 0).
Engineering
- BESSELJ / BESSELY / BESSELI / BESSELK are accurate to about 1e-11 or better. Excel's own results are only good to about 7 digits (its help page prints 0.329925829 for J₂(1.9) = 0.3299257277), so results differ from Excel beyond the 7th digit.
- CONVERT implements the Excel 2013+ unit table with the exact SI definitions. LibreOffice still uses the rounded
factors of Excel 2003 for many units and is therefore only used for the exact-definition groups; everything else
is checked against
scipy.constants. Not independently validated:u(1.660538782e-24 g),eV(1.602176487e-19 J),parsec/pc(3.08567758128155e16 m),mmHg(133.322 Pa) — the values believed to be Excel's. Prefixes on squared and cubed metric units are raised to the power (km2= 1e6 m²). - BITLSHIFT returns
#NUM!when the result reaches 2^48 (not verified). - Complex results are formatted with 15 significant digits like General cells; IMPOWER works in polar form as
Excel does (
IMPOWER("2+3i",3)is-46+9.00000000000001iin Excel; the last digit can differ).
Information
- CELL supports
address,row,col,contents,type; INFO supportssystem,osversion,release,recalc,numfile; other types return#VALUE!. SHEETS does not know 3-D references. - TYPE returns 128 for a LAMBDA value. ERROR.TYPE returns 9 for
#SPILL!, 14 for#CALC!and 15 for the ORIX extension#CIRC!. Tis left to the text package.
SUBTOTAL / AGGREGATE
- Rows hidden by a filter are always ignored by SUBTOTAL; AGGREGATE ignores hidden and filtered rows with options 1, 3, 5, 7. Cells whose formula is itself a SUBTOTAL or AGGREGATE call (possibly negated or parenthesised) are skipped; a subtotal nested deeper inside another expression is not detected.
- Integration note: these functions read
SheetMeta.hiddenRows/filteredRowsat evaluation time and are not volatile. The core engine tracks the dependency on row visibility itself: every formula whose expression contains a SUBTOTAL or AGGREGATE call is recorded when it is registered (CalcEngine.visibilityCells), andCalcEngine.editMeta— as well as undo / redo of such a change — schedules those formulas and their dependents for recalculation wheneverhiddenRowsorfilteredRowsof a sheet changes (markVisibilityDependents()). The application has nothing to dirty by hand, provided row visibility is changed througheditMeta; code that mutatesSheetMetabehind the engine's back must still callmarkAllDirty().
Where LibreOffice differs from Excel (the fixture stores Excel's documented result — see overrides.py):
MROUND with mixed signs, POWER(0,0), POWER(-8,1/3), COMBINA(n<k), ROMAN(n,TRUE/FALSE), ARABIC("-X"),
ATAN2(0,0), LOGNORM.DIST(0,…), EXPON.DIST(-1,…), BITAND / BITOR / BITXOR / BITLSHIFT with non-integers,
DEC2HEX(-12.9), ISNUMBER(TRUE), TYPE(TRUE), ERROR.TYPE(SQRT(-1)), GEOMEAN with a zero, MODE(4,4,1,1),
wildcards and numeric criteria in COUNTIF / SUMIF, mixing i and j in complex functions, DGET with several
matches, DSTDEV / DVAR without matches. LibreOffice has no logical cell type (TRUE is the number 1), so every
behaviour involving logical values inside ranges is covered by hand-written tests only.
Not implemented
Everything requested is registered. Not provided: T (text package), computed database criteria, 3-D references in
SHEETS, the remaining CELL / INFO types.
Function table
Columns: number of reference cases whose formula calls the function, by source. "Hand-written" counts the formulas
of ExcelDocumentationTests.swift and EdgeCaseTests.swift.
| Function | Category | LibreOffice | Independent reference | Hand-written | Remark |
|---|---|---|---|---|---|
ACOS |
Trigonometry | 11 | scipy/mpmath 7 | 1 | |
ACOSH |
Trigonometry | 11 | scipy/mpmath 9 | 2 | |
ACOT |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
ACOTH |
Trigonometry | 11 | scipy/mpmath 9 | 2 | |
AGGREGATE |
Math | 183 | – | 17 | not volatile; recalculated when row visibility changes (see integration note) |
ARABIC |
Math | 13 | – | 3 | LO rejects '-X' and spaces |
ASIN |
Trigonometry | 11 | scipy/mpmath 7 | 1 | |
ASINH |
Trigonometry | 11 | scipy/mpmath 9 | – | |
ATAN |
Trigonometry | 11 | scipy/mpmath 9 | – | |
ATAN2 |
Trigonometry | 10 | – | 3 | LO returns 0 for (0,0) |
ATANH |
Trigonometry | 11 | scipy/mpmath 7 | 2 | |
AVEDEV |
Statistical | 13 | numpy/scipy 6 | 1 | |
AVERAGEA |
Statistical | 13 | numpy/scipy 6 | 1 | |
AVERAGEIF |
Math | 57 | – | 2 | |
AVERAGEIFS |
Statistical | 15 | – | 3 | |
BASE |
Math | 12 | – | 5 | |
BESSELI |
Engineering | – | scipy/mpmath 105 | 2 | exact (Excel itself ~7 digits) |
BESSELJ |
Engineering | – | scipy/mpmath 108 | 3 | exact (Excel itself ~7 digits) |
BESSELK |
Engineering | – | scipy/mpmath 87 | 2 | exact (Excel itself ~7 digits) |
BESSELY |
Engineering | – | scipy/mpmath 84 | 3 | exact (Excel itself ~7 digits) |
BETA.DIST |
Statistical | 8 | scipy/mpmath 288 | 5 | |
BETA.INV |
Statistical | 3 | scipy/mpmath 128 | 2 | |
BETADIST |
Statistical | 2 | scipy/mpmath 144 | – | |
BETAINV |
Statistical | 1 | scipy/mpmath 64 | – | |
BIN2DEC |
Engineering | 11 | – | 5 | |
BIN2HEX |
Engineering | 20 | – | 3 | |
BIN2OCT |
Engineering | 20 | – | 3 | |
BINOM.DIST |
Statistical | 8 | scipy/mpmath 148 | 4 | |
BINOM.DIST.RANGE |
Statistical | – | scipy/mpmath 20 | 2 | unknown to LibreOffice's xlsx import |
BINOM.INV |
Statistical | 5 | scipy/mpmath 70 | 2 | |
BINOMDIST |
Statistical | 1 | scipy/mpmath 74 | – | |
BITAND |
Engineering | 10 | – | 4 | LO truncates non-integers (Excel: #NUM!) |
BITLSHIFT |
Engineering | 9 | – | 2 | result >= 2^48 is #NUM! (unverified) |
BITOR |
Engineering | 10 | – | 2 | LO truncates non-integers |
BITRSHIFT |
Engineering | 9 | – | 2 | |
BITXOR |
Engineering | 10 | – | 1 | LO truncates non-integers |
CEILING |
Math | 21 | – | 7 | |
CEILING.MATH |
Math | 39 | – | 4 | |
CEILING.PRECISE |
Math | 27 | – | 5 | |
CELL |
Information | 7 | – | 12 | 5 info types |
CHIDIST |
Statistical | 1 | scipy/mpmath 99 | – | |
CHIINV |
Statistical | 1 | scipy/mpmath 81 | – | |
CHISQ.DIST |
Statistical | 6 | scipy/mpmath 198 | 4 | |
CHISQ.DIST.RT |
Statistical | 3 | scipy/mpmath 99 | 1 | |
CHISQ.INV |
Statistical | 4 | scipy/mpmath 81 | 4 | |
CHISQ.INV.RT |
Statistical | 3 | scipy/mpmath 81 | 2 | |
CHISQ.TEST |
Statistical | 3 | numpy/scipy 2 | 3 | |
CHITEST |
Statistical | 1 | numpy/scipy 1 | – | |
COMBIN |
Math | 12 | scipy/mpmath 8 | 3 | |
COMBINA |
Math | 12 | scipy/mpmath 7 | 2 | LO differs for n < k and (0,0) |
COMPLEX |
Engineering | 11 | – | 6 | |
CONFIDENCE |
Statistical | 1 | numpy/scipy 5 | 1 | |
CONFIDENCE.NORM |
Statistical | 4 | numpy/scipy 5 | 4 | |
CONFIDENCE.T |
Statistical | 3 | numpy/scipy 5 | 2 | |
CONVERT |
Engineering | 223 | scipy/mpmath 511 | 8 | LO only for exact-definition units; u, eV, parsec, mmHg unverified |
CORREL |
Statistical | 10 | numpy/scipy 4 | 4 | |
COS |
Trigonometry | 11 | scipy/mpmath 9 | – | |
COSH |
Trigonometry | 11 | scipy/mpmath 9 | – | |
COT |
Trigonometry | 11 | scipy/mpmath 9 | 2 | |
COTH |
Trigonometry | 11 | scipy/mpmath 9 | 2 | |
COUNTBLANK |
Statistical | 6 | – | 2 | |
COUNTIF |
Statistical | 85 | – | 18 | numeric criterion matches numeric text; wildcards match text only |
COUNTIFS |
Statistical | 15 | – | 5 | |
COVAR |
Statistical | 10 | numpy/scipy 4 | 1 | |
COVARIANCE.P |
Statistical | 10 | numpy/scipy 4 | 1 | |
COVARIANCE.S |
Statistical | 10 | numpy/scipy 4 | 2 | |
CRITBINOM |
Statistical | 1 | scipy/mpmath 70 | – | |
CSC |
Trigonometry | 11 | scipy/mpmath 9 | 2 | |
CSCH |
Trigonometry | 11 | scipy/mpmath 9 | 2 | |
DAVERAGE |
Database | 24 | – | 3 | |
DCOUNT |
Database | 33 | – | 3 | |
DCOUNTA |
Database | 33 | – | 5 | |
DEC2BIN |
Engineering | 44 | – | 6 | |
DEC2HEX |
Engineering | 42 | – | 5 | LO floors negative non-integers |
DEC2OCT |
Engineering | 40 | – | 3 | |
DECIMAL |
Math | 7 | – | 4 | |
DEGREES |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
DELTA |
Engineering | 6 | – | 4 | |
DEVSQ |
Statistical | 13 | numpy/scipy 6 | 1 | |
DGET |
Database | 32 | – | 5 | LO ignores empty fields when counting matches |
DMAX |
Database | 24 | – | 2 | |
DMIN |
Database | 24 | – | 1 | |
DPRODUCT |
Database | 24 | – | 1 | |
DSTDEV |
Database | 24 | – | 1 | LO returns 0 without matches |
DSTDEVP |
Database | 24 | – | 1 | |
DSUM |
Database | 27 | – | 10 | |
DVAR |
Database | 24 | – | 1 | LO returns 0 without matches |
DVARP |
Database | 24 | – | 1 | |
ERF |
Engineering | 14 | scipy/mpmath 30 | 2 | |
ERF.PRECISE |
Engineering | 7 | scipy/mpmath 15 | 1 | |
ERFC |
Engineering | 7 | scipy/mpmath 15 | 1 | |
ERFC.PRECISE |
Engineering | 7 | scipy/mpmath 15 | 1 | |
ERROR.TYPE |
Information | 9 | – | 5 | ORIX extensions: #SPILL! 9, #CALC! 14, #CIRC! 15 |
EVEN |
Math | 8 | – | 5 | |
EXP |
Math | 11 | scipy/mpmath 9 | 2 | |
EXPON.DIST |
Statistical | 5 | scipy/mpmath 56 | 4 | x < 0 is #NUM! (LO: 0) |
EXPONDIST |
Statistical | 1 | scipy/mpmath 28 | – | |
F.DIST |
Statistical | 6 | scipy/mpmath 180 | 4 | |
F.DIST.RT |
Statistical | 2 | scipy/mpmath 90 | 1 | |
F.INV |
Statistical | 2 | scipy/mpmath 72 | 1 | |
F.INV.RT |
Statistical | 3 | scipy/mpmath 72 | 2 | |
F.TEST |
Statistical | 2 | numpy/scipy 4 | 3 | |
FACT |
Math | 11 | scipy/mpmath 8 | 5 | |
FACTDOUBLE |
Math | 11 | scipy/mpmath 8 | 3 | negative is #NUM! (documentation) |
FDIST |
Statistical | 1 | scipy/mpmath 90 | – | |
FINV |
Statistical | 1 | scipy/mpmath 72 | – | |
FISHER |
Statistical | 7 | scipy/mpmath 7 | 2 | |
FISHERINV |
Statistical | 7 | scipy/mpmath 7 | 1 | |
FLOOR |
Math | 21 | – | 8 | |
FLOOR.MATH |
Math | 39 | – | 4 | |
FLOOR.PRECISE |
Math | 27 | – | 5 | |
FORECAST |
Statistical | 14 | numpy/scipy 4 | 2 | |
FORECAST.LINEAR |
Statistical | 12 | numpy/scipy 4 | 1 | |
FORMULATEXT |
Lookup & Reference | 2 | – | 3 | en-US syntax |
FREQUENCY |
Statistical | 17 | – | 4 | |
FTEST |
Statistical | 1 | numpy/scipy 4 | – | |
GAMMA |
Statistical | 8 | scipy/mpmath 15 | 4 | |
GAMMA.DIST |
Statistical | 6 | scipy/mpmath 126 | 4 | |
GAMMA.INV |
Statistical | 4 | scipy/mpmath 63 | 2 | |
GAMMADIST |
Statistical | 1 | scipy/mpmath 63 | – | |
GAMMAINV |
Statistical | 1 | scipy/mpmath 63 | – | |
GAMMALN |
Statistical | 5 | scipy/mpmath 10 | 3 | |
GAMMALN.PRECISE |
Statistical | 2 | scipy/mpmath 10 | 1 | |
GAUSS |
Statistical | 3 | scipy/mpmath 15 | 1 | |
GCD |
Math | 8 | – | 8 | |
GEOMEAN |
Statistical | 13 | numpy/scipy 4 | 3 | LO returns 0 when a value is 0 |
GESTEP |
Engineering | 5 | – | 5 | |
GROWTH |
Statistical | 14 | statsmodels/numpy 3 | 3 | |
HARMEAN |
Statistical | 13 | numpy/scipy 4 | 2 | |
HEX2BIN |
Engineering | 26 | – | 4 | |
HEX2DEC |
Engineering | 14 | – | 4 | |
HEX2OCT |
Engineering | 26 | – | 3 | |
HYPGEOM.DIST |
Statistical | 6 | scipy/mpmath 56 | 4 | |
HYPGEOMDIST |
Statistical | 1 | scipy/mpmath 28 | – | |
IMABS |
Engineering | 14 | Python cmath 10 | 4 | |
IMAGINARY |
Engineering | 14 | Python cmath 10 | 5 | |
IMARGUMENT |
Engineering | 14 | Python cmath 10 | 2 | |
IMCONJUGATE |
Engineering | 14 | Python cmath 10 | 2 | |
IMCOS |
Engineering | 14 | Python cmath 10 | 1 | |
IMDIV |
Engineering | 28 | Python cmath 40 | 2 | LO allows mixing i and j |
IMEXP |
Engineering | 14 | Python cmath 10 | 1 | |
IMLN |
Engineering | 14 | Python cmath 10 | 2 | |
IMLOG10 |
Engineering | 14 | Python cmath 10 | 1 | |
IMLOG2 |
Engineering | 14 | Python cmath 10 | 1 | |
IMPOWER |
Engineering | 70 | Python cmath 60 | 2 | |
IMPRODUCT |
Engineering | 49 | Python cmath 80 | 3 | |
IMREAL |
Engineering | 14 | Python cmath 10 | 2 | |
IMSIN |
Engineering | 14 | Python cmath 10 | 1 | |
IMSQRT |
Engineering | 14 | Python cmath 10 | 3 | |
IMSUB |
Engineering | 29 | Python cmath 40 | 2 | LO allows mixing i and j |
IMSUM |
Engineering | 49 | Python cmath 80 | 6 | LO allows mixing i and j |
INFO |
Information | – | – | 4 | 5 info types |
INT |
Math | 8 | – | 3 | |
INTERCEPT |
Statistical | 10 | numpy/scipy 4 | 1 | |
ISBLANK |
Information | 12 | – | 2 | |
ISERR |
Information | 12 | – | 2 | |
ISERROR |
Information | 12 | – | 2 | |
ISEVEN |
Information | 10 | – | 3 | |
ISFORMULA |
Information | 2 | – | 3 | |
ISLOGICAL |
Information | 12 | – | 1 | |
ISNA |
Information | 12 | – | 2 | |
ISNONTEXT |
Information | 12 | – | 1 | |
ISNUMBER |
Information | 12 | – | 3 | LO has no logical type |
ISO.CEILING |
Math | 27 | – | 6 | |
ISODD |
Information | 10 | – | 2 | |
ISOMITTED |
Information | – | – | 4 | unknown to LibreOffice |
ISREF |
Information | 4 | – | 4 | |
ISTEXT |
Information | 12 | – | 1 | |
KURT |
Statistical | 13 | numpy/scipy 6 | 2 | |
LARGE |
Statistical | 12 | – | 7 | non-integer k rounded up (unverified) |
LCM |
Math | 7 | – | 4 | |
LINEST |
Statistical | 83 | statsmodels/numpy 61 | 13 | collinear regressors: minimum-norm solution |
LN |
Math | 11 | scipy/mpmath 9 | 2 | |
LOG |
Math | 10 | scipy/mpmath 9 | 4 | |
LOG10 |
Math | 11 | scipy/mpmath 9 | 2 | |
LOGEST |
Statistical | 52 | statsmodels/numpy 42 | 3 | collinear regressors: minimum-norm solution |
LOGINV |
Statistical | 1 | scipy/mpmath 22 | – | |
LOGNORM.DIST |
Statistical | 4 | scipy/mpmath 48 | 3 | x <= 0 is #NUM! (LO: 0) |
LOGNORM.INV |
Statistical | 2 | scipy/mpmath 22 | 2 | |
LOGNORMDIST |
Statistical | 1 | scipy/mpmath 24 | – | |
MAXA |
Statistical | 13 | – | 1 | |
MAXIFS |
Statistical | 15 | – | 2 | |
MDETERM |
Math | 7 | statsmodels/numpy 3 | 5 | |
MEDIAN |
Statistical | 13 | numpy/scipy 6 | 8 | |
MINA |
Statistical | 13 | – | 2 | |
MINIFS |
Statistical | 15 | – | 1 | |
MINVERSE |
Math | 23 | statsmodels/numpy 19 | 4 | |
MMULT |
Math | 19 | statsmodels/numpy 9 | 4 | |
MOD |
Math | 12 | Python decimal 11 | 6 | no #NUM! for huge quotients |
MODE |
Statistical | 16 | – | 1 | first mode encountered (LO: smallest) |
MODE.MULT |
Statistical | 7 | – | 2 | |
MODE.SNGL |
Statistical | 13 | – | 1 | first mode encountered |
MROUND |
Math | 15 | – | 7 | LO differs on mixed signs (Excel: #NUM!) |
MULTINOMIAL |
Math | 6 | – | 3 | |
MUNIT |
Math | 11 | – | 2 | |
N |
Information | 14 | – | 4 | |
NEGBINOM.DIST |
Statistical | 4 | scipy/mpmath 72 | 5 | |
NEGBINOMDIST |
Statistical | 1 | scipy/mpmath 36 | – | |
NORM.DIST |
Statistical | 6 | scipy/mpmath 30 | 4 | |
NORM.INV |
Statistical | 5 | scipy/mpmath 22 | 4 | |
NORM.S.DIST |
Statistical | 3 | scipy/mpmath 30 | 3 | |
NORM.S.INV |
Statistical | 5 | scipy/mpmath 22 | 2 | |
NORMDIST |
Statistical | 2 | scipy/mpmath 15 | – | |
NORMINV |
Statistical | 1 | scipy/mpmath 22 | – | |
NORMSDIST |
Statistical | 2 | scipy/mpmath 15 | – | |
NORMSINV |
Statistical | 1 | scipy/mpmath 22 | – | |
OCT2BIN |
Engineering | 20 | – | 3 | |
OCT2DEC |
Engineering | 11 | – | 3 | |
OCT2HEX |
Engineering | 20 | – | 2 | |
ODD |
Math | 8 | – | 6 | |
PEARSON |
Statistical | 10 | numpy/scipy 4 | 1 | |
PERCENTILE |
Statistical | 42 | numpy/scipy 66 | 2 | |
PERCENTILE.EXC |
Statistical | 42 | numpy/scipy 42 | 3 | |
PERCENTILE.INC |
Statistical | 42 | numpy/scipy 66 | 1 | |
PERCENTRANK |
Statistical | 33 | – | 3 | truncates decimals (LO rounds significant digits) |
PERCENTRANK.EXC |
Statistical | 33 | – | 3 | truncates decimals (LO rounds significant digits) |
PERCENTRANK.INC |
Statistical | 33 | numpy/scipy 36 | 4 | truncates decimals (LO rounds significant digits) |
PERMUT |
Statistical | 12 | scipy/mpmath 6 | 3 | |
PERMUTATIONA |
Statistical | 12 | scipy/mpmath 6 | 2 | |
PHI |
Statistical | 3 | scipy/mpmath 15 | 1 | |
PI |
Math | 1 | – | 1 | |
POISSON |
Statistical | 1 | scipy/mpmath 49 | – | |
POISSON.DIST |
Statistical | 6 | scipy/mpmath 98 | 4 | |
POWER |
Math | 10 | – | 6 | LO differs for 0^0 and negative^fraction |
PROB |
Statistical | 7 | – | 4 | |
PRODUCT |
Math | 4 | numpy/scipy 6 | 5 | |
QUARTILE |
Statistical | 24 | – | 2 | |
QUARTILE.EXC |
Statistical | 24 | numpy/scipy 18 | 3 | |
QUARTILE.INC |
Statistical | 24 | numpy/scipy 30 | 1 | |
QUOTIENT |
Math | 12 | Python decimal 11 | 4 | |
RADIANS |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
RANDARRAY |
Math | – | – | 7 | random: shape, range, integrality |
RANDBETWEEN |
Math | – | – | 4 | random: range and integrality |
RANK |
Statistical | 28 | – | 3 | |
RANK.AVG |
Statistical | 28 | numpy/scipy 36 | 2 | |
RANK.EQ |
Statistical | 28 | numpy/scipy 36 | 2 | |
ROMAN |
Math | 108 | – | 11 | LO differs for logical form |
ROUND |
Math | 100 | Python decimal 1015 | 32 | 15-digit decimal rule |
ROUNDDOWN |
Math | 100 | Python decimal 1015 | 6 | 15-digit decimal rule |
ROUNDUP |
Math | 100 | Python decimal 1015 | 7 | 15-digit decimal rule |
RSQ |
Statistical | 10 | numpy/scipy 4 | 1 | |
SEC |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
SECH |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
SERIESSUM |
Math | 4 | – | 3 | |
SHEET |
Information | 3 | – | 4 | |
SHEETS |
Information | 1 | – | 2 | |
SIGN |
Math | 11 | – | 1 | |
SIN |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
SINH |
Trigonometry | 11 | scipy/mpmath 9 | 1 | |
SKEW |
Statistical | 13 | numpy/scipy 6 | 4 | |
SKEW.P |
Statistical | 13 | numpy/scipy 6 | 1 | |
SLOPE |
Statistical | 10 | numpy/scipy 4 | 2 | |
SMALL |
Statistical | 12 | – | 3 | non-integer k rounded up (unverified; LO truncates) |
SQRT |
Math | 12 | scipy/mpmath 9 | 5 | |
SQRTPI |
Math | 11 | scipy/mpmath 9 | 2 | |
STANDARDIZE |
Statistical | 4 | – | 2 | |
STDEV |
Statistical | 13 | numpy/scipy 6 | 3 | |
STDEV.P |
Statistical | 13 | numpy/scipy 6 | 1 | |
STDEV.S |
Statistical | 13 | numpy/scipy 6 | – | |
STDEVA |
Statistical | 13 | – | 1 | |
STDEVP |
Statistical | 13 | numpy/scipy 6 | – | |
STDEVPA |
Statistical | 13 | numpy/scipy 6 | 1 | |
STEYX |
Statistical | 10 | numpy/scipy 4 | 2 | |
SUBTOTAL |
Math | 51 | – | 19 | not volatile; recalculated when row visibility changes (see integration note) |
SUMIF |
Math | 78 | – | 8 | same criteria rules as COUNTIF |
SUMIFS |
Math | 16 | – | 4 | |
SUMPRODUCT |
Math | 11 | numpy/scipy 4 | 5 | |
SUMSQ |
Math | 4 | numpy/scipy 6 | 5 | |
SUMX2MY2 |
Math | 3 | numpy/scipy 4 | 2 | |
SUMX2PY2 |
Math | 2 | numpy/scipy 4 | 2 | |
SUMXMY2 |
Math | 2 | numpy/scipy 4 | 2 | |
T.DIST |
Statistical | 7 | scipy/mpmath 192 | 3 | |
T.DIST.2T |
Statistical | 4 | scipy/mpmath 56 | 2 | |
T.DIST.RT |
Statistical | 3 | scipy/mpmath 96 | 1 | |
T.INV |
Statistical | 6 | scipy/mpmath 96 | 3 | |
T.INV.2T |
Statistical | 4 | scipy/mpmath 96 | 2 | |
T.TEST |
Statistical | 14 | numpy/scipy 24 | 4 | |
TAN |
Trigonometry | 11 | scipy/mpmath 9 | – | |
TANH |
Trigonometry | 11 | scipy/mpmath 9 | – | |
TDIST |
Statistical | 4 | scipy/mpmath 112 | 2 | |
TINV |
Statistical | 1 | scipy/mpmath 96 | 1 | |
TREND |
Statistical | 25 | statsmodels/numpy 6 | 3 | |
TRIMMEAN |
Statistical | 20 | numpy/scipy 24 | 3 | |
TRUNC |
Math | 108 | Python decimal 1015 | 3 | 15-digit decimal rule |
TTEST |
Statistical | 6 | numpy/scipy 8 | – | |
TYPE |
Information | 13 | – | 8 | LO has no logical type |
VAR |
Statistical | 13 | numpy/scipy 6 | 1 | |
VAR.P |
Statistical | 13 | numpy/scipy 6 | – | |
VAR.S |
Statistical | 13 | numpy/scipy 6 | 1 | |
VARA |
Statistical | 13 | numpy/scipy 6 | 2 | |
VARP |
Statistical | 13 | numpy/scipy 6 | – | |
VARPA |
Statistical | 13 | – | 1 | |
WEIBULL |
Statistical | 1 | scipy/mpmath 35 | – | |
WEIBULL.DIST |
Statistical | 4 | scipy/mpmath 70 | 3 | |
Z.TEST |
Statistical | 3 | numpy/scipy 12 | 3 | |
ZTEST |
Statistical | 1 | – | – |