SPB Git

Repository Public

COMPATIBILITY.md 27.2 KB 437 lines markdown

1# FunctionsNumeric — compatibility and validation23`FunctionsNumeric` registers **296 worksheet functions** into a `FunctionRegistry` (math & trigonometry,4conditional aggregates, statistics and distributions, information, engineering, database) through5`NumericFunctions.register(into:)`. Target semantics: Microsoft Excel (argument coercion, error codes, edge cases).67Nothing in this document comes from running Excel: Excel's behaviour is taken from Microsoft's function8documentation (the worked examples of the help pages are tests), LibreOffice is used as an executable9spreadsheet reference and scipy / numpy / mpmath / statsmodels as independent numerical references.1011## How the functions are validated1213| Source | Cases | What it vouches for |14|---|---:|---|15| LibreOffice 26.8 (headless recalculation of generated `.xlsx` workbooks, en-US locale) | 4 687 | spreadsheet semantics: coercion, blanks, criteria, ranges, array results, text results |16| scipy 1.17 + mpmath 1.3 (40 digits) | 5 440 | distributions, quantiles, special functions, Bessel, trigonometry, unit conversions (`scipy.constants`) |17| Python `decimal` | 4 082 | ROUND / ROUNDUP / ROUNDDOWN / TRUNC on the 15-digit decimal reading, MOD, QUOTIENT |18| numpy + scipy.stats | 606 | descriptive statistics, percentiles, ranks, correlation, t / F / z / χ² tests |19| statsmodels OLS + numpy.linalg + mpmath | 143 | LINEST / LOGEST statistics blocks, TREND, GROWTH, MINVERSE, MDETERM, MMULT |20| Python `cmath` | 420 | complex-number functions |21| **Reference corpus (JSON fixtures)** | **15 378** | `Tests/FunctionsNumericTests/Fixtures/*.json` |22| Microsoft documentation examples (hand-written) | ≈ 370 | `ExcelDocumentationTests.swift`: the examples of Excel's help pages, to the printed digits |23| Hand-written edge cases | ≈ 500 | `EdgeCaseTests.swift`: 201 Excel error codes, logical values, arrays, blanks, whole columns, hidden rows |2425Tolerances: numbers are compared with a relative tolerance of 1e-12 (scipy / mpmath / numpy fixtures; 1e-11 for26HYPGEOM.DIST and 2e-11 for BESSELJ / BESSELY, which rely on libm's `jn` / `yn`), exactly (tolerance 0) for decimal27rounding, 1e-9 to 1e-13 against LibreOffice (LibreOffice writes 15 significant digits; its distribution functions28are compared at 1e-8 because they are themselves less accurate), text and logical values exactly. For LibreOffice29cases whose result is an error only the fact that an error is returned is compared, because LibreOffice's error30codes differ from Excel's; the exact Excel codes are asserted by the hand-written tests.3132The generators are in `Tests/FunctionsNumericTests/Generators/` (`gen_libreoffice.py`, `gen_scipy.py`;33`overrides.py` holds the 51 cases where the fixture stores Excel's documented result instead of LibreOffice's;34`coverage.py` builds the table below). `FN_DUMP=<dir> swift test` writes every mismatch of a regenerated corpus.3536While building the corpus the references disagreed with each other in three places; each was arbitrated with37mpmath at 40–400 digits: scipy's `poisson.pmf` (4e-12 off for large means), `t.isf` / `f.isf` (up to 1e-8 off in38the tails) — the fixtures use the mpmath values.3940## Numerical methods4142- **ROUND family**: the number is read at 15 significant digits (which removes binary noise: 1.005 is43  1.00499999999999989…) and rounded on that decimal expansion, half away from zero. A fast path scales by an exact44  power of ten when the scaled value is far from a rounding boundary. CEILING / FLOOR / MROUND / EVEN / ODD snap a45  quotient that is an integer to 15 digits before rounding it (`CEILING(1.1,0.1)` is 1.1, not 1.2).46- **Distributions**: probability masses and densities use Loader's saddle-point expansion (`stirlerr` / `bd0`);47  the regularized incomplete gamma and beta functions use a power series or a continued fraction (modified Lentz)48  with the mass as prefactor; the normal quantile is Acklam's approximation polished by Halley steps on `erfc`;49  the t / χ² / F / beta / gamma quantiles use Brent's method on whichever tail is smaller, so both tails keep full50  relative accuracy. Measured accuracy against 40-digit references: ≤ 1e-12 relative over the test grid.51- **Linear algebra**: MINVERSE and MDETERM call LAPACK (`dgetrf`, `dgetri`) and LINEST / LOGEST / TREND / GROWTH52  the LAPACK SVD (`dgesvd`) of the centered design matrix, through a small C shim (`CNumericLAPACK`) compiled with53  `ACCELERATE_NEW_LAPACK`.54- **Bessel**: `jn` / `yn` of libm; I_n by its all-positive power series; K_n by the trapezoidal rule on55  ∫₀^∞ exp(−x·cosh t)·cosh(nt) dt (geometric convergence).5657## Known deviations from Excel5859Deliberate or unavoidable differences, and behaviours that could not be checked against a reference.6061**Rounding and arithmetic**62- ROUND / ROUNDUP / ROUNDDOWN / TRUNC apply the 15-digit rule uniformly (`ROUND(2.4999999999999996,0)` is 3). This63  matches Excel on every documented tie; Excel's behaviour on values that differ from a tie only beyond the 15th64  digit is not documented.65- MOD has no `#NUM!` limit on the size of the quotient. SIN, COS, TAN… accept arguments above 2^27 (Excel returns66  `#NUM!`).67- `COMBINA(0,0)` is 1; `FACTDOUBLE` of a negative number is `#NUM!` (as documented).68- LARGE / SMALL round a non-integer `k` up. Not verified (LibreOffice truncates for SMALL).6970**Criteria (COUNTIF, SUMIF, …IFS, database functions)**71- A numeric criterion with `=` / `<>` also matches numeric text (`COUNTIF(range,5)` counts `"5"`); inequalities72  only look at numeric cells; wildcards only match text cells. This follows Excel and differs from LibreOffice.73- Date criteria are understood as ISO (`2024-03-01`) or US (`3/1/2024`) dates only, independent of the locale.74- Database functions: a text criterion without operator matches as a prefix (`Dav` finds `David`), `="=Dave"` is75  exact. Computed criteria (a formula under a blank or unknown header) are not supported and return `#VALUE!`.7677**Statistics**78- PERCENTRANK* truncate the result to `significance` decimals (Excel's documented examples: 0.333, 0.666, 0.583);79  LibreOffice rounds to significant digits instead.80- MODE / MODE.SNGL return the first mode encountered; MODE.MULT lists the modes in order of first appearance.81- LINEST / LOGEST with collinear regressors return the minimum-norm least-squares solution with zero standard82  errors (Excel sets the coefficient of a redundant column to 0). With no degrees of freedom F is `#NUM!`.83  Non-numeric cells in the regression ranges are `#VALUE!`.84- Degrees of freedom of the t, χ² and F functions are truncated to integers (T.TEST type 3 keeps the fractional85  Welch degrees of freedom).86- Densities that are infinite at the edge of the support (`GAMMA.DIST(0,0.5,1,FALSE)`…) return `#NUM!`.87  `CHISQ.DIST(0,2,FALSE)` is 0.5 (LibreOffice: 0).8889**Engineering**90- BESSELJ / BESSELY / BESSELI / BESSELK are accurate to about 1e-11 or better. Excel's own results are only good to91  about 7 digits (its help page prints 0.329925829 for J₂(1.9) = 0.3299257277), so results differ from Excel92  beyond the 7th digit.93- CONVERT implements the Excel 2013+ unit table with the exact SI definitions. LibreOffice still uses the rounded94  factors of Excel 2003 for many units and is therefore only used for the exact-definition groups; everything else95  is checked against `scipy.constants`. Not independently validated: `u` (1.660538782e-24 g), `eV`96  (1.602176487e-19 J), `parsec` / `pc` (3.08567758128155e16 m), `mmHg` (133.322 Pa) — the values believed to be97  Excel's. Prefixes on squared and cubed metric units are raised to the power (`km2` = 1e6 m²).98- BITLSHIFT returns `#NUM!` when the result reaches 2^48 (not verified).99- Complex results are formatted with 15 significant digits like General cells; IMPOWER works in polar form as100  Excel does (`IMPOWER("2+3i",3)` is `-46+9.00000000000001i` in Excel; the last digit can differ).101102**Information**103- CELL supports `address`, `row`, `col`, `contents`, `type`; INFO supports `system`, `osversion`, `release`,104  `recalc`, `numfile`; other types return `#VALUE!`. SHEETS does not know 3-D references.105- TYPE returns 128 for a LAMBDA value. ERROR.TYPE returns 9 for `#SPILL!`, 14 for `#CALC!` and 15 for the ORIX106  extension `#CIRC!`.107- `T` is left to the text package.108109**SUBTOTAL / AGGREGATE**110- Rows hidden by a filter are always ignored by SUBTOTAL; AGGREGATE ignores hidden and filtered rows with options111  1, 3, 5, 7. Cells whose formula is itself a SUBTOTAL or AGGREGATE call (possibly negated or parenthesised) are112  skipped; a subtotal nested deeper inside another expression is not detected.113- Integration note: these functions read `SheetMeta.hiddenRows` / `filteredRows` at evaluation time and are **not**114  volatile. The core engine tracks the dependency on row visibility itself: every formula whose expression contains115  a SUBTOTAL or AGGREGATE call is recorded when it is registered (`CalcEngine.visibilityCells`), and116  `CalcEngine.editMeta` — as well as undo / redo of such a change — schedules those formulas and their dependents117  for recalculation whenever `hiddenRows` or `filteredRows` of a sheet changes (`markVisibilityDependents()`). The118  application has nothing to dirty by hand, provided row visibility is changed through `editMeta`; code that mutates119  `SheetMeta` behind the engine's back must still call `markAllDirty()`.120121**Where LibreOffice differs from Excel** (the fixture stores Excel's documented result — see `overrides.py`):122MROUND with mixed signs, `POWER(0,0)`, `POWER(-8,1/3)`, `COMBINA(n<k)`, `ROMAN(n,TRUE/FALSE)`, `ARABIC("-X")`,123`ATAN2(0,0)`, `LOGNORM.DIST(0,…)`, `EXPON.DIST(-1,…)`, BITAND / BITOR / BITXOR / BITLSHIFT with non-integers,124`DEC2HEX(-12.9)`, `ISNUMBER(TRUE)`, `TYPE(TRUE)`, `ERROR.TYPE(SQRT(-1))`, GEOMEAN with a zero, `MODE(4,4,1,1)`,125wildcards and numeric criteria in COUNTIF / SUMIF, mixing `i` and `j` in complex functions, DGET with several126matches, DSTDEV / DVAR without matches. LibreOffice has no logical cell type (TRUE is the number 1), so every127behaviour involving logical values inside ranges is covered by hand-written tests only.128129## Not implemented130131Everything requested is registered. Not provided: `T` (text package), computed database criteria, 3-D references in132SHEETS, the remaining CELL / INFO types.133134## Function table135136Columns: number of reference cases whose formula calls the function, by source. "Hand-written" counts the formulas137of `ExcelDocumentationTests.swift` and `EdgeCaseTests.swift`.138139| Function | Category | LibreOffice | Independent reference | Hand-written | Remark |140|---|---|---:|---|---:|---|141| `ACOS` | Trigonometry | 11 | scipy/mpmath 7 | 1 |  |142| `ACOSH` | Trigonometry | 11 | scipy/mpmath 9 | 2 |  |143| `ACOT` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |144| `ACOTH` | Trigonometry | 11 | scipy/mpmath 9 | 2 |  |145| `AGGREGATE` | Math | 183 | – | 17 | not volatile; recalculated when row visibility changes (see integration note) |146| `ARABIC` | Math | 13 | – | 3 | LO rejects '-X' and spaces |147| `ASIN` | Trigonometry | 11 | scipy/mpmath 7 | 1 |  |148| `ASINH` | Trigonometry | 11 | scipy/mpmath 9 | – |  |149| `ATAN` | Trigonometry | 11 | scipy/mpmath 9 | – |  |150| `ATAN2` | Trigonometry | 10 | – | 3 | LO returns 0 for (0,0) |151| `ATANH` | Trigonometry | 11 | scipy/mpmath 7 | 2 |  |152| `AVEDEV` | Statistical | 13 | numpy/scipy 6 | 1 |  |153| `AVERAGEA` | Statistical | 13 | numpy/scipy 6 | 1 |  |154| `AVERAGEIF` | Math | 57 | – | 2 |  |155| `AVERAGEIFS` | Statistical | 15 | – | 3 |  |156| `BASE` | Math | 12 | – | 5 |  |157| `BESSELI` | Engineering | – | scipy/mpmath 105 | 2 | exact (Excel itself ~7 digits) |158| `BESSELJ` | Engineering | – | scipy/mpmath 108 | 3 | exact (Excel itself ~7 digits) |159| `BESSELK` | Engineering | – | scipy/mpmath 87 | 2 | exact (Excel itself ~7 digits) |160| `BESSELY` | Engineering | – | scipy/mpmath 84 | 3 | exact (Excel itself ~7 digits) |161| `BETA.DIST` | Statistical | 8 | scipy/mpmath 288 | 5 |  |162| `BETA.INV` | Statistical | 3 | scipy/mpmath 128 | 2 |  |163| `BETADIST` | Statistical | 2 | scipy/mpmath 144 | – |  |164| `BETAINV` | Statistical | 1 | scipy/mpmath 64 | – |  |165| `BIN2DEC` | Engineering | 11 | – | 5 |  |166| `BIN2HEX` | Engineering | 20 | – | 3 |  |167| `BIN2OCT` | Engineering | 20 | – | 3 |  |168| `BINOM.DIST` | Statistical | 8 | scipy/mpmath 148 | 4 |  |169| `BINOM.DIST.RANGE` | Statistical | – | scipy/mpmath 20 | 2 | unknown to LibreOffice's xlsx import |170| `BINOM.INV` | Statistical | 5 | scipy/mpmath 70 | 2 |  |171| `BINOMDIST` | Statistical | 1 | scipy/mpmath 74 | – |  |172| `BITAND` | Engineering | 10 | – | 4 | LO truncates non-integers (Excel: #NUM!) |173| `BITLSHIFT` | Engineering | 9 | – | 2 | result >= 2^48 is #NUM! (unverified) |174| `BITOR` | Engineering | 10 | – | 2 | LO truncates non-integers |175| `BITRSHIFT` | Engineering | 9 | – | 2 |  |176| `BITXOR` | Engineering | 10 | – | 1 | LO truncates non-integers |177| `CEILING` | Math | 21 | – | 7 |  |178| `CEILING.MATH` | Math | 39 | – | 4 |  |179| `CEILING.PRECISE` | Math | 27 | – | 5 |  |180| `CELL` | Information | 7 | – | 12 | 5 info types |181| `CHIDIST` | Statistical | 1 | scipy/mpmath 99 | – |  |182| `CHIINV` | Statistical | 1 | scipy/mpmath 81 | – |  |183| `CHISQ.DIST` | Statistical | 6 | scipy/mpmath 198 | 4 |  |184| `CHISQ.DIST.RT` | Statistical | 3 | scipy/mpmath 99 | 1 |  |185| `CHISQ.INV` | Statistical | 4 | scipy/mpmath 81 | 4 |  |186| `CHISQ.INV.RT` | Statistical | 3 | scipy/mpmath 81 | 2 |  |187| `CHISQ.TEST` | Statistical | 3 | numpy/scipy 2 | 3 |  |188| `CHITEST` | Statistical | 1 | numpy/scipy 1 | – |  |189| `COMBIN` | Math | 12 | scipy/mpmath 8 | 3 |  |190| `COMBINA` | Math | 12 | scipy/mpmath 7 | 2 | LO differs for n < k and (0,0) |191| `COMPLEX` | Engineering | 11 | – | 6 |  |192| `CONFIDENCE` | Statistical | 1 | numpy/scipy 5 | 1 |  |193| `CONFIDENCE.NORM` | Statistical | 4 | numpy/scipy 5 | 4 |  |194| `CONFIDENCE.T` | Statistical | 3 | numpy/scipy 5 | 2 |  |195| `CONVERT` | Engineering | 223 | scipy/mpmath 511 | 8 | LO only for exact-definition units; u, eV, parsec, mmHg unverified |196| `CORREL` | Statistical | 10 | numpy/scipy 4 | 4 |  |197| `COS` | Trigonometry | 11 | scipy/mpmath 9 | – |  |198| `COSH` | Trigonometry | 11 | scipy/mpmath 9 | – |  |199| `COT` | Trigonometry | 11 | scipy/mpmath 9 | 2 |  |200| `COTH` | Trigonometry | 11 | scipy/mpmath 9 | 2 |  |201| `COUNTBLANK` | Statistical | 6 | – | 2 |  |202| `COUNTIF` | Statistical | 85 | – | 18 | numeric criterion matches numeric text; wildcards match text only |203| `COUNTIFS` | Statistical | 15 | – | 5 |  |204| `COVAR` | Statistical | 10 | numpy/scipy 4 | 1 |  |205| `COVARIANCE.P` | Statistical | 10 | numpy/scipy 4 | 1 |  |206| `COVARIANCE.S` | Statistical | 10 | numpy/scipy 4 | 2 |  |207| `CRITBINOM` | Statistical | 1 | scipy/mpmath 70 | – |  |208| `CSC` | Trigonometry | 11 | scipy/mpmath 9 | 2 |  |209| `CSCH` | Trigonometry | 11 | scipy/mpmath 9 | 2 |  |210| `DAVERAGE` | Database | 24 | – | 3 |  |211| `DCOUNT` | Database | 33 | – | 3 |  |212| `DCOUNTA` | Database | 33 | – | 5 |  |213| `DEC2BIN` | Engineering | 44 | – | 6 |  |214| `DEC2HEX` | Engineering | 42 | – | 5 | LO floors negative non-integers |215| `DEC2OCT` | Engineering | 40 | – | 3 |  |216| `DECIMAL` | Math | 7 | – | 4 |  |217| `DEGREES` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |218| `DELTA` | Engineering | 6 | – | 4 |  |219| `DEVSQ` | Statistical | 13 | numpy/scipy 6 | 1 |  |220| `DGET` | Database | 32 | – | 5 | LO ignores empty fields when counting matches |221| `DMAX` | Database | 24 | – | 2 |  |222| `DMIN` | Database | 24 | – | 1 |  |223| `DPRODUCT` | Database | 24 | – | 1 |  |224| `DSTDEV` | Database | 24 | – | 1 | LO returns 0 without matches |225| `DSTDEVP` | Database | 24 | – | 1 |  |226| `DSUM` | Database | 27 | – | 10 |  |227| `DVAR` | Database | 24 | – | 1 | LO returns 0 without matches |228| `DVARP` | Database | 24 | – | 1 |  |229| `ERF` | Engineering | 14 | scipy/mpmath 30 | 2 |  |230| `ERF.PRECISE` | Engineering | 7 | scipy/mpmath 15 | 1 |  |231| `ERFC` | Engineering | 7 | scipy/mpmath 15 | 1 |  |232| `ERFC.PRECISE` | Engineering | 7 | scipy/mpmath 15 | 1 |  |233| `ERROR.TYPE` | Information | 9 | – | 5 | ORIX extensions: #SPILL! 9, #CALC! 14, #CIRC! 15 |234| `EVEN` | Math | 8 | – | 5 |  |235| `EXP` | Math | 11 | scipy/mpmath 9 | 2 |  |236| `EXPON.DIST` | Statistical | 5 | scipy/mpmath 56 | 4 | x < 0 is #NUM! (LO: 0) |237| `EXPONDIST` | Statistical | 1 | scipy/mpmath 28 | – |  |238| `F.DIST` | Statistical | 6 | scipy/mpmath 180 | 4 |  |239| `F.DIST.RT` | Statistical | 2 | scipy/mpmath 90 | 1 |  |240| `F.INV` | Statistical | 2 | scipy/mpmath 72 | 1 |  |241| `F.INV.RT` | Statistical | 3 | scipy/mpmath 72 | 2 |  |242| `F.TEST` | Statistical | 2 | numpy/scipy 4 | 3 |  |243| `FACT` | Math | 11 | scipy/mpmath 8 | 5 |  |244| `FACTDOUBLE` | Math | 11 | scipy/mpmath 8 | 3 | negative is #NUM! (documentation) |245| `FDIST` | Statistical | 1 | scipy/mpmath 90 | – |  |246| `FINV` | Statistical | 1 | scipy/mpmath 72 | – |  |247| `FISHER` | Statistical | 7 | scipy/mpmath 7 | 2 |  |248| `FISHERINV` | Statistical | 7 | scipy/mpmath 7 | 1 |  |249| `FLOOR` | Math | 21 | – | 8 |  |250| `FLOOR.MATH` | Math | 39 | – | 4 |  |251| `FLOOR.PRECISE` | Math | 27 | – | 5 |  |252| `FORECAST` | Statistical | 14 | numpy/scipy 4 | 2 |  |253| `FORECAST.LINEAR` | Statistical | 12 | numpy/scipy 4 | 1 |  |254| `FORMULATEXT` | Lookup & Reference | 2 | – | 3 | en-US syntax |255| `FREQUENCY` | Statistical | 17 | – | 4 |  |256| `FTEST` | Statistical | 1 | numpy/scipy 4 | – |  |257| `GAMMA` | Statistical | 8 | scipy/mpmath 15 | 4 |  |258| `GAMMA.DIST` | Statistical | 6 | scipy/mpmath 126 | 4 |  |259| `GAMMA.INV` | Statistical | 4 | scipy/mpmath 63 | 2 |  |260| `GAMMADIST` | Statistical | 1 | scipy/mpmath 63 | – |  |261| `GAMMAINV` | Statistical | 1 | scipy/mpmath 63 | – |  |262| `GAMMALN` | Statistical | 5 | scipy/mpmath 10 | 3 |  |263| `GAMMALN.PRECISE` | Statistical | 2 | scipy/mpmath 10 | 1 |  |264| `GAUSS` | Statistical | 3 | scipy/mpmath 15 | 1 |  |265| `GCD` | Math | 8 | – | 8 |  |266| `GEOMEAN` | Statistical | 13 | numpy/scipy 4 | 3 | LO returns 0 when a value is 0 |267| `GESTEP` | Engineering | 5 | – | 5 |  |268| `GROWTH` | Statistical | 14 | statsmodels/numpy 3 | 3 |  |269| `HARMEAN` | Statistical | 13 | numpy/scipy 4 | 2 |  |270| `HEX2BIN` | Engineering | 26 | – | 4 |  |271| `HEX2DEC` | Engineering | 14 | – | 4 |  |272| `HEX2OCT` | Engineering | 26 | – | 3 |  |273| `HYPGEOM.DIST` | Statistical | 6 | scipy/mpmath 56 | 4 |  |274| `HYPGEOMDIST` | Statistical | 1 | scipy/mpmath 28 | – |  |275| `IMABS` | Engineering | 14 | Python cmath 10 | 4 |  |276| `IMAGINARY` | Engineering | 14 | Python cmath 10 | 5 |  |277| `IMARGUMENT` | Engineering | 14 | Python cmath 10 | 2 |  |278| `IMCONJUGATE` | Engineering | 14 | Python cmath 10 | 2 |  |279| `IMCOS` | Engineering | 14 | Python cmath 10 | 1 |  |280| `IMDIV` | Engineering | 28 | Python cmath 40 | 2 | LO allows mixing i and j |281| `IMEXP` | Engineering | 14 | Python cmath 10 | 1 |  |282| `IMLN` | Engineering | 14 | Python cmath 10 | 2 |  |283| `IMLOG10` | Engineering | 14 | Python cmath 10 | 1 |  |284| `IMLOG2` | Engineering | 14 | Python cmath 10 | 1 |  |285| `IMPOWER` | Engineering | 70 | Python cmath 60 | 2 |  |286| `IMPRODUCT` | Engineering | 49 | Python cmath 80 | 3 |  |287| `IMREAL` | Engineering | 14 | Python cmath 10 | 2 |  |288| `IMSIN` | Engineering | 14 | Python cmath 10 | 1 |  |289| `IMSQRT` | Engineering | 14 | Python cmath 10 | 3 |  |290| `IMSUB` | Engineering | 29 | Python cmath 40 | 2 | LO allows mixing i and j |291| `IMSUM` | Engineering | 49 | Python cmath 80 | 6 | LO allows mixing i and j |292| `INFO` | Information | – | – | 4 | 5 info types |293| `INT` | Math | 8 | – | 3 |  |294| `INTERCEPT` | Statistical | 10 | numpy/scipy 4 | 1 |  |295| `ISBLANK` | Information | 12 | – | 2 |  |296| `ISERR` | Information | 12 | – | 2 |  |297| `ISERROR` | Information | 12 | – | 2 |  |298| `ISEVEN` | Information | 10 | – | 3 |  |299| `ISFORMULA` | Information | 2 | – | 3 |  |300| `ISLOGICAL` | Information | 12 | – | 1 |  |301| `ISNA` | Information | 12 | – | 2 |  |302| `ISNONTEXT` | Information | 12 | – | 1 |  |303| `ISNUMBER` | Information | 12 | – | 3 | LO has no logical type |304| `ISO.CEILING` | Math | 27 | – | 6 |  |305| `ISODD` | Information | 10 | – | 2 |  |306| `ISOMITTED` | Information | – | – | 4 | unknown to LibreOffice |307| `ISREF` | Information | 4 | – | 4 |  |308| `ISTEXT` | Information | 12 | – | 1 |  |309| `KURT` | Statistical | 13 | numpy/scipy 6 | 2 |  |310| `LARGE` | Statistical | 12 | – | 7 | non-integer k rounded up (unverified) |311| `LCM` | Math | 7 | – | 4 |  |312| `LINEST` | Statistical | 83 | statsmodels/numpy 61 | 13 | collinear regressors: minimum-norm solution |313| `LN` | Math | 11 | scipy/mpmath 9 | 2 |  |314| `LOG` | Math | 10 | scipy/mpmath 9 | 4 |  |315| `LOG10` | Math | 11 | scipy/mpmath 9 | 2 |  |316| `LOGEST` | Statistical | 52 | statsmodels/numpy 42 | 3 | collinear regressors: minimum-norm solution |317| `LOGINV` | Statistical | 1 | scipy/mpmath 22 | – |  |318| `LOGNORM.DIST` | Statistical | 4 | scipy/mpmath 48 | 3 | x <= 0 is #NUM! (LO: 0) |319| `LOGNORM.INV` | Statistical | 2 | scipy/mpmath 22 | 2 |  |320| `LOGNORMDIST` | Statistical | 1 | scipy/mpmath 24 | – |  |321| `MAXA` | Statistical | 13 | – | 1 |  |322| `MAXIFS` | Statistical | 15 | – | 2 |  |323| `MDETERM` | Math | 7 | statsmodels/numpy 3 | 5 |  |324| `MEDIAN` | Statistical | 13 | numpy/scipy 6 | 8 |  |325| `MINA` | Statistical | 13 | – | 2 |  |326| `MINIFS` | Statistical | 15 | – | 1 |  |327| `MINVERSE` | Math | 23 | statsmodels/numpy 19 | 4 |  |328| `MMULT` | Math | 19 | statsmodels/numpy 9 | 4 |  |329| `MOD` | Math | 12 | Python decimal 11 | 6 | no #NUM! for huge quotients |330| `MODE` | Statistical | 16 | – | 1 | first mode encountered (LO: smallest) |331| `MODE.MULT` | Statistical | 7 | – | 2 |  |332| `MODE.SNGL` | Statistical | 13 | – | 1 | first mode encountered |333| `MROUND` | Math | 15 | – | 7 | LO differs on mixed signs (Excel: #NUM!) |334| `MULTINOMIAL` | Math | 6 | – | 3 |  |335| `MUNIT` | Math | 11 | – | 2 |  |336| `N` | Information | 14 | – | 4 |  |337| `NEGBINOM.DIST` | Statistical | 4 | scipy/mpmath 72 | 5 |  |338| `NEGBINOMDIST` | Statistical | 1 | scipy/mpmath 36 | – |  |339| `NORM.DIST` | Statistical | 6 | scipy/mpmath 30 | 4 |  |340| `NORM.INV` | Statistical | 5 | scipy/mpmath 22 | 4 |  |341| `NORM.S.DIST` | Statistical | 3 | scipy/mpmath 30 | 3 |  |342| `NORM.S.INV` | Statistical | 5 | scipy/mpmath 22 | 2 |  |343| `NORMDIST` | Statistical | 2 | scipy/mpmath 15 | – |  |344| `NORMINV` | Statistical | 1 | scipy/mpmath 22 | – |  |345| `NORMSDIST` | Statistical | 2 | scipy/mpmath 15 | – |  |346| `NORMSINV` | Statistical | 1 | scipy/mpmath 22 | – |  |347| `OCT2BIN` | Engineering | 20 | – | 3 |  |348| `OCT2DEC` | Engineering | 11 | – | 3 |  |349| `OCT2HEX` | Engineering | 20 | – | 2 |  |350| `ODD` | Math | 8 | – | 6 |  |351| `PEARSON` | Statistical | 10 | numpy/scipy 4 | 1 |  |352| `PERCENTILE` | Statistical | 42 | numpy/scipy 66 | 2 |  |353| `PERCENTILE.EXC` | Statistical | 42 | numpy/scipy 42 | 3 |  |354| `PERCENTILE.INC` | Statistical | 42 | numpy/scipy 66 | 1 |  |355| `PERCENTRANK` | Statistical | 33 | – | 3 | truncates decimals (LO rounds significant digits) |356| `PERCENTRANK.EXC` | Statistical | 33 | – | 3 | truncates decimals (LO rounds significant digits) |357| `PERCENTRANK.INC` | Statistical | 33 | numpy/scipy 36 | 4 | truncates decimals (LO rounds significant digits) |358| `PERMUT` | Statistical | 12 | scipy/mpmath 6 | 3 |  |359| `PERMUTATIONA` | Statistical | 12 | scipy/mpmath 6 | 2 |  |360| `PHI` | Statistical | 3 | scipy/mpmath 15 | 1 |  |361| `PI` | Math | 1 | – | 1 |  |362| `POISSON` | Statistical | 1 | scipy/mpmath 49 | – |  |363| `POISSON.DIST` | Statistical | 6 | scipy/mpmath 98 | 4 |  |364| `POWER` | Math | 10 | – | 6 | LO differs for 0^0 and negative^fraction |365| `PROB` | Statistical | 7 | – | 4 |  |366| `PRODUCT` | Math | 4 | numpy/scipy 6 | 5 |  |367| `QUARTILE` | Statistical | 24 | – | 2 |  |368| `QUARTILE.EXC` | Statistical | 24 | numpy/scipy 18 | 3 |  |369| `QUARTILE.INC` | Statistical | 24 | numpy/scipy 30 | 1 |  |370| `QUOTIENT` | Math | 12 | Python decimal 11 | 4 |  |371| `RADIANS` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |372| `RANDARRAY` | Math | – | – | 7 | random: shape, range, integrality |373| `RANDBETWEEN` | Math | – | – | 4 | random: range and integrality |374| `RANK` | Statistical | 28 | – | 3 |  |375| `RANK.AVG` | Statistical | 28 | numpy/scipy 36 | 2 |  |376| `RANK.EQ` | Statistical | 28 | numpy/scipy 36 | 2 |  |377| `ROMAN` | Math | 108 | – | 11 | LO differs for logical form |378| `ROUND` | Math | 100 | Python decimal 1015 | 32 | 15-digit decimal rule |379| `ROUNDDOWN` | Math | 100 | Python decimal 1015 | 6 | 15-digit decimal rule |380| `ROUNDUP` | Math | 100 | Python decimal 1015 | 7 | 15-digit decimal rule |381| `RSQ` | Statistical | 10 | numpy/scipy 4 | 1 |  |382| `SEC` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |383| `SECH` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |384| `SERIESSUM` | Math | 4 | – | 3 |  |385| `SHEET` | Information | 3 | – | 4 |  |386| `SHEETS` | Information | 1 | – | 2 |  |387| `SIGN` | Math | 11 | – | 1 |  |388| `SIN` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |389| `SINH` | Trigonometry | 11 | scipy/mpmath 9 | 1 |  |390| `SKEW` | Statistical | 13 | numpy/scipy 6 | 4 |  |391| `SKEW.P` | Statistical | 13 | numpy/scipy 6 | 1 |  |392| `SLOPE` | Statistical | 10 | numpy/scipy 4 | 2 |  |393| `SMALL` | Statistical | 12 | – | 3 | non-integer k rounded up (unverified; LO truncates) |394| `SQRT` | Math | 12 | scipy/mpmath 9 | 5 |  |395| `SQRTPI` | Math | 11 | scipy/mpmath 9 | 2 |  |396| `STANDARDIZE` | Statistical | 4 | – | 2 |  |397| `STDEV` | Statistical | 13 | numpy/scipy 6 | 3 |  |398| `STDEV.P` | Statistical | 13 | numpy/scipy 6 | 1 |  |399| `STDEV.S` | Statistical | 13 | numpy/scipy 6 | – |  |400| `STDEVA` | Statistical | 13 | – | 1 |  |401| `STDEVP` | Statistical | 13 | numpy/scipy 6 | – |  |402| `STDEVPA` | Statistical | 13 | numpy/scipy 6 | 1 |  |403| `STEYX` | Statistical | 10 | numpy/scipy 4 | 2 |  |404| `SUBTOTAL` | Math | 51 | – | 19 | not volatile; recalculated when row visibility changes (see integration note) |405| `SUMIF` | Math | 78 | – | 8 | same criteria rules as COUNTIF |406| `SUMIFS` | Math | 16 | – | 4 |  |407| `SUMPRODUCT` | Math | 11 | numpy/scipy 4 | 5 |  |408| `SUMSQ` | Math | 4 | numpy/scipy 6 | 5 |  |409| `SUMX2MY2` | Math | 3 | numpy/scipy 4 | 2 |  |410| `SUMX2PY2` | Math | 2 | numpy/scipy 4 | 2 |  |411| `SUMXMY2` | Math | 2 | numpy/scipy 4 | 2 |  |412| `T.DIST` | Statistical | 7 | scipy/mpmath 192 | 3 |  |413| `T.DIST.2T` | Statistical | 4 | scipy/mpmath 56 | 2 |  |414| `T.DIST.RT` | Statistical | 3 | scipy/mpmath 96 | 1 |  |415| `T.INV` | Statistical | 6 | scipy/mpmath 96 | 3 |  |416| `T.INV.2T` | Statistical | 4 | scipy/mpmath 96 | 2 |  |417| `T.TEST` | Statistical | 14 | numpy/scipy 24 | 4 |  |418| `TAN` | Trigonometry | 11 | scipy/mpmath 9 | – |  |419| `TANH` | Trigonometry | 11 | scipy/mpmath 9 | – |  |420| `TDIST` | Statistical | 4 | scipy/mpmath 112 | 2 |  |421| `TINV` | Statistical | 1 | scipy/mpmath 96 | 1 |  |422| `TREND` | Statistical | 25 | statsmodels/numpy 6 | 3 |  |423| `TRIMMEAN` | Statistical | 20 | numpy/scipy 24 | 3 |  |424| `TRUNC` | Math | 108 | Python decimal 1015 | 3 | 15-digit decimal rule |425| `TTEST` | Statistical | 6 | numpy/scipy 8 | – |  |426| `TYPE` | Information | 13 | – | 8 | LO has no logical type |427| `VAR` | Statistical | 13 | numpy/scipy 6 | 1 |  |428| `VAR.P` | Statistical | 13 | numpy/scipy 6 | – |  |429| `VAR.S` | Statistical | 13 | numpy/scipy 6 | 1 |  |430| `VARA` | Statistical | 13 | numpy/scipy 6 | 2 |  |431| `VARP` | Statistical | 13 | numpy/scipy 6 | – |  |432| `VARPA` | Statistical | 13 | – | 1 |  |433| `WEIBULL` | Statistical | 1 | scipy/mpmath 35 | – |  |434| `WEIBULL.DIST` | Statistical | 4 | scipy/mpmath 70 | 3 |  |435| `Z.TEST` | Statistical | 3 | numpy/scipy 12 | 3 |  |436| `ZTEST` | Statistical | 1 | – | – |  |437
Files main
Menu

Theme