Function catalogue
Every worksheet function available in ORIX Sheet formulas, grouped by category, with its signature, a one-line summary, the package that implements it, the reference sources its tests are checked against, and its known deviations from Microsoft Excel.
- The language itself (operators, references, lifting over arrays,
LET/LAMBDA) is described in ../architecture/formula-engine.md. - Number semantics and the cross-cutting numeric deviations are in ../mathematics/numerical-precision.md; financial conventions in ../mathematics/financial-conventions.md.
- How the reference corpora are built and their limits are in ../mathematics/reference-tests.md.
- File-format and application-level compatibility is in excel.md.
How this catalogue is produced
The tables at the end of this file are generated, not typed:
- A throw-away Swift executable linked against
Packages/SheetEnginecallsORIXEngine.bootstrap()and dumpsFunctionRegistry.standard— name, category, signature, summary, argument bounds, volatility and the library that registers each name — tofunction-registry.json(run on 2026-11-21). make_formula_catalogue.pymerges that dump with the per-function information of the package documents (Packages/FunctionsNumeric/COMPATIBILITY.md,Packages/FunctionsText/COMPATIBILITY.mdand its fixture file,Packages/FunctionsLookup/COMPATIBILITY.md) and rewrites the section between the twoGENERATED CATALOGUEmarkers. The script stops if a FunctionsNumeric or FunctionsLookup function has no row in its package document, if the FunctionsNumeric table and the registry list different names, or if a name is registered by two libraries, so the catalogue cannot silently drift from the registry. (FunctionsText has no per-function table; its counts are computed from thesrctag of each case offunction_references.json.)python3 docs/compatibility/make_formula_catalogue.py --checkverifies that this file is up to date with the JSON dump and the package documents.
The dump itself must be regenerated (step 1) when functions are added; the procedure is in the header of the script.
Counts
| Category | Registered functions | Special forms counted in this category | ORIXEngine.functionCountsByCategory() |
|---|---|---|---|
| Database | 12 | – | 12 |
| Date & Time | 25 | – | 25 |
| Dynamic Array | 22 | – | 22 |
| Engineering | 46 | – | 46 |
| Financial | 49 | – | 49 |
| Information | 21 | – | 21 |
| Logical | 6 | 7 (IF, IFS, IFERROR, IFNA, SWITCH, LET, LAMBDA) |
13 |
| Lookup & Reference | 18 | 1 (CHOOSE) |
19 |
| Math | 58 | – | 58 |
| Statistical | 144 | – | 144 |
| Text | 41 | – | 41 |
| Trigonometry | 23 | – | 23 |
| Total | 465 | 8 | 473 |
Source: docs/compatibility/function-registry.json (registry dump and the output of ORIXEngine.functionCountsByCategory(), 2026-11-21); the split of the eight special forms between Logical and Lookup & Reference is hard-coded in Packages/SheetEngine/Sources/SheetEngine/Bootstrap.swift. docs/benchmarks/engine.txt reports the same total ("functions: 473").
Categories are those declared in the code (FunctionCategory), which do not always coincide with Excel's
function-library grouping (for example AVERAGEIF, SUMIF and SUBTOTAL are in Math, COUNTIF in
Statistical, FORMULATEXT in Lookup & Reference, and the dynamic-array functions have their own category).
Reading the validation column
The column gives, per function, the number of reference cases whose formula calls it, by source, exactly as recorded by the package that owns the function. The vocabulary differs slightly between packages:
| Label | Meaning | Used by |
|---|---|---|
| LibreOffice n, LO n | formula recalculated by headless LibreOffice 26.8 on a generated .xlsx |
all three packages |
| scipy/mpmath n, numpy/scipy n, statsmodels/numpy n, Python cmath n, Python decimal n | independent numerical reference | FunctionsNumeric |
| hand-written n | formulas in ExcelDocumentationTests.swift and EdgeCaseTests.swift (Microsoft help-page examples and Excel-semantics edge cases) |
FunctionsNumeric |
| LibreOffice+Python n | LibreOffice result that an independent Python computation confirms | FunctionsText, FunctionsLookup |
| Python n, PY n | Python reference only (the function does not exist in LibreOffice, or LibreOffice is known to differ) | FunctionsText, FunctionsLookup |
| Excel-documented value n | LibreOffice deviates from Excel's documented behaviour; the fixture stores the Excel value | FunctionsText |
| Excel-computed data n | values computed by Microsoft Excel, from the public test data of the ExcelFinancialFunctions project (Apache-2.0) | FunctionsLookup (financial) |
| DOC | worked example of Microsoft's function reference | FunctionsLookup |
| HW | hand-written Excel-semantics tests | FunctionsLookup |
| "row shared by …" | the package document gives one figure for a group of functions; it is repeated on each | FunctionsLookup |
Source: Packages/FunctionsNumeric/COMPATIBILITY.md, Packages/FunctionsText/COMPATIBILITY.md, Packages/FunctionsLookup/COMPATIBILITY.md.
No count in this file comes from running Microsoft Excel. The only Excel-computed numbers are those of the
public financial data set. The 24 functions registered by SheetCore itself have no external reference
corpus: they are covered by the unit tests of Packages/SheetCore/Tests/SheetCoreTests/CoreTests.swift
(and are exercised indirectly by the formulas of every other corpus, which is not counted). Test totals per
package are produced by Scripts/test-all.sh into docs/reports/tests.json.
Special forms
Eight names are evaluated by the evaluator instead of the registry, because they evaluate their arguments
lazily or bind names (EvalContext.evalCall in Packages/SheetCore/Sources/SheetCore/Engine/Evaluator.swift).
They have no FunctionSpec, hence no registered signature; the signatures below are written from the code.
| Form | Arguments accepted | Behaviour and deviations |
|---|---|---|
IF(logical_test, [value_if_true], [value_if_false]) |
2–3 | Lazy on a scalar test; a reference result stays a reference. Omitted third argument → FALSE; an empty placeholder → 0. With an array test both branches are evaluated and combined element-wise. |
IFERROR(value, value_if_error) |
exactly 2 | Second argument evaluated only when needed; element-wise on arrays. |
IFNA(value, value_if_na) |
exactly 2 | As IFERROR, for #N/A only. |
IFS(test1, value1, [test2, value2], …) |
even, ≥ 2 | First true test wins; none → #N/A. Tests are read as scalars (top-left element of an array): no element-wise form. |
SWITCH(expression, value1, result1, …, [default]) |
≥ 3 | Text compared case-insensitively; no match and no default → #N/A. Scalar subject only. |
CHOOSE(index_num, value1, [value2], …) |
≥ 2 | Scalar index: lazy, references survive. Array index: element-wise. Out of range → #VALUE!. |
LET(name1, value1, [name2, value2], …, calculation) |
odd, ≥ 3 | Sequential binding; a name must be a plain identifier (#NAME? otherwise). |
LAMBDA([parameter1, …], calculation) |
≥ 1 | Closure over the current LET/LAMBDA environment. Fewer arguments than parameters is allowed (missing ones test TRUE with ISOMITTED); more → #VALUE!. Call depth limited to 400 (#NUM!). A lambda as a cell result shows #CALC!. |
Source: Packages/SheetCore/Sources/SheetCore/Engine/Evaluator.swift; validation: CoreTests.swift (SheetCore) — no external reference corpus, and LibreOffice has no LAMBDA.
A wrong number of arguments to a special form or to a registered function is #VALUE! at evaluation time,
not a syntax error at entry.
Cross-cutting behaviours
These apply to many functions and are not repeated on every row.
- Volatile functions (recalculated on every pass):
CELL,INDIRECT,INFO,NOW,OFFSET,RAND,RANDARRAY,RANDBETWEEN,TODAY.SUBTOTALandAGGREGATEare not volatile; the engine re-evaluates them when row visibility changes (see ../architecture/calculation-graph.md). - Lifting. A function whose parameter is declared scalar is applied element-wise when given an array or a multi-cell range, and the result spills. Functions marked as taking whole ranges do their own handling.
- Whole-column references are clamped to the used range when a function materializes them
(
EvalContext.effectiveRange); aggregates visit only stored cells.ROWS(A:A)is still 1 048 576. - Dates given as text are parsed by the FunctionsText date functions in month/day/year or ISO order
whatever the locale; the financial functions return
#VALUE!for text dates. - Text and date functions use the en-US format locale for month names, separators and
TEXTrendering:ORIXEngine.bootstrap()registers them with the defaultlocale: .enUS. - Function names are English only and are not translated in any interface language.
- Error codes for invalid arguments follow Excel's documentation. Against LibreOffice only "is an error" is compared, because its error codes differ; exact codes are asserted by hand-written tests.
#CIRC!,#SPILL!,#CALC!haveERROR.TYPEcodes 15, 9 and 14.
Excel functions that are not available
Formulas that call a name absent from the registry evaluate to #NAME?. The package documents declare
these as out of scope: ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, AMORDEGRC, AMORLINC,
GETPIVOTDATA (Packages/FunctionsLookup/COMPATIBILITY.md), computed criteria in the database functions,
3-D references in SHEETS, and the CELL / INFO types other than the five each supports
(Packages/FunctionsNumeric/COMPATIBILITY.md).
In addition, the following Excel function names were checked against the registry dump on 2026-11-21 and
are not registered: CUBEVALUE, CUBEMEMBER, CUBESET (the cube family), WEBSERVICE, FILTERXML,
IMAGE, STOCKHISTORY, RTD, FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY,
FORECAST.ETS.STAT, GROUPBY, PIVOTBY, PERCENTOF, TRIMRANGE, TRANSLATE, DETECTLANGUAGE,
IMCOSH, IMSINH, IMTAN, IMSEC, IMCSC, IMCOT, IMSECH, IMCSCH, FINDB, SEARCHB, REPLACEB,
ASC, DBCS, JIS, PHONETIC, BAHTTEXT, EUROCONVERT, CALL, REGISTER.ID, SQL.REQUEST. This list
is a spot check of well-known names, not an exhaustive difference with Excel's function list: no
machine-readable list of Excel's functions is kept in the repository.
ISOMITTED is registered by FunctionsNumeric (Information.swift) and is in the catalogue below.
Packages/FunctionsLookup/COMPATIBILITY.md says the same: the function is not part of that package but
exists in the application.
Catalogue
Registry dump: 465 registered functions + 8 special forms = 473 (ORIXEngine.functionCount).
| Origin package | Registered functions |
|---|---|
SheetCore |
24 |
FunctionsNumeric |
296 |
FunctionsText |
64 |
FunctionsLookup |
81 |
Source: docs/compatibility/function-registry.json (each library registered into a fresh FunctionRegistry).
Math (58 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
ABS |
ABS(number) |
Absolute value. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
AGGREGATE |
AGGREGATE(function_num, options, ref1, [ref2 or k], …) |
Aggregate of a list with options to ignore hidden rows, error values and nested subtotals. | Numeric | LibreOffice 183, hand-written 17 | not volatile: see integration note |
ARABIC |
ARABIC(text) |
Converts a Roman numeral to Arabic. | Numeric | LibreOffice 13, hand-written 3 | LO rejects '-X' and spaces |
AVERAGEIF |
AVERAGEIF(range, criteria, [average_range]) |
Averages the cells that meet a criterion. | Numeric | LibreOffice 57, hand-written 2 | |
BASE |
BASE(number, radix, [min_length]) |
Converts a number to text in a given base. | Numeric | LibreOffice 12, hand-written 5 | |
CEILING |
CEILING(number, significance) |
Rounds a number up to the nearest multiple of significance. | Numeric | LibreOffice 21, hand-written 7 | |
CEILING.MATH |
CEILING.MATH(number, [significance], [mode]) |
Rounds a number up to the nearest integer or multiple; mode controls negative numbers. | Numeric | LibreOffice 39, hand-written 4 | |
CEILING.PRECISE |
CEILING.PRECISE(number, [significance]) |
Rounds a number up (toward +∞) to the nearest multiple, whatever its sign. | Numeric | LibreOffice 27, hand-written 5 | |
COMBIN |
COMBIN(number, number_chosen) |
Number of combinations without repetition. | Numeric | LibreOffice 12, scipy/mpmath 8, hand-written 3 | |
COMBINA |
COMBINA(number, number_chosen) |
Number of combinations with repetitions. | Numeric | LibreOffice 12, scipy/mpmath 7, hand-written 2 | LO differs for n < k and (0,0) |
DECIMAL |
DECIMAL(text, radix) |
Converts text in a given base to a decimal number. | Numeric | LibreOffice 7, hand-written 4 | |
EVEN |
EVEN(number) |
Rounds a number away from zero to the nearest even integer. | Numeric | LibreOffice 8, hand-written 5 | |
EXP |
EXP(number) |
e raised to the power of a number. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
FACT |
FACT(number) |
Factorial of a number. | Numeric | LibreOffice 11, scipy/mpmath 8, hand-written 5 | |
FACTDOUBLE |
FACTDOUBLE(number) |
Double factorial of a number. | Numeric | LibreOffice 11, scipy/mpmath 8, hand-written 3 | negative is #NUM! (documentation) |
FLOOR |
FLOOR(number, significance) |
Rounds a number down to the nearest multiple of significance. | Numeric | LibreOffice 21, hand-written 8 | |
FLOOR.MATH |
FLOOR.MATH(number, [significance], [mode]) |
Rounds a number down to the nearest integer or multiple; mode controls negative numbers. | Numeric | LibreOffice 39, hand-written 4 | |
FLOOR.PRECISE |
FLOOR.PRECISE(number, [significance]) |
Rounds a number down (toward −∞) to the nearest multiple, whatever its sign. | Numeric | LibreOffice 27, hand-written 5 | |
GCD |
GCD(number1, [number2], …) |
Greatest common divisor. | Numeric | LibreOffice 8, hand-written 8 | |
INT |
INT(number) |
Rounds a number down to the nearest integer. | Numeric | LibreOffice 8, hand-written 3 | |
ISO.CEILING |
ISO.CEILING(number, [significance]) |
Rounds a number up (toward +∞) to the nearest multiple, whatever its sign. | Numeric | LibreOffice 27, hand-written 6 | |
LCM |
LCM(number1, [number2], …) |
Least common multiple. | Numeric | LibreOffice 7, hand-written 4 | |
LN |
LN(number) |
Natural logarithm. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
LOG |
LOG(number, [base]) |
Logarithm of a number to a base (10 by default). | Numeric | LibreOffice 10, scipy/mpmath 9, hand-written 4 | |
LOG10 |
LOG10(number) |
Base-10 logarithm. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
MDETERM |
MDETERM(array) |
Determinant of a square matrix. | Numeric | LibreOffice 7, statsmodels/numpy 3, hand-written 5 | |
MINVERSE |
MINVERSE(array) |
Inverse of a square matrix. | Numeric | LibreOffice 23, statsmodels/numpy 19, hand-written 4 | |
MMULT |
MMULT(array1, array2) |
Matrix product of two arrays. | Numeric | LibreOffice 19, statsmodels/numpy 9, hand-written 4 | |
MOD |
MOD(number, divisor) |
Remainder after division; the result has the sign of the divisor. | Numeric | LibreOffice 12, Python decimal 11, hand-written 6 | no #NUM! for huge quotients |
MROUND |
MROUND(number, multiple) |
Rounds a number to the nearest multiple. | Numeric | LibreOffice 15, hand-written 7 | LO differs on mixed signs (Excel: #NUM!) |
MULTINOMIAL |
MULTINOMIAL(number1, [number2], …) |
Multinomial of a set of numbers: (Σn)! / Πn!. | Numeric | LibreOffice 6, hand-written 3 | |
MUNIT |
MUNIT(dimension) |
Identity matrix of a given dimension. | Numeric | LibreOffice 11, hand-written 2 | |
ODD |
ODD(number) |
Rounds a number away from zero to the nearest odd integer. | Numeric | LibreOffice 8, hand-written 6 | |
PI |
PI() |
The constant π. | Numeric | LibreOffice 1, hand-written 1 | |
POWER |
POWER(number, power) |
Number raised to a power. | Numeric | LibreOffice 10, hand-written 6 | LO differs for 0^0 and negative^fraction |
PRODUCT |
PRODUCT(number1, [number2], …) |
Multiplies its arguments. | Numeric | LibreOffice 4, numpy/scipy 6, hand-written 5 | |
QUOTIENT |
QUOTIENT(numerator, denominator) |
Integer portion of a division. | Numeric | LibreOffice 12, Python decimal 11, hand-written 4 | |
RAND |
RAND() |
Uniform random number in [0, 1). | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Volatile. SystemRandomNumberGenerator; not seedable. |
RANDARRAY |
RANDARRAY([rows], [columns], [min], [max], [whole_number]) |
Array of random numbers. Volatile. | Numeric | hand-written 7 | Volatile. random: shape, range, integrality |
RANDBETWEEN |
RANDBETWEEN(bottom, top) |
Random integer between two bounds. Volatile. | Numeric | hand-written 4 | Volatile. random: range and integrality |
ROMAN |
ROMAN(number, [form]) |
Converts an Arabic numeral to Roman, as text. | Numeric | LibreOffice 108, hand-written 11 | LO differs for logical form |
ROUND |
ROUND(number, num_digits) |
Rounds a number to a specified number of digits. | Numeric | LibreOffice 100, Python decimal 1015, hand-written 32 | 15-digit decimal rule |
ROUNDDOWN |
ROUNDDOWN(number, num_digits) |
Rounds a number down, toward zero. | Numeric | LibreOffice 100, Python decimal 1015, hand-written 6 | 15-digit decimal rule |
ROUNDUP |
ROUNDUP(number, num_digits) |
Rounds a number up, away from zero. | Numeric | LibreOffice 100, Python decimal 1015, hand-written 7 | 15-digit decimal rule |
SERIESSUM |
SERIESSUM(x, n, m, coefficients) |
Sum of a power series a₁xⁿ + a₂xⁿ⁺ᵐ + …. | Numeric | LibreOffice 4, hand-written 3 | |
SIGN |
SIGN(number) |
Sign of a number: 1, 0 or −1. | Numeric | LibreOffice 11, hand-written 1 | |
SQRT |
SQRT(number) |
Positive square root. | Numeric | LibreOffice 12, scipy/mpmath 9, hand-written 5 | |
SQRTPI |
SQRTPI(number) |
Square root of (number × π). | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
SUBTOTAL |
SUBTOTAL(function_num, ref1, [ref2], …) |
Subtotal of a list (1–11 include manually hidden rows, 101–111 ignore them); filtered rows and nested subtotals are always ignored. | Numeric | LibreOffice 51, hand-written 19 | not volatile: see integration note |
SUM |
SUM(number1, [number2], …) |
Adds its arguments. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Neumaier compensated summation. |
SUMIF |
SUMIF(range, criteria, [sum_range]) |
Adds the cells that meet a criterion. | Numeric | LibreOffice 78, hand-written 8 | same criteria rules as COUNTIF |
SUMIFS |
SUMIFS(sum_range, criteria_range1, criteria1, …) |
Adds the cells that meet several criteria. | Numeric | LibreOffice 16, hand-written 4 | |
SUMPRODUCT |
SUMPRODUCT(array1, [array2], …) |
Sum of the products of corresponding array elements. | Numeric | LibreOffice 11, numpy/scipy 4, hand-written 5 | |
SUMSQ |
SUMSQ(number1, [number2], …) |
Sum of the squares of its arguments. | Numeric | LibreOffice 4, numpy/scipy 6, hand-written 5 | |
SUMX2MY2 |
SUMX2MY2(array_x, array_y) |
Sum of the differences of squares of corresponding values. | Numeric | LibreOffice 3, numpy/scipy 4, hand-written 2 | |
SUMX2PY2 |
SUMX2PY2(array_x, array_y) |
Sum of the sums of squares of corresponding values. | Numeric | LibreOffice 2, numpy/scipy 4, hand-written 2 | |
SUMXMY2 |
SUMXMY2(array_x, array_y) |
Sum of the squares of differences of corresponding values. | Numeric | LibreOffice 2, numpy/scipy 4, hand-written 2 | |
TRUNC |
TRUNC(number, [num_digits]) |
Truncates a number to an integer or to a number of digits. | Numeric | LibreOffice 108, Python decimal 1015, hand-written 3 | 15-digit decimal rule |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Trigonometry (23 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
ACOS |
ACOS(number) |
Arccosine, in radians. | Numeric | LibreOffice 11, scipy/mpmath 7, hand-written 1 | |
ACOSH |
ACOSH(number) |
Inverse hyperbolic cosine. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
ACOT |
ACOT(number) |
Arccotangent, in radians (0 to π). | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
ACOTH |
ACOTH(number) |
Inverse hyperbolic cotangent. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
ASIN |
ASIN(number) |
Arcsine, in radians. | Numeric | LibreOffice 11, scipy/mpmath 7, hand-written 1 | |
ASINH |
ASINH(number) |
Inverse hyperbolic sine. | Numeric | LibreOffice 11, scipy/mpmath 9 | |
ATAN |
ATAN(number) |
Arctangent, in radians. | Numeric | LibreOffice 11, scipy/mpmath 9 | |
ATAN2 |
ATAN2(x_num, y_num) |
Arctangent from x and y coordinates, in radians (−π to π). | Numeric | LibreOffice 10, hand-written 3 | LO returns 0 for (0,0) |
ATANH |
ATANH(number) |
Inverse hyperbolic tangent. | Numeric | LibreOffice 11, scipy/mpmath 7, hand-written 2 | |
COS |
COS(number) |
Cosine of an angle in radians. | Numeric | LibreOffice 11, scipy/mpmath 9 | |
COSH |
COSH(number) |
Hyperbolic cosine. | Numeric | LibreOffice 11, scipy/mpmath 9 | |
COT |
COT(number) |
Cotangent of an angle in radians. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
COTH |
COTH(number) |
Hyperbolic cotangent. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
CSC |
CSC(number) |
Cosecant of an angle in radians. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
CSCH |
CSCH(number) |
Hyperbolic cosecant. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 2 | |
DEGREES |
DEGREES(number) |
Converts radians to degrees. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
RADIANS |
RADIANS(number) |
Converts degrees to radians. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
SEC |
SEC(number) |
Secant of an angle in radians. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
SECH |
SECH(number) |
Hyperbolic secant. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
SIN |
SIN(number) |
Sine of an angle in radians. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
SINH |
SINH(number) |
Hyperbolic sine. | Numeric | LibreOffice 11, scipy/mpmath 9, hand-written 1 | |
TAN |
TAN(number) |
Tangent of an angle in radians. | Numeric | LibreOffice 11, scipy/mpmath 9 | |
TANH |
TANH(number) |
Hyperbolic tangent. | Numeric | LibreOffice 11, scipy/mpmath 9 |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Statistical (144 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
AVEDEV |
AVEDEV(number1, [number2], …) |
Average of the absolute deviations from the mean. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
AVERAGE |
AVERAGE(number1, [number2], …) |
Arithmetic mean of its arguments. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Compensated sum divided by the count; no numbers gives #DIV/0!. |
AVERAGEA |
AVERAGEA(value1, [value2], …) |
Average of its arguments, counting text as 0 and logical values. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
AVERAGEIFS |
AVERAGEIFS(average_range, criteria_range1, criteria1, …) |
Averages the cells that meet several criteria. | Numeric | LibreOffice 15, hand-written 3 | |
BETA.DIST |
BETA.DIST(x, alpha, beta, cumulative, [A], [B]) |
Beta distribution (density or cumulative). | Numeric | LibreOffice 8, scipy/mpmath 288, hand-written 5 | |
BETA.INV |
BETA.INV(probability, alpha, beta, [A], [B]) |
Inverse of the cumulative beta distribution. | Numeric | LibreOffice 3, scipy/mpmath 128, hand-written 2 | |
BETADIST |
BETADIST(x, alpha, beta, [A], [B]) |
Cumulative beta distribution (compatibility). | Numeric | LibreOffice 2, scipy/mpmath 144 | |
BETAINV |
BETAINV(probability, alpha, beta, [A], [B]) |
Inverse of the cumulative beta distribution. | Numeric | LibreOffice 1, scipy/mpmath 64 | |
BINOM.DIST |
BINOM.DIST(number_s, trials, probability_s, cumulative) |
Binomial distribution (mass or cumulative). | Numeric | LibreOffice 8, scipy/mpmath 148, hand-written 4 | |
BINOM.DIST.RANGE |
BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]) |
Probability of a number of successes between two bounds for a binomial distribution. | Numeric | scipy/mpmath 20, hand-written 2 | unknown to LibreOffice's xlsx import |
BINOM.INV |
BINOM.INV(trials, probability_s, alpha) |
Smallest number of successes whose cumulative binomial probability reaches alpha. | Numeric | LibreOffice 5, scipy/mpmath 70, hand-written 2 | |
BINOMDIST |
BINOMDIST(number_s, trials, probability_s, cumulative) |
Binomial distribution (mass or cumulative). | Numeric | LibreOffice 1, scipy/mpmath 74 | |
CHIDIST |
CHIDIST(x, deg_freedom) |
Right-tailed probability of the chi-squared distribution. | Numeric | LibreOffice 1, scipy/mpmath 99 | |
CHIINV |
CHIINV(probability, deg_freedom) |
Inverse of the right-tailed chi-squared distribution. | Numeric | LibreOffice 1, scipy/mpmath 81 | |
CHISQ.DIST |
CHISQ.DIST(x, deg_freedom, cumulative) |
Chi-squared distribution (density or cumulative). | Numeric | LibreOffice 6, scipy/mpmath 198, hand-written 4 | |
CHISQ.DIST.RT |
CHISQ.DIST.RT(x, deg_freedom) |
Right-tailed probability of the chi-squared distribution. | Numeric | LibreOffice 3, scipy/mpmath 99, hand-written 1 | |
CHISQ.INV |
CHISQ.INV(probability, deg_freedom) |
Inverse of the left-tailed chi-squared distribution. | Numeric | LibreOffice 4, scipy/mpmath 81, hand-written 4 | |
CHISQ.INV.RT |
CHISQ.INV.RT(probability, deg_freedom) |
Inverse of the right-tailed chi-squared distribution. | Numeric | LibreOffice 3, scipy/mpmath 81, hand-written 2 | |
CHISQ.TEST |
CHISQ.TEST(actual_range, expected_range) |
Chi-squared test for independence. | Numeric | LibreOffice 3, numpy/scipy 2, hand-written 3 | |
CHITEST |
CHITEST(actual_range, expected_range) |
Chi-squared test for independence. | Numeric | LibreOffice 1, numpy/scipy 1 | |
CONFIDENCE |
CONFIDENCE(alpha, standard_dev, size) |
Confidence interval half-width for a mean, normal distribution. | Numeric | LibreOffice 1, numpy/scipy 5, hand-written 1 | |
CONFIDENCE.NORM |
CONFIDENCE.NORM(alpha, standard_dev, size) |
Confidence interval half-width for a mean, normal distribution. | Numeric | LibreOffice 4, numpy/scipy 5, hand-written 4 | |
CONFIDENCE.T |
CONFIDENCE.T(alpha, standard_dev, size) |
Confidence interval half-width for a mean, Student t distribution. | Numeric | LibreOffice 3, numpy/scipy 5, hand-written 2 | |
CORREL |
CORREL(array1, array2) |
Correlation coefficient between two data sets. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 4 | |
COUNT |
COUNT(value1, [value2], …) |
Counts cells that contain numbers. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
COUNTA |
COUNTA(value1, [value2], …) |
Counts cells that are not empty. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
COUNTBLANK |
COUNTBLANK(range) |
Counts the empty cells of a range (including empty text). | Numeric | LibreOffice 6, hand-written 2 | |
COUNTIF |
COUNTIF(range, criteria) |
Counts the cells of a range that meet a criterion. | Numeric | LibreOffice 85, hand-written 18 | numeric criterion matches numeric text; wildcards match text only |
COUNTIFS |
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …) |
Counts the cells that meet several criteria. | Numeric | LibreOffice 15, hand-written 5 | |
COVAR |
COVAR(array1, array2) |
Population covariance of two data sets. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 1 | |
COVARIANCE.P |
COVARIANCE.P(array1, array2) |
Population covariance of two data sets. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 1 | |
COVARIANCE.S |
COVARIANCE.S(array1, array2) |
Sample covariance of two data sets. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 2 | |
CRITBINOM |
CRITBINOM(trials, probability_s, alpha) |
Smallest number of successes whose cumulative binomial probability reaches alpha. | Numeric | LibreOffice 1, scipy/mpmath 70 | |
DEVSQ |
DEVSQ(number1, [number2], …) |
Sum of squares of deviations from the mean. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
EXPON.DIST |
EXPON.DIST(x, lambda, cumulative) |
Exponential distribution (density or cumulative). | Numeric | LibreOffice 5, scipy/mpmath 56, hand-written 4 | x < 0 is #NUM! (LO: 0) |
EXPONDIST |
EXPONDIST(x, lambda, cumulative) |
Exponential distribution (density or cumulative). | Numeric | LibreOffice 1, scipy/mpmath 28 | |
F.DIST |
F.DIST(x, deg_freedom1, deg_freedom2, cumulative) |
F distribution (density or cumulative). | Numeric | LibreOffice 6, scipy/mpmath 180, hand-written 4 | |
F.DIST.RT |
F.DIST.RT(x, deg_freedom1, deg_freedom2) |
Right-tailed F probability distribution. | Numeric | LibreOffice 2, scipy/mpmath 90, hand-written 1 | |
F.INV |
F.INV(probability, deg_freedom1, deg_freedom2) |
Inverse of the left-tailed F distribution. | Numeric | LibreOffice 2, scipy/mpmath 72, hand-written 1 | |
F.INV.RT |
F.INV.RT(probability, deg_freedom1, deg_freedom2) |
Inverse of the right-tailed F distribution. | Numeric | LibreOffice 3, scipy/mpmath 72, hand-written 2 | |
F.TEST |
F.TEST(array1, array2) |
Two-tailed probability that the variances of two samples are equal. | Numeric | LibreOffice 2, numpy/scipy 4, hand-written 3 | |
FDIST |
FDIST(x, deg_freedom1, deg_freedom2) |
Right-tailed F probability distribution. | Numeric | LibreOffice 1, scipy/mpmath 90 | |
FINV |
FINV(probability, deg_freedom1, deg_freedom2) |
Inverse of the right-tailed F distribution. | Numeric | LibreOffice 1, scipy/mpmath 72 | |
FISHER |
FISHER(x) |
Fisher transformation. | Numeric | LibreOffice 7, scipy/mpmath 7, hand-written 2 | |
FISHERINV |
FISHERINV(y) |
Inverse of the Fisher transformation. | Numeric | LibreOffice 7, scipy/mpmath 7, hand-written 1 | |
FORECAST |
FORECAST(x, known_y's, known_x's) |
Predicts a value along a linear trend. | Numeric | LibreOffice 14, numpy/scipy 4, hand-written 2 | |
FORECAST.LINEAR |
FORECAST.LINEAR(x, known_y's, known_x's) |
Predicts a value along a linear trend. | Numeric | LibreOffice 12, numpy/scipy 4, hand-written 1 | |
FREQUENCY |
FREQUENCY(data_array, bins_array) |
Frequency distribution as a vertical array (one more element than bins). | Numeric | LibreOffice 17, hand-written 4 | |
FTEST |
FTEST(array1, array2) |
Two-tailed probability that the variances of two samples are equal. | Numeric | LibreOffice 1, numpy/scipy 4 | |
GAMMA |
GAMMA(number) |
Gamma function value. | Numeric | LibreOffice 8, scipy/mpmath 15, hand-written 4 | |
GAMMA.DIST |
GAMMA.DIST(x, alpha, beta, cumulative) |
Gamma distribution (density or cumulative). | Numeric | LibreOffice 6, scipy/mpmath 126, hand-written 4 | |
GAMMA.INV |
GAMMA.INV(probability, alpha, beta) |
Inverse of the gamma cumulative distribution. | Numeric | LibreOffice 4, scipy/mpmath 63, hand-written 2 | |
GAMMADIST |
GAMMADIST(x, alpha, beta, cumulative) |
Gamma distribution (density or cumulative). | Numeric | LibreOffice 1, scipy/mpmath 63 | |
GAMMAINV |
GAMMAINV(probability, alpha, beta) |
Inverse of the gamma cumulative distribution. | Numeric | LibreOffice 1, scipy/mpmath 63 | |
GAMMALN |
GAMMALN(x) |
Natural logarithm of the gamma function. | Numeric | LibreOffice 5, scipy/mpmath 10, hand-written 3 | |
GAMMALN.PRECISE |
GAMMALN.PRECISE(x) |
Natural logarithm of the gamma function. | Numeric | LibreOffice 2, scipy/mpmath 10, hand-written 1 | |
GAUSS |
GAUSS(z) |
Probability that a standard normal value lies between 0 and z. | Numeric | LibreOffice 3, scipy/mpmath 15, hand-written 1 | |
GEOMEAN |
GEOMEAN(number1, [number2], …) |
Geometric mean of positive numbers. | Numeric | LibreOffice 13, numpy/scipy 4, hand-written 3 | LO returns 0 when a value is 0 |
GROWTH |
GROWTH(known_y's, [known_x's], [new_x's], [const]) |
Values along an exponential trend fitted by least squares. | Numeric | LibreOffice 14, statsmodels/numpy 3, hand-written 3 | |
HARMEAN |
HARMEAN(number1, [number2], …) |
Harmonic mean of positive numbers. | Numeric | LibreOffice 13, numpy/scipy 4, hand-written 2 | |
HYPGEOM.DIST |
HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) |
Hypergeometric distribution (mass or cumulative). | Numeric | LibreOffice 6, scipy/mpmath 56, hand-written 4 | |
HYPGEOMDIST |
HYPGEOMDIST(sample_s, number_sample, population_s, number_pop) |
Hypergeometric probability mass (compatibility). | Numeric | LibreOffice 1, scipy/mpmath 28 | |
INTERCEPT |
INTERCEPT(known_y's, known_x's) |
Intercept of the linear regression line. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 1 | |
KURT |
KURT(number1, [number2], …) |
Kurtosis of a data set. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 2 | |
LARGE |
LARGE(array, k) |
k-th largest value of a data set. | Numeric | LibreOffice 12, hand-written 7 | non-integer k rounded up (unverified) |
LINEST |
LINEST(known_y's, [known_x's], [const], [stats]) |
Statistics of the least-squares line (or hyperplane) through the data, as an array. | Numeric | LibreOffice 83, statsmodels/numpy 61, hand-written 13 | collinear regressors: minimum-norm solution |
LOGEST |
LOGEST(known_y's, [known_x's], [const], [stats]) |
Statistics of the exponential curve y = b·m^x fitted to the data, as an array. | Numeric | LibreOffice 52, statsmodels/numpy 42, hand-written 3 | collinear regressors: minimum-norm solution |
LOGINV |
LOGINV(probability, mean, standard_dev) |
Inverse of the log-normal cumulative distribution. | Numeric | LibreOffice 1, scipy/mpmath 22 | |
LOGNORM.DIST |
LOGNORM.DIST(x, mean, standard_dev, cumulative) |
Log-normal distribution (density or cumulative). | Numeric | LibreOffice 4, scipy/mpmath 48, hand-written 3 | x <= 0 is #NUM! (LO: 0) |
LOGNORM.INV |
LOGNORM.INV(probability, mean, standard_dev) |
Inverse of the log-normal cumulative distribution. | Numeric | LibreOffice 2, scipy/mpmath 22, hand-written 2 | |
LOGNORMDIST |
LOGNORMDIST(x, mean, standard_dev) |
Cumulative log-normal distribution (compatibility). | Numeric | LibreOffice 1, scipy/mpmath 24 | |
MAX |
MAX(number1, [number2], …) |
Largest number. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
No number at all gives 0. |
MAXA |
MAXA(value1, [value2], …) |
Largest value, counting text as 0 and logical values. | Numeric | LibreOffice 13, hand-written 1 | |
MAXIFS |
MAXIFS(max_range, criteria_range1, criteria1, …) |
Largest value among the cells that meet several criteria. | Numeric | LibreOffice 15, hand-written 2 | |
MEDIAN |
MEDIAN(number1, [number2], …) |
Median of the given numbers. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 8 | |
MIN |
MIN(number1, [number2], …) |
Smallest number. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
No number at all gives 0. |
MINA |
MINA(value1, [value2], …) |
Smallest value, counting text as 0 and logical values. | Numeric | LibreOffice 13, hand-written 2 | |
MINIFS |
MINIFS(min_range, criteria_range1, criteria1, …) |
Smallest value among the cells that meet several criteria. | Numeric | LibreOffice 15, hand-written 1 | |
MODE |
MODE(number1, [number2], …) |
Most frequently occurring value. | Numeric | LibreOffice 16, hand-written 1 | first mode encountered (LO: smallest) |
MODE.MULT |
MODE.MULT(number1, [number2], …) |
Vertical array of the most frequently occurring values. | Numeric | LibreOffice 7, hand-written 2 | |
MODE.SNGL |
MODE.SNGL(number1, [number2], …) |
Most frequently occurring value. | Numeric | LibreOffice 13, hand-written 1 | first mode encountered |
NEGBINOM.DIST |
NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) |
Negative binomial distribution (mass or cumulative). | Numeric | LibreOffice 4, scipy/mpmath 72, hand-written 5 | |
NEGBINOMDIST |
NEGBINOMDIST(number_f, number_s, probability_s) |
Negative binomial probability mass (compatibility). | Numeric | LibreOffice 1, scipy/mpmath 36 | |
NORM.DIST |
NORM.DIST(x, mean, standard_dev, cumulative) |
Normal distribution (density or cumulative). | Numeric | LibreOffice 6, scipy/mpmath 30, hand-written 4 | |
NORM.INV |
NORM.INV(probability, mean, standard_dev) |
Inverse of the normal cumulative distribution. | Numeric | LibreOffice 5, scipy/mpmath 22, hand-written 4 | |
NORM.S.DIST |
NORM.S.DIST(z, cumulative) |
Standard normal distribution (density or cumulative). | Numeric | LibreOffice 3, scipy/mpmath 30, hand-written 3 | |
NORM.S.INV |
NORM.S.INV(probability) |
Inverse of the standard normal cumulative distribution. | Numeric | LibreOffice 5, scipy/mpmath 22, hand-written 2 | |
NORMDIST |
NORMDIST(x, mean, standard_dev, cumulative) |
Normal distribution (compatibility). | Numeric | LibreOffice 2, scipy/mpmath 15 | |
NORMINV |
NORMINV(probability, mean, standard_dev) |
Inverse of the normal cumulative distribution. | Numeric | LibreOffice 1, scipy/mpmath 22 | |
NORMSDIST |
NORMSDIST(z) |
Standard normal cumulative distribution. | Numeric | LibreOffice 2, scipy/mpmath 15 | |
NORMSINV |
NORMSINV(probability) |
Inverse of the standard normal cumulative distribution. | Numeric | LibreOffice 1, scipy/mpmath 22 | |
PEARSON |
PEARSON(array1, array2) |
Pearson product-moment correlation coefficient. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 1 | |
PERCENTILE |
PERCENTILE(array, k) |
k-th percentile of values, k in 0…1 inclusive. | Numeric | LibreOffice 42, numpy/scipy 66, hand-written 2 | |
PERCENTILE.EXC |
PERCENTILE.EXC(array, k) |
k-th percentile of values, k in 0…1 exclusive. | Numeric | LibreOffice 42, numpy/scipy 42, hand-written 3 | |
PERCENTILE.INC |
PERCENTILE.INC(array, k) |
k-th percentile of values, k in 0…1 inclusive. | Numeric | LibreOffice 42, numpy/scipy 66, hand-written 1 | |
PERCENTRANK |
PERCENTRANK(array, x, [significance]) |
Rank of a value in a data set as a percentage (0…1 inclusive). | Numeric | LibreOffice 33, hand-written 3 | truncates decimals (LO rounds significant digits) |
PERCENTRANK.EXC |
PERCENTRANK.EXC(array, x, [significance]) |
Rank of a value in a data set as a percentage (0…1 exclusive). | Numeric | LibreOffice 33, hand-written 3 | truncates decimals (LO rounds significant digits) |
PERCENTRANK.INC |
PERCENTRANK.INC(array, x, [significance]) |
Rank of a value in a data set as a percentage (0…1 inclusive). | Numeric | LibreOffice 33, numpy/scipy 36, hand-written 4 | truncates decimals (LO rounds significant digits) |
PERMUT |
PERMUT(number, number_chosen) |
Number of permutations without repetition. | Numeric | LibreOffice 12, scipy/mpmath 6, hand-written 3 | |
PERMUTATIONA |
PERMUTATIONA(number, number_chosen) |
Number of permutations with repetitions. | Numeric | LibreOffice 12, scipy/mpmath 6, hand-written 2 | |
PHI |
PHI(x) |
Density of the standard normal distribution. | Numeric | LibreOffice 3, scipy/mpmath 15, hand-written 1 | |
POISSON |
POISSON(x, mean, cumulative) |
Poisson distribution (mass or cumulative). | Numeric | LibreOffice 1, scipy/mpmath 49 | |
POISSON.DIST |
POISSON.DIST(x, mean, cumulative) |
Poisson distribution (mass or cumulative). | Numeric | LibreOffice 6, scipy/mpmath 98, hand-written 4 | |
PROB |
PROB(x_range, prob_range, lower_limit, [upper_limit]) |
Probability that values in a range are between two limits. | Numeric | LibreOffice 7, hand-written 4 | |
QUARTILE |
QUARTILE(array, quart) |
Quartile of a data set (0…4). | Numeric | LibreOffice 24, hand-written 2 | |
QUARTILE.EXC |
QUARTILE.EXC(array, quart) |
Quartile of a data set, percentiles 0…1 exclusive. | Numeric | LibreOffice 24, numpy/scipy 18, hand-written 3 | |
QUARTILE.INC |
QUARTILE.INC(array, quart) |
Quartile of a data set, percentiles 0…1 inclusive. | Numeric | LibreOffice 24, numpy/scipy 30, hand-written 1 | |
RANK |
RANK(number, ref, [order]) |
Rank of a number in a list of numbers. | Numeric | LibreOffice 28, hand-written 3 | |
RANK.AVG |
RANK.AVG(number, ref, [order]) |
Rank of a number in a list; ties share the average rank. | Numeric | LibreOffice 28, numpy/scipy 36, hand-written 2 | |
RANK.EQ |
RANK.EQ(number, ref, [order]) |
Rank of a number in a list; ties share the top rank. | Numeric | LibreOffice 28, numpy/scipy 36, hand-written 2 | |
RSQ |
RSQ(known_y's, known_x's) |
Square of the Pearson correlation coefficient. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 1 | |
SKEW |
SKEW(number1, [number2], …) |
Skewness of a distribution (sample). | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 4 | |
SKEW.P |
SKEW.P(number1, [number2], …) |
Skewness of a distribution (population). | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
SLOPE |
SLOPE(known_y's, known_x's) |
Slope of the linear regression line. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 2 | |
SMALL |
SMALL(array, k) |
k-th smallest value of a data set. | Numeric | LibreOffice 12, hand-written 3 | non-integer k rounded up (unverified; LO truncates) |
STANDARDIZE |
STANDARDIZE(x, mean, standard_dev) |
Normalized value of x for a mean and standard deviation. | Numeric | LibreOffice 4, hand-written 2 | |
STDEV |
STDEV(number1, [number2], …) |
Standard deviation of a sample. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 3 | |
STDEV.P |
STDEV.P(number1, [number2], …) |
Standard deviation of an entire population. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
STDEV.S |
STDEV.S(number1, [number2], …) |
Standard deviation of a sample. | Numeric | LibreOffice 13, numpy/scipy 6 | |
STDEVA |
STDEVA(value1, [value2], …) |
Standard deviation of a sample, counting text as 0 and logical values. | Numeric | LibreOffice 13, hand-written 1 | |
STDEVP |
STDEVP(number1, [number2], …) |
Standard deviation of an entire population. | Numeric | LibreOffice 13, numpy/scipy 6 | |
STDEVPA |
STDEVPA(value1, [value2], …) |
Standard deviation of a population, counting text as 0 and logical values. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
STEYX |
STEYX(known_y's, known_x's) |
Standard error of the predicted y for each x in a regression. | Numeric | LibreOffice 10, numpy/scipy 4, hand-written 2 | |
T.DIST |
T.DIST(x, deg_freedom, cumulative) |
Left-tailed Student t distribution (density or cumulative). | Numeric | LibreOffice 7, scipy/mpmath 192, hand-written 3 | |
T.DIST.2T |
T.DIST.2T(x, deg_freedom) |
Two-tailed Student t distribution. | Numeric | LibreOffice 4, scipy/mpmath 56, hand-written 2 | |
T.DIST.RT |
T.DIST.RT(x, deg_freedom) |
Right-tailed Student t distribution. | Numeric | LibreOffice 3, scipy/mpmath 96, hand-written 1 | |
T.INV |
T.INV(probability, deg_freedom) |
Left-tailed inverse of the Student t distribution. | Numeric | LibreOffice 6, scipy/mpmath 96, hand-written 3 | |
T.INV.2T |
T.INV.2T(probability, deg_freedom) |
Two-tailed inverse of the Student t distribution. | Numeric | LibreOffice 4, scipy/mpmath 96, hand-written 2 | |
T.TEST |
T.TEST(array1, array2, tails, type) |
Probability associated with a Student t-test. | Numeric | LibreOffice 14, numpy/scipy 24, hand-written 4 | |
TDIST |
TDIST(x, deg_freedom, tails) |
Student t distribution with one or two tails (compatibility). | Numeric | LibreOffice 4, scipy/mpmath 112, hand-written 2 | |
TINV |
TINV(probability, deg_freedom) |
Two-tailed inverse of the Student t distribution. | Numeric | LibreOffice 1, scipy/mpmath 96, hand-written 1 | |
TREND |
TREND(known_y's, [known_x's], [new_x's], [const]) |
Values along a linear trend fitted by least squares. | Numeric | LibreOffice 25, statsmodels/numpy 6, hand-written 3 | |
TRIMMEAN |
TRIMMEAN(array, percent) |
Mean of the interior of a data set, excluding a fraction of the extreme values. | Numeric | LibreOffice 20, numpy/scipy 24, hand-written 3 | |
TTEST |
TTEST(array1, array2, tails, type) |
Probability associated with a Student t-test. | Numeric | LibreOffice 6, numpy/scipy 8 | |
VAR |
VAR(number1, [number2], …) |
Variance of a sample. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
VAR.P |
VAR.P(number1, [number2], …) |
Variance of an entire population. | Numeric | LibreOffice 13, numpy/scipy 6 | |
VAR.S |
VAR.S(number1, [number2], …) |
Variance of a sample. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 1 | |
VARA |
VARA(value1, [value2], …) |
Variance of a sample, counting text as 0 and logical values. | Numeric | LibreOffice 13, numpy/scipy 6, hand-written 2 | |
VARP |
VARP(number1, [number2], …) |
Variance of an entire population. | Numeric | LibreOffice 13, numpy/scipy 6 | |
VARPA |
VARPA(value1, [value2], …) |
Variance of a population, counting text as 0 and logical values. | Numeric | LibreOffice 13, hand-written 1 | |
WEIBULL |
WEIBULL(x, alpha, beta, cumulative) |
Weibull distribution (density or cumulative). | Numeric | LibreOffice 1, scipy/mpmath 35 | |
WEIBULL.DIST |
WEIBULL.DIST(x, alpha, beta, cumulative) |
Weibull distribution (density or cumulative). | Numeric | LibreOffice 4, scipy/mpmath 70, hand-written 3 | |
Z.TEST |
Z.TEST(array, x, [sigma]) |
One-tailed P-value of a z-test. | Numeric | LibreOffice 3, numpy/scipy 12, hand-written 3 | |
ZTEST |
ZTEST(array, x, [sigma]) |
One-tailed P-value of a z-test. | Numeric | LibreOffice 1 |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Financial (49 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
ACCRINT |
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method]) |
Accrued interest of a security that pays periodic interest. | Lookup | Excel-computed data 1 923, LibreOffice+Python 200, Python only (LibreOffice differs) 122, PY 45, DOC | Excel data covers settlement ≤ first_interest with calc_method TRUE. Not covered by Excel data: settlement after first_interest (TRUE: same per-period rule continued; FALSE: accrual from first_interest, as documented) — Python model only. |
ACCRINTM |
ACCRINTM(issue, settlement, rate, [par], [basis]) |
Accrued interest of a security that pays interest at maturity. | Lookup | Excel-computed data 362, LibreOffice+Python 125, Python only (LibreOffice differs) 20, DOC | |
COUPDAYBS |
COUPDAYBS(settlement, maturity, frequency, [basis]) |
Days from the beginning of the coupon period to the settlement date. | Lookup | Excel-computed data 917, LibreOffice+Python 474, DOC | COUPDAYSNC in 30/360 around the end of February: Excel's own count (LibreOffice differs). (row shared by COUPDAYBS, COUPDAYS, COUPDAYSNC) |
COUPDAYS |
COUPDAYS(settlement, maturity, frequency, [basis]) |
Days in the coupon period that contains the settlement date. | Lookup | Excel-computed data 17, LibreOffice+Python 456, DOC | COUPDAYSNC in 30/360 around the end of February: Excel's own count (LibreOffice differs). (row shared by COUPDAYBS, COUPDAYS, COUPDAYSNC) |
COUPDAYSNC |
COUPDAYSNC(settlement, maturity, frequency, [basis]) |
Days from the settlement date to the next coupon date. | Lookup | Excel-computed data 917, LibreOffice+Python 433, Python only (LibreOffice differs) 23, DOC | COUPDAYSNC in 30/360 around the end of February: Excel's own count (LibreOffice differs). (row shared by COUPDAYBS, COUPDAYS, COUPDAYSNC) |
COUPNCD |
COUPNCD(settlement, maturity, frequency, [basis]) |
Next coupon date after the settlement date (serial number). | Lookup | Excel-computed data 917, LibreOffice+Python 474, DOC | Coupon dates are derived from maturity directly (see below). (row shared by COUPNCD, COUPPCD, COUPNUM) |
COUPNUM |
COUPNUM(settlement, maturity, frequency, [basis]) |
Number of coupons payable between the settlement and maturity dates. | Lookup | Excel-computed data 917, LibreOffice+Python 474, DOC | Coupon dates are derived from maturity directly (see below). (row shared by COUPNCD, COUPPCD, COUPNUM) |
COUPPCD |
COUPPCD(settlement, maturity, frequency, [basis]) |
Previous coupon date before the settlement date (serial number). | Lookup | Excel-computed data 917, LibreOffice+Python 456, DOC | Coupon dates are derived from maturity directly (see below). (row shared by COUPNCD, COUPPCD, COUPNUM) |
CUMIPMT |
CUMIPMT(rate, nper, pv, start_period, end_period, type) |
Cumulative interest paid on a loan between two periods. | Lookup | Excel-computed data 210, LibreOffice+Python 103, DOC | Fractional bounds: ⌈start⌉…⌊end⌋ (Excel). (row shared by CUMIPMT, CUMPRINC) |
CUMPRINC |
CUMPRINC(rate, nper, pv, start_period, end_period, type) |
Cumulative principal repaid on a loan between two periods. | Lookup | Excel-computed data 210, LibreOffice+Python 103, DOC | Fractional bounds: ⌈start⌉…⌊end⌋ (Excel). (row shared by CUMIPMT, CUMPRINC) |
DB |
DB(cost, salvage, life, period, [month]) |
Fixed-declining balance depreciation of an asset for a period. | Lookup | Excel-computed data 398, LibreOffice+Python 236, DOC | Period life + 1 allowed when month < 12. |
DDB |
DDB(cost, salvage, life, period, [factor]) |
Double-declining (or other factor) balance depreciation of an asset for a period. | Lookup | Excel-computed data 458, LibreOffice+Python 215, DOC | Fractional periods follow Excel (LibreOffice differs). |
DISC |
DISC(settlement, maturity, pr, redemption, [basis]) |
Discount rate of a security. | Lookup | Excel-computed data 2 747, LibreOffice+Python 213, DOC | |
DOLLARDE |
DOLLARDE(fractional_dollar, fraction) |
Converts a price written as a fraction (1.02 = 1 2/16) to a decimal number. | Lookup | Excel-computed data 14, LibreOffice+Python 91, DOC | |
DOLLARFR |
DOLLARFR(decimal_dollar, fraction) |
Converts a decimal price to fractional notation (1.125 → 1.02 for sixteenths). | Lookup | Excel-computed data 14, LibreOffice+Python 91, DOC | |
DURATION |
DURATION(settlement, maturity, coupon, yld, frequency, [basis]) |
Macaulay duration, in years, of a security with periodic interest payments. | Lookup | Excel-computed data 5 492, LibreOffice+Python 585, Python only (LibreOffice differs) 1 220, DOC | LibreOffice measures time with YEARFRAC × frequency and misses Excel's documented 5.993775. (row shared by DURATION, MDURATION) |
EFFECT |
EFFECT(nominal_rate, npery) |
Effective annual rate from a nominal rate compounded npery times a year. | Lookup | Excel-computed data 14, LibreOffice+Python 38, DOC | Rate ≤ 0 → #NUM! (LibreOffice returns 0). (row shared by EFFECT, NOMINAL) |
FV |
FV(rate, nper, pmt, [pv], [type]) |
Future value of an investment with constant payments and rate. | Lookup | Excel-computed data 2 026, LibreOffice+Python 451, DOC | |
FVSCHEDULE |
FVSCHEDULE(principal, schedule) |
Future value of a principal after applying a series of compound interest rates. | Lookup | Excel-computed data 14, LibreOffice+Python 7, DOC | |
INTRATE |
INTRATE(settlement, maturity, investment, redemption, [basis]) |
Interest rate of a fully invested security. | Lookup | Excel-computed data 2 749, LibreOffice+Python 121, Python only (LibreOffice differs) 20, DOC | RECEIVED with discount × years ≥ 1 → #NUM!. (row shared by INTRATE, RECEIVED, PRICEDISC) |
IPMT |
IPMT(rate, per, nper, pv, [fv], [type]) |
Interest part of a given payment of a loan with constant payments and rate. | Lookup | Excel-computed data 4 552, LibreOffice+Python 363, DOC | |
IRR |
IRR(values, [guess]) |
Internal rate of return of periodic cash flows (iterative; guess defaults to 10%). | Lookup | Excel-computed data 11, LibreOffice+Python 41, DOC, HW | See "iterative functions" in FINANCIAL-CONVENTIONS.md. With multiple roots the result may differ from Excel's when the guess is far from every root. |
ISPMT |
ISPMT(rate, per, nper, pv) |
Interest paid in a period of a loan repaid in equal principal instalments. | Lookup | Excel-computed data 626, LibreOffice+Python 48, DOC | |
MDURATION |
MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) |
Modified Macaulay duration of a security with periodic interest payments. | Lookup | Excel-computed data 5 492, LibreOffice+Python 585, Python only (LibreOffice differs) 1 220, DOC | LibreOffice measures time with YEARFRAC × frequency and misses Excel's documented 5.993775. (row shared by DURATION, MDURATION) |
MIRR |
MIRR(values, finance_rate, reinvest_rate) |
Internal rate of return with separate financing and reinvestment rates. | Lookup | Excel-computed data 149, LibreOffice+Python 49, DOC | |
NOMINAL |
NOMINAL(effect_rate, npery) |
Nominal annual rate from an effective rate and npery compounding periods a year. | Lookup | Excel-computed data 14, LibreOffice+Python 38, DOC | Rate ≤ 0 → #NUM! (LibreOffice returns 0). (row shared by EFFECT, NOMINAL) |
NPER |
NPER(rate, pmt, pv, [fv], [type]) |
Number of periods of an investment with constant payments and rate. | Lookup | Excel-computed data 855, LibreOffice+Python 99, DOC | No solution → #NUM!. |
NPV |
NPV(rate, value1, [value2], …) |
Net present value of cash flows at the end of periods 1, 2, … discounted at a constant rate. | Lookup | Excel-computed data 23, LibreOffice+Python 96, DOC, HW | |
PDURATION |
PDURATION(rate, pv, fv) |
Number of periods for an investment to reach a value at a constant rate. | Lookup | Excel-computed data 23, LibreOffice+Python 40, DOC | RRI(n, 0, 0) = 0. (row shared by PDURATION, RRI) |
PMT |
PMT(rate, nper, pv, [fv], [type]) |
Periodic payment of a loan or annuity with constant payments and rate. | Lookup | Excel-computed data 1 752, LibreOffice+Python 483, DOC | |
PPMT |
PPMT(rate, per, nper, pv, [fv], [type]) |
Principal part of a given payment of a loan with constant payments and rate. | Lookup | Excel-computed data 4 552, LibreOffice+Python 362, DOC | |
PRICE |
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) |
Price per 100 face value of a security that pays periodic interest. | Lookup | Excel-computed data 10 982, LibreOffice+Python 1 409, Python only (LibreOffice differs) 848, DOC | Time to next coupon (E − A)/E; simple interest in the last period. yld < 0 → #NUM!. |
PRICEDISC |
PRICEDISC(settlement, maturity, discount, redemption, [basis]) |
Price per 100 face value of a discounted security. | Lookup | Excel-computed data 3 662, LibreOffice+Python 121, Python only (LibreOffice differs) 20, DOC | RECEIVED with discount × years ≥ 1 → #NUM!. (row shared by INTRATE, RECEIVED, PRICEDISC) |
PRICEMAT |
PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) |
Price per 100 face value of a security that pays interest at maturity. | Lookup | Excel-computed data 1 942, LibreOffice+Python 359, Python only (LibreOffice differs) 62, DOC | DSM = DIM − A, B from issue → settlement (Excel; LibreOffice uses three YEARFRACs). (row shared by PRICEMAT, YIELDMAT) |
PV |
PV(rate, nper, pmt, [fv], [type]) |
Present value of an investment with constant payments and rate. | Lookup | Excel-computed data 1 842, LibreOffice+Python 450, DOC | |
RATE |
RATE(nper, pmt, pv, [fv], [type], [guess]) |
Interest rate per period of an annuity (iterative; guess defaults to 10%). | Lookup | Excel-computed data 8, LibreOffice+Python 449, PY 73, DOC, HW | Root always found when one exists (Excel gives #NUM! after 20 Newton steps). 73 cases where LibreOffice's solver fails or stops on a non-root are Python-only. |
RECEIVED |
RECEIVED(settlement, maturity, investment, discount, [basis]) |
Amount received at maturity for a fully invested security. | Lookup | Excel-computed data 1 290, LibreOffice+Python 121, Python only (LibreOffice differs) 20, DOC | RECEIVED with discount × years ≥ 1 → #NUM!. (row shared by INTRATE, RECEIVED, PRICEDISC) |
RRI |
RRI(nper, pv, fv) |
Equivalent constant rate for the growth of an investment. | Lookup | Excel-computed data 31, LibreOffice+Python 50, DOC | RRI(n, 0, 0) = 0. (row shared by PDURATION, RRI) |
SLN |
SLN(cost, salvage, life) |
Straight-line depreciation of an asset for one period. | Lookup | Excel-computed data 26, LibreOffice+Python 8, DOC | SYD accepts 0 < per ≤ life (fractions too). (row shared by SLN, SYD) |
SYD |
SYD(cost, salvage, life, per) |
Sum-of-years' digits depreciation of an asset for a period. | Lookup | Excel-computed data 134, LibreOffice+Python 53, DOC | SYD accepts 0 < per ≤ life (fractions too). (row shared by SLN, SYD) |
TBILLEQ |
TBILLEQ(settlement, maturity, discount) |
Bond-equivalent yield of a Treasury bill. | Lookup | Excel-computed data 50, LibreOffice+Python 14, Python only (LibreOffice differs) 20, PY 18, DOC | Actual days; TBILLEQ switches formula beyond 182 days (Excel data includes such bills). Maturity equal to settlement → #NUM!. (row shared by TBILLEQ, TBILLPRICE, TBILLYIELD) |
TBILLPRICE |
TBILLPRICE(settlement, maturity, discount) |
Price per 100 face value of a Treasury bill. | Lookup | Excel-computed data 83, LibreOffice+Python 14, Python only (LibreOffice differs) 32, PY 18, DOC | Actual days; TBILLEQ switches formula beyond 182 days (Excel data includes such bills). Maturity equal to settlement → #NUM!. (row shared by TBILLEQ, TBILLPRICE, TBILLYIELD) |
TBILLYIELD |
TBILLYIELD(settlement, maturity, pr) |
Yield of a Treasury bill. | Lookup | Excel-computed data 71, LibreOffice+Python 14, Python only (LibreOffice differs) 32, PY 18, DOC | Actual days; TBILLEQ switches formula beyond 182 days (Excel data includes such bills). Maturity equal to settlement → #NUM!. (row shared by TBILLEQ, TBILLPRICE, TBILLYIELD) |
VDB |
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) |
Declining-balance depreciation between two (possibly fractional) periods, switching to straight line when larger. | Lookup | Excel-computed data 2 546, LibreOffice+Python 297, LO 168, DOC | |
XIRR |
XIRR(values, dates, [guess]) |
Annual internal rate of return of cash flows on given dates (iterative; guess defaults to 10%). | Lookup | Excel-computed data 26, LibreOffice+Python 21, DOC, HW | |
XNPV |
XNPV(rate, values, dates) |
Net present value of cash flows on given dates, discounted at an annual rate over actual/365 years. | Lookup | LibreOffice+Python 61, DOC, HW | No Excel data set: LibreOffice + Python + documentation example. Rates > −1 accepted. |
YIELD |
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) |
Yield of a security that pays periodic interest. | Lookup | Excel-computed data 441, LibreOffice+Python 1 400, Python only (LibreOffice differs) 751, DOC | The Excel set covers negative yields; positive yields rest on PRICE (10 982 Excel cases) being inverted, LO+PY and the documentation example. |
YIELDDISC |
YIELDDISC(settlement, maturity, pr, redemption, [basis]) |
Annual yield of a discounted security. | Lookup | Excel-computed data 2 747, LibreOffice+Python 211, DOC | |
YIELDMAT |
YIELDMAT(settlement, maturity, issue, rate, pr, [basis]) |
Annual yield of a security that pays interest at maturity. | Lookup | Excel-computed data 2 912, LibreOffice+Python 359, Python only (LibreOffice differs) 62, DOC | DSM = DIM − A, B from issue → settlement (Excel; LibreOffice uses three YEARFRACs). (row shared by PRICEMAT, YIELDMAT) |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Logical (6 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
AND |
AND(logical1, [logical2], …) |
TRUE when all arguments are TRUE. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
FALSE |
FALSE() |
The logical value FALSE. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
NOT |
NOT(logical) |
Reverses a logical value. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
OR |
OR(logical1, [logical2], …) |
TRUE when any argument is TRUE. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
TRUE |
TRUE() |
The logical value TRUE. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
XOR |
XOR(logical1, [logical2], …) |
TRUE when an odd number of arguments are TRUE. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Text (41 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
ARRAYTOTEXT |
ARRAYTOTEXT(array, [format]) |
Text of an array: comma-separated (0) or as an array constant (1). | Text | Python 15 | |
CHAR |
CHAR(number) |
Character for a code number (1 to 255, Windows-1252). | Text | LibreOffice 9, Excel-documented value 1 | CHAR/CODE follow Windows-1252, not Mac OS Roman as Excel for Mac does. |
CLEAN |
CLEAN(text) |
Removes non-printable characters (codes 0 to 31). | Text | LibreOffice 12, Excel-documented value 1 | |
CODE |
CODE(text) |
Code number of the first character (Windows-1252). | Text | LibreOffice 5, Excel-documented value 2 | CHAR/CODE follow Windows-1252, not Mac OS Roman as Excel for Mac does. |
CONCAT |
CONCAT(text1, [text2], …) |
Joins text from several ranges or strings. | Text | LibreOffice 6, Excel-documented value 1 | |
CONCATENATE |
CONCATENATE(text1, [text2], …) |
Joins several text strings into one. | Text | LibreOffice 3, Excel-documented value 1 | |
DOLLAR |
DOLLAR(number, [decimals]) |
Rounds a number and formats it as currency text. | Text | LibreOffice 10 | DOLLAR returns -$1,234.57 for negative numbers (current Excel and LibreOffice); Excel's documentation still describes ($1,234.57). |
ENCODEURL |
ENCODEURL(text) |
URL-encodes a text string (UTF-8 percent-encoding). | Text | LibreOffice 6, Excel-documented value 1 | |
EXACT |
EXACT(text1, text2) |
TRUE when two text strings are identical (case-sensitive). | Text | LibreOffice 6, Excel-documented value 1 | |
FIND |
FIND(find_text, within_text, [start_num]) |
Position of one text inside another (case-sensitive). | Text | LibreOffice 15, Excel-documented value 2 | |
FIXED |
FIXED(number, [decimals], [no_commas]) |
Rounds a number and formats it as text with a fixed number of decimals. | Text | LibreOffice 17, Excel-documented value 2 | |
LEFT |
LEFT(text, [num_chars]) |
First characters of a text string. | Text | LibreOffice 49, Excel-documented value 1 | |
LEFTB |
LEFTB(text, [num_bytes]) |
First characters of a text string (same as LEFT). | Text | LibreOffice 45 | |
LEN |
LEN(text) |
Number of characters in a text string. | Text | LibreOffice 12, Excel-documented value 2 | |
LENB |
LENB(text) |
Number of characters in a text string (same as LEN). | Text | LibreOffice 9 | |
LOWER |
LOWER(text) |
Converts text to lowercase. | Text | LibreOffice 10, Excel-documented value 1 | UPPER/LOWER/PROPER use Unicode case mapping (UPPER("straße") = STRASSE). |
MID |
MID(text, start_num, num_chars) |
Characters from the middle of a text string. | Text | LibreOffice 59 | |
MIDB |
MIDB(text, start_num, num_bytes) |
Characters from the middle of a text string (same as MID). | Text | LibreOffice 54 | |
NUMBERVALUE |
NUMBERVALUE(text, [decimal_separator], [group_separator]) |
Converts text to a number using the given separators. | Text | LibreOffice 16, Excel-documented value 1 | |
PROPER |
PROPER(text) |
Capitalizes the first letter of each word. | Text | LibreOffice 13, Excel-documented value 1 | UPPER/LOWER/PROPER use Unicode case mapping (UPPER("straße") = STRASSE). |
REGEXEXTRACT |
REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) |
Text matching a regular expression: first match (0), all matches (1) or capture groups (2). | Text | Python 329 | The REGEX functions use ICU syntax; Excel uses PCRE2. Common syntax is identical; PCRE-only constructs differ. |
REGEXREPLACE |
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) |
Replaces text matching a regular expression ($1 refers to a capture group). | Text | Python 1563 | The REGEX functions use ICU syntax; Excel uses PCRE2. Common syntax is identical; PCRE-only constructs differ. |
REGEXTEST |
REGEXTEST(text, pattern, [case_sensitivity]) |
TRUE when a regular expression matches any part of the text. | Text | Python 422 | The REGEX functions use ICU syntax; Excel uses PCRE2. Common syntax is identical; PCRE-only constructs differ. |
REPLACE |
REPLACE(old_text, start_num, num_chars, new_text) |
Replaces characters at a given position. | Text | LibreOffice 11 | |
REPT |
REPT(text, number_times) |
Repeats text a given number of times. | Text | LibreOffice 7 | |
RIGHT |
RIGHT(text, [num_chars]) |
Last characters of a text string. | Text | LibreOffice 48, Excel-documented value 1 | |
RIGHTB |
RIGHTB(text, [num_bytes]) |
Last characters of a text string (same as RIGHT). | Text | LibreOffice 45 | |
SEARCH |
SEARCH(find_text, within_text, [start_num]) |
Position of one text inside another (case-insensitive, wildcards ? and *). | Text | LibreOffice 19, Excel-documented value 1 | |
SUBSTITUTE |
SUBSTITUTE(text, old_text, new_text, [instance_num]) |
Replaces occurrences of old text with new text. | Text | LibreOffice 17 | |
T |
T(value) |
Returns the value when it is text, otherwise empty text. | Text | LibreOffice 12 | |
TEXT |
TEXT(value, format_text) |
Formats a value as text with a number format code. | Text | LibreOffice 24, Excel-documented value 2 | |
TEXTAFTER |
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) |
Text after a delimiter. | Text | LibreOffice 30, Excel-documented value 1 | TEXTBEFORE/TEXTAFTER count non-overlapping delimiter occurrences; overlapping delimiters may differ from Excel. |
TEXTBEFORE |
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) |
Text before a delimiter. | Text | LibreOffice 30, Excel-documented value 1 | TEXTBEFORE/TEXTAFTER count non-overlapping delimiter occurrences; overlapping delimiters may differ from Excel. |
TEXTJOIN |
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) |
Joins text with a delimiter, optionally skipping empty values. | Text | LibreOffice 10, Excel-documented value 1 | |
TEXTSPLIT |
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) |
Splits text into columns and rows by delimiters. | Text | LibreOffice 74, Excel-documented value 1 | TEXTSPLIT uses the top-left value of an array text argument. |
TRIM |
TRIM(text) |
Removes extra spaces, leaving single spaces between words. | Text | LibreOffice 15 | |
UNICHAR |
UNICHAR(number) |
Unicode character for a code point. | Text | LibreOffice 6, Excel-documented value 2 | |
UNICODE |
UNICODE(text) |
Unicode code point of the first character. | Text | LibreOffice 7 | |
UPPER |
UPPER(text) |
Converts text to uppercase. | Text | LibreOffice 10, Excel-documented value 1 | UPPER/LOWER/PROPER use Unicode case mapping (UPPER("straße") = STRASSE). |
VALUE |
VALUE(text) |
Converts text that represents a number, date or time to a number. | Text | LibreOffice 26 | Date text is parsed in month/day/year order regardless of the locale (ISO dates are always accepted); day-first locales are not supported yet. Dots are not date separators. |
VALUETOTEXT |
VALUETOTEXT(value, [format]) |
Text of a value; format 1 quotes text strings (strict). | Text | Python 33 |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Date & Time (25 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
DATE |
DATE(year, month, day) |
Serial number of a date. | Text | LibreOffice 19, Excel-documented value 3 | |
DATEDIF |
DATEDIF(start_date, end_date, unit) |
Difference between two dates in "Y", "M", "D", "MD", "YM" or "YD" units. | Text | LibreOffice 636, Excel-documented value 2 | DATEDIF "YD" follows the algorithm LibreOffice shares with Excel's documentation; Excel has reported discrepancies for some leap-year spans that are not reproduced. |
DATEVALUE |
DATEVALUE(date_text) |
Converts a date stored as text to a serial number. | Text | LibreOffice 22, Excel-documented value 5 | Date text is parsed in month/day/year order regardless of the locale (ISO dates are always accepted); day-first locales are not supported yet. Dots are not date separators. |
DAY |
DAY(serial_number) |
Day of the month of a date (1 to 31). | Text | LibreOffice 2, LibreOffice+Python 29, Excel-documented value 1 | |
DAYS |
DAYS(end_date, start_date) |
Number of days between two dates. | Text | LibreOffice 3, LibreOffice+Python 5 | HOUR/MINUTE/SECOND rounding at half-second boundaries and DAYS truncating its arguments follow Excel's observed behaviour; LibreOffice differs, so these cases are covered by hand-written tests only. |
DAYS360 |
DAYS360(start_date, end_date, [method]) |
Days between two dates on a 360-day year (US method, or European when method is TRUE). | Text | LibreOffice 395, Excel-documented value 1 | |
EDATE |
EDATE(start_date, months) |
Date a number of months before or after a start date. | Text | LibreOffice 4, LibreOffice+Python 190, Excel-documented value 3 | |
EOMONTH |
EOMONTH(start_date, months) |
Last day of the month a number of months before or after a start date. | Text | LibreOffice 4, LibreOffice+Python 190, Excel-documented value 2 | |
HOUR |
HOUR(serial_number) |
Hour of a time (0 to 23). | Text | LibreOffice 3, LibreOffice+Python 15, Excel-documented value 1 | HOUR/MINUTE/SECOND rounding at half-second boundaries and DAYS truncating its arguments follow Excel's observed behaviour; LibreOffice differs, so these cases are covered by hand-written tests only. |
ISOWEEKNUM |
ISOWEEKNUM(date) |
ISO 8601 week number of a date. | Text | LibreOffice+Python 169 | |
MINUTE |
MINUTE(serial_number) |
Minute of a time (0 to 59). | Text | LibreOffice 2, LibreOffice+Python 15, Excel-documented value 1 | HOUR/MINUTE/SECOND rounding at half-second boundaries and DAYS truncating its arguments follow Excel's observed behaviour; LibreOffice differs, so these cases are covered by hand-written tests only. |
MONTH |
MONTH(serial_number) |
Month of a date (1 to 12). | Text | LibreOffice 2, LibreOffice+Python 29, Excel-documented value 1 | |
NETWORKDAYS |
NETWORKDAYS(start_date, end_date, [holidays]) |
Working days between two dates (Saturday and Sunday off). | Text | LibreOffice 5, LibreOffice+Python 24, Excel-documented value 1 | |
NETWORKDAYS.INTL |
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) |
Working days between two dates with a custom weekend. | Text | LibreOffice 280, Excel-documented value 3 | |
NOW |
NOW() |
Current date and time as a serial number. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Volatile. One timestamp per recalculation pass (EvalContext.now). |
SECOND |
SECOND(serial_number) |
Second of a time (0 to 59). | Text | LibreOffice 1, LibreOffice+Python 15, Excel-documented value 1 | HOUR/MINUTE/SECOND rounding at half-second boundaries and DAYS truncating its arguments follow Excel's observed behaviour; LibreOffice differs, so these cases are covered by hand-written tests only. |
TIME |
TIME(hour, minute, second) |
Serial number of a time of day. | Text | LibreOffice 10, Excel-documented value 5 | |
TIMEVALUE |
TIMEVALUE(time_text) |
Converts a time stored as text to a fraction of a day. | Text | LibreOffice 15 | Date text is parsed in month/day/year order regardless of the locale (ISO dates are always accepted); day-first locales are not supported yet. Dots are not date separators. |
TODAY |
TODAY() |
Current date as a serial number. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Volatile. One timestamp per recalculation pass (EvalContext.now). |
WEEKDAY |
WEEKDAY(serial_number, [return_type]) |
Day of the week of a date as a number. | Text | LibreOffice 2, LibreOffice+Python 319, Excel-documented value 4 | |
WEEKNUM |
WEEKNUM(serial_number, [return_type]) |
Week number of a date within its year. | Text | LibreOffice 2, LibreOffice+Python 593, Excel-documented value 149 | |
WORKDAY |
WORKDAY(start_date, days, [holidays]) |
Date a number of working days before or after a start date. | Text | LibreOffice 5, LibreOffice+Python 168, Excel-documented value 2 | |
WORKDAY.INTL |
WORKDAY.INTL(start_date, days, [weekend], [holidays]) |
Date a number of working days before or after a start date, with a custom weekend. | Text | LibreOffice 774, Excel-documented value 2 | |
YEAR |
YEAR(serial_number) |
Year of a date. | Text | LibreOffice 4, LibreOffice+Python 29, Excel-documented value 2 | |
YEARFRAC |
YEARFRAC(start_date, end_date, [basis]) |
Fraction of a year between two dates (basis 0 to 4). | Text | LibreOffice 628, Excel-documented value 16 |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Lookup & Reference (18 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
ADDRESS |
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) |
Cell address as text, in A1 or R1C1 style, optionally prefixed with a sheet name. | Lookup | LO 66, HW | abs_num 5–8 accepted as 1–4. Sheet names are quoted when they are not plain identifiers. |
AREAS |
AREAS(reference) |
Number of areas in a reference. | Lookup | LO 3, HW | Always 1: SheetCore has no multi-area (union) references. |
COLUMN |
COLUMN([reference]) |
Column number of a reference. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
A multi-column reference returns a row array. |
COLUMNS |
COLUMNS(array) |
Number of columns in a reference or array. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
FORMULATEXT |
FORMULATEXT(reference) |
Formula of the referenced cell, as text. | Numeric | LibreOffice 2, hand-written 3 | en-US syntax |
HLOOKUP |
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) |
Looks for a value in the first row of a table and returns the value in the same column from another row. | Lookup | LO 198, HW (row shared by VLOOKUP, HLOOKUP) | Approximate match = binary search on ascending data (last of equal items); exact match linear with *, ?, ~. An empty range_lookup placeholder (VLOOKUP(a,b,2,)) means FALSE. Text never matches numbers ("5" ≠ 5) and logical values only match logical values — LibreOffice differs on both. |
HYPERLINK |
HYPERLINK(link_location, [friendly_name]) |
Creates a link; the cell shows the friendly name, or the link location when it is omitted. | Lookup | HW | Returns the friendly name (or the location); the link itself is not stored — cell hyperlinks are a model feature. LibreOffice returns a two-cell array and is not used as reference. |
INDEX |
INDEX(array, row_num, [column_num]) |
Value or reference at a given row and column of a range. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Reference form returns a reference. A 4th argument (area_num) is accepted and ignored: there are no multi-area references. |
INDIRECT |
INDIRECT(ref_text, [a1]) |
Reference described by a text string. Volatile. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Volatile. A1 style only: a1 = FALSE (R1C1) returns #REF!. Accepts a cell, range, defined name or structured reference. |
LOOKUP |
LOOKUP(lookup_value, lookup_vector, [result_vector]) |
Approximate lookup in a sorted one-row or one-column range (vector form) or in an array (array form). | Lookup | LO 77, HW | Vector and array forms. A result vector shorter than the match position gives #N/A (Excel reads past the end of a range). |
MATCH |
MATCH(lookup_value, lookup_array, [match_type]) |
Relative position of a value in a one-row or one-column range (1 = sorted ascending, 0 = exact, -1 = sorted descending). | Lookup | LO 130, HW | Types 1, 0, −1. Type −1 is a binary search on descending data. On unsorted data approximate modes return a position that may differ from Excel's (undefined behaviour in both). Blank cells and errors inside the range are skipped by the binary search. |
OFFSET |
OFFSET(reference, rows, cols, [height], [width]) |
Reference shifted from a starting reference. Volatile. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
Volatile. Result outside the grid is #REF!. |
ROW |
ROW([reference]) |
Row number of a reference. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
A multi-row reference returns a column array. |
ROWS |
ROWS(array) |
Number of rows in a reference or array. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
TRANSPOSE |
TRANSPOSE(array) |
Swaps the rows and columns of an array or range. | Lookup | LO 7, HW | |
VLOOKUP |
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
Looks for a value in the first column of a table and returns the value in the same row from another column. | Lookup | LO 198, HW (row shared by VLOOKUP, HLOOKUP) | Approximate match = binary search on ascending data (last of equal items); exact match linear with *, ?, ~. An empty range_lookup placeholder (VLOOKUP(a,b,2,)) means FALSE. Text never matches numbers ("5" ≠ 5) and logical values only match logical values — LibreOffice differs on both. |
XLOOKUP |
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
Searches a range for a value and returns the corresponding item from a second range; returns a reference when the return range is one. | Lookup | LO 365, HW (row shared by XMATCH, XLOOKUP) | Match modes 0, −1, 1, 2; search modes 1, −1, 2, −2. Binary modes return the first of several equal items on ascending data and the last on descending data (LibreOffice's behaviour; Excel documents none). Modes −1/1 with a linear search compare only items of the lookup value's type. An empty lookup value matches empty cells and "". XLOOKUP returns a reference when return_array is a reference and there is one lookup value. With several lookup values each result is the first cell of the matching row/column. |
XMATCH |
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) |
Relative position of a value in a range, with exact, next-smaller, next-larger or wildcard matching and forward, reverse or binary search. | Lookup | LO 365, HW (row shared by XMATCH, XLOOKUP) | Match modes 0, −1, 1, 2; search modes 1, −1, 2, −2. Binary modes return the first of several equal items on ascending data and the last on descending data (LibreOffice's behaviour; Excel documents none). Modes −1/1 with a linear search compare only items of the lookup value's type. An empty lookup value matches empty cells and "". XLOOKUP returns a reference when return_array is a reference and there is one lookup value. With several lookup values each result is the first cell of the matching row/column. |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Dynamic Array (22 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
BYCOL |
BYCOL(array, lambda) |
Applies a LAMBDA to each column and returns a row of results. | Lookup | PY 40, HW (row shared by MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
BYROW |
BYROW(array, lambda) |
Applies a LAMBDA to each row and returns a column of results. | Lookup | PY 40, HW (row shared by MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
CHOOSECOLS |
CHOOSECOLS(array, col_num1, [col_num2], …) |
Returns the given columns of an array (negative numbers count from the end). | Lookup | LO+PY 23, HW (row shared by CHOOSEROWS, CHOOSECOLS) | Index 0 or out of range → #VALUE!. |
CHOOSEROWS |
CHOOSEROWS(array, row_num1, [row_num2], …) |
Returns the given rows of an array (negative numbers count from the end). | Lookup | LO+PY 23, HW (row shared by CHOOSEROWS, CHOOSECOLS) | Index 0 or out of range → #VALUE!. |
DROP |
DROP(array, rows, [columns]) |
Removes the first (or, with a negative count, last) rows and columns of an array. | Lookup | LO+PY 68, HW (row shared by TAKE, DROP) | Zero count / nothing left → #CALC!. |
EXPAND |
EXPAND(array, rows, [columns], [pad_with]) |
Pads an array to the given dimensions (with #N/A unless pad_with is given). | Lookup | LO+PY 9, PY 1, HW | Smaller target → #VALUE!; default padding #N/A. |
FILTER |
FILTER(array, include, [if_empty]) |
Keeps the rows (or columns) of an array for which the include vector is TRUE. | Lookup | LO+PY 22, HW | include must be a column of rows items or a row of columns items; numbers ≠ 0 count as TRUE; text is #VALUE!. Empty result: if_empty or #CALC!. |
HSTACK |
HSTACK(array1, [array2], …) |
Appends arrays side by side; shorter arrays are padded with #N/A. | Lookup | LO+PY 13, HW (row shared by HSTACK, VSTACK) | Padding #N/A. |
MAKEARRAY |
MAKEARRAY(rows, columns, lambda) |
Builds an array by calling a LAMBDA(row, column) for every cell. | Lookup | PY 40, HW (row shared by MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
MAP |
MAP(array1, [array2], …, lambda) |
Applies a LAMBDA to each element of one or more arrays and returns the array of results. | Lookup | PY 40, HW (row shared by MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
REDUCE |
REDUCE([initial_value], array, lambda) |
Reduces an array to an accumulated value by applying a LAMBDA(accumulator, value) to each element. | Lookup | PY 40, HW (row shared by MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
SCAN |
SCAN([initial_value], array, lambda) |
Applies a LAMBDA(accumulator, value) to each element and returns every intermediate value. | Lookup | PY 40, HW (row shared by MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) | Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range. |
SEQUENCE |
SEQUENCE(rows, [columns], [start], [step]) |
Array of sequential numbers, filled row by row. | Lookup | LO+PY 11, PY 2, HW | SEQUENCE(0) → #CALC!, negative size → #VALUE!. |
SORT |
SORT(array, [sort_index], [sort_order], [by_col]) |
Sorts the rows (or columns) of an array; stable, numbers before text before booleans. | Lookup | LO+PY 35, PY 6, HW (row shared by SORT, SORTBY) | Stable. Order: numbers < text < FALSE < TRUE < errors, empty cells last in both directions (the rule of Excel's Sort command; the position of errors and empty cells was not verified against the SORT function). Mixed-type cases are Python-only (LibreOffice sorts logical values as numbers). |
SORTBY |
SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …) |
Sorts an array by the values of one or more corresponding vectors. | Lookup | LO+PY 35, PY 6, HW (row shared by SORT, SORTBY) | Stable. Order: numbers < text < FALSE < TRUE < errors, empty cells last in both directions (the rule of Excel's Sort command; the position of errors and empty cells was not verified against the SORT function). Mixed-type cases are Python-only (LibreOffice sorts logical values as numbers). |
TAKE |
TAKE(array, rows, [columns]) |
First (or, with a negative count, last) rows and columns of an array. | Lookup | LO+PY 68, HW (row shared by TAKE, DROP) | Zero count / nothing left → #CALC!. |
TOCOL |
TOCOL(array, [ignore], [scan_by_column]) |
Flattens an array into one column (ignore: 1 blanks, 2 errors, 3 both). | Lookup | LO+PY 21, PY 1, HW (row shared by TOCOL, TOROW) | Everything ignored → #CALC!. |
TOROW |
TOROW(array, [ignore], [scan_by_column]) |
Flattens an array into one row (ignore: 1 blanks, 2 errors, 3 both). | Lookup | LO+PY 21, PY 1, HW (row shared by TOCOL, TOROW) | Everything ignored → #CALC!. |
UNIQUE |
UNIQUE(array, [by_col], [exactly_once]) |
Distinct rows (or columns) of an array, in order of first appearance; optionally only those occurring once. | Lookup | LO+PY 13, PY 2, HW | Case-insensitive; 1 ≠ "1"; an empty cell, "" and 0 are three distinct values (Excel's handling of empty cells versus "" was not verified). |
VSTACK |
VSTACK(array1, [array2], …) |
Appends arrays one below the other; narrower arrays are padded with #N/A. | Lookup | LO+PY 13, HW (row shared by HSTACK, VSTACK) | Padding #N/A. |
WRAPCOLS |
WRAPCOLS(vector, wrap_count, [pad_with]) |
Wraps a row or column vector into columns of wrap_count values. | Lookup | LO+PY 27, HW (row shared by WRAPROWS, WRAPCOLS) | Non-vector → #VALUE!; wrap_count < 1 → #NUM!. |
WRAPROWS |
WRAPROWS(vector, wrap_count, [pad_with]) |
Wraps a row or column vector into rows of wrap_count values. | Lookup | LO+PY 27, HW (row shared by WRAPROWS, WRAPCOLS) | Non-vector → #VALUE!; wrap_count < 1 → #NUM!. |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Database (12 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
DAVERAGE |
DAVERAGE(database, field, criteria) |
Averages the values of a field in the records that match the criteria. | Numeric | LibreOffice 24, hand-written 3 | |
DCOUNT |
DCOUNT(database, [field], criteria) |
Counts the cells containing numbers in a field of the matching records. | Numeric | LibreOffice 33, hand-written 3 | |
DCOUNTA |
DCOUNTA(database, [field], criteria) |
Counts the non-empty cells in a field of the matching records. | Numeric | LibreOffice 33, hand-written 5 | |
DGET |
DGET(database, field, criteria) |
The single value of a field in the record that matches the criteria. | Numeric | LibreOffice 32, hand-written 5 | LO ignores empty fields when counting matches |
DMAX |
DMAX(database, field, criteria) |
Largest value of a field in the records that match the criteria. | Numeric | LibreOffice 24, hand-written 2 | |
DMIN |
DMIN(database, field, criteria) |
Smallest value of a field in the records that match the criteria. | Numeric | LibreOffice 24, hand-written 1 | |
DPRODUCT |
DPRODUCT(database, field, criteria) |
Multiplies the values of a field in the records that match the criteria. | Numeric | LibreOffice 24, hand-written 1 | |
DSTDEV |
DSTDEV(database, field, criteria) |
Sample standard deviation of a field in the matching records. | Numeric | LibreOffice 24, hand-written 1 | LO returns 0 without matches |
DSTDEVP |
DSTDEVP(database, field, criteria) |
Population standard deviation of a field in the matching records. | Numeric | LibreOffice 24, hand-written 1 | |
DSUM |
DSUM(database, field, criteria) |
Adds the numbers of a field in the records that match the criteria. | Numeric | LibreOffice 27, hand-written 10 | |
DVAR |
DVAR(database, field, criteria) |
Sample variance of a field in the matching records. | Numeric | LibreOffice 24, hand-written 1 | LO returns 0 without matches |
DVARP |
DVARP(database, field, criteria) |
Population variance of a field in the matching records. | Numeric | LibreOffice 24, hand-written 1 |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Engineering (46 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
BESSELI |
BESSELI(x, n) |
Modified Bessel function of the first kind In(x). | Numeric | scipy/mpmath 105, hand-written 2 | exact (Excel itself ~7 digits) |
BESSELJ |
BESSELJ(x, n) |
Bessel function of the first kind Jn(x). | Numeric | scipy/mpmath 108, hand-written 3 | exact (Excel itself ~7 digits) |
BESSELK |
BESSELK(x, n) |
Modified Bessel function of the second kind Kn(x). | Numeric | scipy/mpmath 87, hand-written 2 | exact (Excel itself ~7 digits) |
BESSELY |
BESSELY(x, n) |
Bessel function of the second kind Yn(x). | Numeric | scipy/mpmath 84, hand-written 3 | exact (Excel itself ~7 digits) |
BIN2DEC |
BIN2DEC(number) |
Converts a binary number to decimal. | Numeric | LibreOffice 11, hand-written 5 | |
BIN2HEX |
BIN2HEX(number, [places]) |
Converts a binary number to hexadecimal. | Numeric | LibreOffice 20, hand-written 3 | |
BIN2OCT |
BIN2OCT(number, [places]) |
Converts a binary number to octal. | Numeric | LibreOffice 20, hand-written 3 | |
BITAND |
BITAND(number1, number2) |
Bitwise AND of two numbers. | Numeric | LibreOffice 10, hand-written 4 | LO truncates non-integers (Excel: #NUM!) |
BITLSHIFT |
BITLSHIFT(number, shift_amount) |
Number shifted left by a number of bits. | Numeric | LibreOffice 9, hand-written 2 | result >= 2^48 is #NUM! (unverified) |
BITOR |
BITOR(number1, number2) |
Bitwise OR of two numbers. | Numeric | LibreOffice 10, hand-written 2 | LO truncates non-integers |
BITRSHIFT |
BITRSHIFT(number, shift_amount) |
Number shifted right by a number of bits. | Numeric | LibreOffice 9, hand-written 2 | |
BITXOR |
BITXOR(number1, number2) |
Bitwise exclusive OR of two numbers. | Numeric | LibreOffice 10, hand-written 1 | LO truncates non-integers |
COMPLEX |
COMPLEX(real_num, i_num, [suffix]) |
Complex number from real and imaginary coefficients, as text. | Numeric | LibreOffice 11, hand-written 6 | |
CONVERT |
CONVERT(number, from_unit, to_unit) |
Converts a number from one measurement system to another. | Numeric | LibreOffice 223, scipy/mpmath 511, hand-written 8 | LO only for exact-definition units; u, eV, parsec, mmHg unverified |
DEC2BIN |
DEC2BIN(number, [places]) |
Converts a decimal number to binary. | Numeric | LibreOffice 44, hand-written 6 | |
DEC2HEX |
DEC2HEX(number, [places]) |
Converts a decimal number to hexadecimal. | Numeric | LibreOffice 42, hand-written 5 | LO floors negative non-integers |
DEC2OCT |
DEC2OCT(number, [places]) |
Converts a decimal number to octal. | Numeric | LibreOffice 40, hand-written 3 | |
DELTA |
DELTA(number1, [number2]) |
Tests whether two numbers are equal (1) or not (0). | Numeric | LibreOffice 6, hand-written 4 | |
ERF |
ERF(lower_limit, [upper_limit]) |
Error function integrated between two limits. | Numeric | LibreOffice 14, scipy/mpmath 30, hand-written 2 | |
ERF.PRECISE |
ERF.PRECISE(x) |
Error function. | Numeric | LibreOffice 7, scipy/mpmath 15, hand-written 1 | |
ERFC |
ERFC(x) |
Complementary error function. | Numeric | LibreOffice 7, scipy/mpmath 15, hand-written 1 | |
ERFC.PRECISE |
ERFC.PRECISE(x) |
Complementary error function. | Numeric | LibreOffice 7, scipy/mpmath 15, hand-written 1 | |
GESTEP |
GESTEP(number, [step]) |
Tests whether a number is greater than or equal to a threshold. | Numeric | LibreOffice 5, hand-written 5 | |
HEX2BIN |
HEX2BIN(number, [places]) |
Converts a hexadecimal number to binary. | Numeric | LibreOffice 26, hand-written 4 | |
HEX2DEC |
HEX2DEC(number) |
Converts a hexadecimal number to decimal. | Numeric | LibreOffice 14, hand-written 4 | |
HEX2OCT |
HEX2OCT(number, [places]) |
Converts a hexadecimal number to octal. | Numeric | LibreOffice 26, hand-written 3 | |
IMABS |
IMABS(inumber) |
Absolute value (modulus) of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 4 | |
IMAGINARY |
IMAGINARY(inumber) |
Imaginary coefficient of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 5 | |
IMARGUMENT |
IMARGUMENT(inumber) |
Argument θ of a complex number, in radians. | Numeric | LibreOffice 14, Python cmath 10, hand-written 2 | |
IMCONJUGATE |
IMCONJUGATE(inumber) |
Complex conjugate of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 2 | |
IMCOS |
IMCOS(inumber) |
Cosine of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 1 | |
IMDIV |
IMDIV(inumber1, inumber2) |
Quotient of two complex numbers. | Numeric | LibreOffice 28, Python cmath 40, hand-written 2 | LO allows mixing i and j |
IMEXP |
IMEXP(inumber) |
Exponential of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 1 | |
IMLN |
IMLN(inumber) |
Natural logarithm of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 2 | |
IMLOG10 |
IMLOG10(inumber) |
Base-10 logarithm of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 1 | |
IMLOG2 |
IMLOG2(inumber) |
Base-2 logarithm of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 1 | |
IMPOWER |
IMPOWER(inumber, number) |
Complex number raised to a real power. | Numeric | LibreOffice 70, Python cmath 60, hand-written 2 | |
IMPRODUCT |
IMPRODUCT(inumber1, [inumber2], …) |
Product of complex numbers. | Numeric | LibreOffice 49, Python cmath 80, hand-written 3 | |
IMREAL |
IMREAL(inumber) |
Real coefficient of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 2 | |
IMSIN |
IMSIN(inumber) |
Sine of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 1 | |
IMSQRT |
IMSQRT(inumber) |
Square root of a complex number. | Numeric | LibreOffice 14, Python cmath 10, hand-written 3 | |
IMSUB |
IMSUB(inumber1, inumber2) |
Difference of two complex numbers. | Numeric | LibreOffice 29, Python cmath 40, hand-written 2 | LO allows mixing i and j |
IMSUM |
IMSUM(inumber1, [inumber2], …) |
Sum of complex numbers. | Numeric | LibreOffice 49, Python cmath 80, hand-written 6 | LO allows mixing i and j |
OCT2BIN |
OCT2BIN(number, [places]) |
Converts a octal number to binary. | Numeric | LibreOffice 20, hand-written 3 | |
OCT2DEC |
OCT2DEC(number) |
Converts a octal number to decimal. | Numeric | LibreOffice 11, hand-written 3 | |
OCT2HEX |
OCT2HEX(number, [places]) |
Converts a octal number to hexadecimal. | Numeric | LibreOffice 20, hand-written 2 |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).
Information (21 registered)
| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |
|---|---|---|---|---|---|
CELL |
CELL(info_type, [reference]) |
Information about a cell: "address", "row", "col", "contents" or "type". Volatile. | Numeric | LibreOffice 7, hand-written 12 | Volatile. 5 info types |
ERROR.TYPE |
ERROR.TYPE(error_val) |
Number identifying an error value. | Numeric | LibreOffice 9, hand-written 5 | ORIX extensions: #SPILL! 9, #CALC! 14, #CIRC! 15 |
INFO |
INFO(type_text) |
Information about the environment: "system", "osversion", "release", "recalc", "numfile". Volatile. | Numeric | hand-written 4 | Volatile. 5 info types |
ISBLANK |
ISBLANK(value) |
TRUE when the value is an empty cell. | Numeric | LibreOffice 12, hand-written 2 | |
ISERR |
ISERR(value) |
TRUE when the value is an error other than #N/A. | Numeric | LibreOffice 12, hand-written 2 | |
ISERROR |
ISERROR(value) |
TRUE when the value is any error. | Numeric | LibreOffice 12, hand-written 2 | |
ISEVEN |
ISEVEN(number) |
TRUE when the number (truncated) is even. | Numeric | LibreOffice 10, hand-written 3 | |
ISFORMULA |
ISFORMULA(reference) |
TRUE when the referenced cell contains a formula. | Numeric | LibreOffice 2, hand-written 3 | |
ISLOGICAL |
ISLOGICAL(value) |
TRUE when the value is a logical value. | Numeric | LibreOffice 12, hand-written 1 | |
ISNA |
ISNA(value) |
TRUE when the value is the #N/A error. | Numeric | LibreOffice 12, hand-written 2 | |
ISNONTEXT |
ISNONTEXT(value) |
TRUE when the value is not text (including empty cells). | Numeric | LibreOffice 12, hand-written 1 | |
ISNUMBER |
ISNUMBER(value) |
TRUE when the value is a number. | Numeric | LibreOffice 12, hand-written 3 | LO has no logical type |
ISODD |
ISODD(number) |
TRUE when the number (truncated) is odd. | Numeric | LibreOffice 10, hand-written 2 | |
ISOMITTED |
ISOMITTED(argument) |
TRUE when a LAMBDA argument was omitted. | Numeric | hand-written 4 | unknown to LibreOffice |
ISREF |
ISREF(value) |
TRUE when the value is a reference. | Numeric | LibreOffice 4, hand-written 4 | |
ISTEXT |
ISTEXT(value) |
TRUE when the value is text. | Numeric | LibreOffice 12, hand-written 1 | |
N |
N(value) |
Value converted to a number (text becomes 0). | Numeric | LibreOffice 14, hand-written 4 | |
NA |
NA() |
The error value #N/A. | SheetCore | SheetCore unit tests (CoreTests.swift); no external reference corpus |
|
SHEET |
SHEET([value]) |
Sheet number of a reference or sheet name. | Numeric | LibreOffice 3, hand-written 4 | |
SHEETS |
SHEETS([reference]) |
Number of sheets in a reference (or in the workbook). | Numeric | LibreOffice 1, hand-written 2 | |
TYPE |
TYPE(value) |
Number indicating the data type of a value: 1 number, 2 text, 4 logical, 16 error, 64 array. | Numeric | LibreOffice 13, hand-written 8 | LO has no logical type |
Source: function-registry.json (name, signature, summary, volatility); validation and notes from the COMPATIBILITY.md of the origin package (FunctionsText: counts from function_references.json by src tag).