SPB Git

Repository Public

COMPATIBILITY.md 27.2 KB

# 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 on erfc; 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 with ACCELERATE_NEW_LAPACK.
  • Bessel: jn / yn of 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; FACTDOUBLE of a negative number is #NUM! (as documented).
  • LARGE / SMALL round a non-integer k up. 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 (Dav finds David), ="=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 significance decimals (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.00000000000001i in Excel; the last digit can differ).

Information

  • CELL supports address, row, col, contents, type; INFO supports system, 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!.
  • T is 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 / filteredRows at 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), and CalcEngine.editMeta — as well as undo / redo of such a change — schedules those formulas and their dependents for recalculation whenever hiddenRows or filteredRows of a sheet changes (markVisibilityDependents()). The application has nothing to dirty by hand, provided row visibility is changed through editMeta; code that mutates SheetMeta behind the engine's back must still call markAllDirty().

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 – –
Files main
Menu

Theme