xref: /trunk/audit/openformula-function-gaps/function-gap-inventory.md (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
14f909564SJim Jagielski<!--
24f909564SJim Jagielski Licensed to the Apache Software Foundation (ASF) under one
34f909564SJim Jagielski or more contributor license agreements.  See the NOTICE file
44f909564SJim Jagielski distributed with this work for additional information
54f909564SJim Jagielski regarding copyright ownership.  The ASF licenses this file
64f909564SJim Jagielski to you under the Apache License, Version 2.0 (the
74f909564SJim Jagielski "License"); you may not use this file except in compliance
84f909564SJim Jagielski with the License.  You may obtain a copy of the License at
94f909564SJim Jagielski
104f909564SJim Jagielski   http://www.apache.org/licenses/LICENSE-2.0
114f909564SJim Jagielski
124f909564SJim Jagielski Unless required by applicable law or agreed to in writing,
134f909564SJim Jagielski software distributed under the License is distributed on an
144f909564SJim Jagielski "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
154f909564SJim Jagielski KIND, either express or implied.  See the License for the
164f909564SJim Jagielski specific language governing permissions and limitations
174f909564SJim Jagielski under the License.
184f909564SJim Jagielski-->
194f909564SJim Jagielski
200224e3dcSPedro GiffuniOpenFormula Function Compatibility Gap Audit — Inventory
210224e3dcSPedro Giffuni=========================================================
220224e3dcSPedro Giffuni
230224e3dcSPedro GiffuniBranch: openformula
240224e3dcSPedro GiffuniStarting commit: 6a2f742073
250224e3dcSPedro GiffuniAudit type: Read-only source inspection (no build, no runtime execution)
260224e3dcSPedro Giffuni
274f909564SJim JagielskiUpdated 2026-11-18 to match the code after the post-merge review. The
284f909564SJim Jagielskiworking tree was built on macOS and Linux and the functions were run and,
294f909564SJim Jagielskiwhere Excel has them, compared with Excel 16; "run" in the Confidence column
304f909564SJim Jagielskimeans that. The ODF Group column and DDE/GETPIVOTDATA ODF status are from
314f909564SJim Jagielskithe original audit and were not rechecked.
324f909564SJim Jagielski
334f909564SJim JagielskiSummary table of the 15 audited functions plus HYPGEOMDIST (H.9).
340224e3dcSPedro Giffuni
350224e3dcSPedro Giffuni| Function | Support Status | Native Opcode | Evaluator | Help | ODF 1.4 Defined | ODF Group | Excel XLS Import | Excel XLSX Import | Excel XLS Export | Excel XLSX Export | Failure Type | Priority | Confidence |
360224e3dcSPedro Giffuni|----------|---------------|---------------|-----------|------|-----------------|-----------|------------------|-------------------|------------------|-------------------|--------------|----------|------------|
374f909564SJim Jagielski| FINDB | Present | ocFindB(412) | ScFindB() | Yes (HID_FUNC_FINDB) | Yes (§6.7.2) | not checked | Yes(BIFF2,ID205) | Yes | Yes(BIFF2,ID205) | n/a (no xlsx export) | None; argument order fixed 2026-11-18 | Resolved | HIGH (run, Excel 16) |
384f909564SJim Jagielski| REPLACEB | Present | ocReplaceB(414) | ScReplaceB() | Yes (HID_FUNC_REPLACEB) | Yes (§6.7.6) | not checked | Yes(BIFF2,ID207) | Yes | Yes(BIFF2,ID207) | n/a (no xlsx export) | None | Resolved | HIGH (run, Excel 16) |
394f909564SJim Jagielski| SEARCHB | Present | ocSearchB(413) | ScSearchB() | Yes (HID_FUNC_SEARCHB) | Yes (§6.7.8) | not checked | Yes(BIFF2,ID206) | Yes | Yes(BIFF2,ID206) | n/a (no xlsx export) | None; argument order fixed 2026-11-18; regular expressions, no wildcards | Resolved | HIGH (run, Excel 16) |
404f909564SJim Jagielski| DATEDIF | Present | ocDateDif(415) | ScDateDif() | Yes (HID_FUNC_DATEDIF) | Yes (§6.10.3) | Large Group (not rechecked) | Yes(BIFF5,ID351) | Yes | Yes(BIFF5,ID351) | n/a (no xlsx export) | Excel-compatible where ODF is silent (Jim): start>end error, case-insensitive, Excel md/yd; one Feb 29 yd case differs from Excel | Resolved | HIGH (run, Excel 16) |
41*83f26b2aSJim Jagielski| ISOWEEKNUM | Present | ocIsoWeekNum(416) | ScIsoWeekNum() | Yes (HID_FUNC_ISOWEEKNUM) | Yes | Large Group (not rechecked) | Yes(_xlfn.ISOWEEKNUM) | Yes (_xlfn.ISOWEEKNUM) | Yes(_xlfn.ISOWEEKNUM) | n/a (no xlsx export) | ODF one-argument ISOWEEKNUM read as ocIsoWeekNum (FormulaCompiler::Factor); two arguments stay WEEKNUM | Resolved | HIGH (run, Excel 16) |
424f909564SJim Jagielski| DDE | Present (platform-dependent) | ocDde(369) | ScDde() | Yes (HID_FUNC_DDE) | not checked | N/A | No | No | No | n/a (no xlsx export) | Platform-dependent; no file-format mapping | DEFER | HIGH (source) |
434f909564SJim Jagielski| ODDFPRICE | Present (AddIn) | None | getOddfprice() (AddIn) | Yes (HID_AAI_FUNC_ODDFPRICE) | Yes (§6.12.31) | not checked | No | No | No | n/a (no xlsx export) | Implemented 2026-11-18 to Excel formulas; 14 of 4524 cases differ from Excel (classified) | Resolved (native opcode deferred) | HIGH (run, Excel 16) |
444f909564SJim Jagielski| ODDFYIELD | Present (AddIn) | None | getOddfyield() (AddIn) | Yes (HID_AAI_FUNC_ODDFYIELD) | Yes (§6.12.32) | not checked | No | No | No | n/a (no xlsx export) | Implemented 2026-11-18; bracketed Illinois solver | Resolved (native opcode deferred) | HIGH (run, Excel 16) |
458501d2b6SJim Jagielski| RRI | Present | ocZGZ(256) | ScZGZ() | Yes (HID_FUNC_ZGZ, 04060118.xhp) | Yes (§6.12.44) | not checked | Yes(_xlfnodf.RRI) | Yes (_xlfn.RRI) | _xlfnodf.RRI (Excel #NAME?) | n/a (no xlsx export) | No validation of N > 0; ODF/English name RRI since 2026-11-18, ZGZ alias | P3 | HIGH (run, Excel 16) |
464f909564SJim Jagielski| ERROR.TYPE | Present | ocErrorTypeODF(419) | ScErrorType(true) | Yes (HID_FUNC_ERROR_TYPE_ODF) | Yes (§6.13.11) | Large Group (not rechecked) | Yes(BIFF4,ID261) | Yes | Yes(BIFF4,ID261) | n/a (no xlsx export) | Own opcode since 2026-11-18 (Jim); mapping like XclTools::GetXclErrorCode; ERRORTYPE unchanged | Resolved | HIGH (run, Excel 16) |
478501d2b6SJim Jagielski| NUMBERVALUE | Present | ocNumberValue(392) | ScNumberValue() | Yes (HID_FUNC_NUMBERVALUE) | Yes (§6.13.28) | not checked | Yes(_xlfnodf.NUMBERVALUE) | Yes (_xlfn.NUMBERVALUE) | _xlfnodf.NUMBERVALUE (Excel #NAME?) | n/a (no xlsx export) | ODF steps since 2026-11-18; empty text and empty GroupSeparator differ from Excel on purpose | Resolved | HIGH (run, Excel 16) |
484f909564SJim Jagielski| GETPIVOTDATA | Present | ocGetPivotData(390) | ScGetPivotData() | Yes (HID_FUNC_GETPIVOTDATA) | not checked | N/A | Yes(BIFF8,ID358) | Yes (import-only row, not run) | Yes(BIFF8) | n/a (no xlsx export) | DataPilot-dependent | P3 | HIGH (source) |
498501d2b6SJim Jagielski| FDIST | Present | ocFDistLeft(417) for ODF FDIST; ocFDist(315) is LEGACY.FDIST | ScFDistLeft() | Yes (HID_FUNC_F_DIST_LEFT) | Yes (§6.18.22) | N/A | Yes(`_xlfn.F.DIST`; `_xlfnodf.FDIST` reads as legacy FDIST) | Yes (`_xlfn.F.DIST`, 4 args) | `_xlfn.F.DIST` (Excel calculates 4 args, #N/A for 3) | n/a (no xlsx export) | New opcode 2026-11-18 (Jim); legacy FDIST unchanged | Resolved | HIGH (run, Excel 16) |
508501d2b6SJim Jagielski| FINV | Present | ocFInvLeft(418) for ODF FINV; ocFInv(359) is LEGACY.FINV | ScFInvLeft() | Yes (HID_FUNC_F_INV_LEFT) | Yes (§6.18.24) | N/A | Yes(`_xlfn.F.INV`; `_xlfnodf.FINV` reads as legacy FINV) | Yes (`_xlfn.F.INV`) | `_xlfn.F.INV` (Excel calculates it) | n/a (no xlsx export) | New opcode 2026-11-18 (Jim); legacy FINV unchanged | Resolved | HIGH (run, Excel 16) |
514f909564SJim Jagielski| HYPGEOMDIST | Present | ocHypGeomDist(312) | ScHypGeomDist() | Yes (HID_FUNC_HYPGEOMVERT) | Yes (§6.18.37) | not checked | Yes(BIFF4,ID289) | Yes | Yes(BIFF4,ID289; 5 args written as NA()) | n/a (no xlsx export) | Cumulative added (H.9); PMF replaced 2026-11-18 (trunk gave 3.2E+181 for large args) | Resolved | HIGH (run, Excel 16) |
524f909564SJim Jagielski| HARMEAN | Present | ocHarMean(321) | ScHarMean() | Yes (HID_FUNC_HARMITTEL, 04060182.xhp) | Yes (not rechecked) | not checked | Yes(BIFF4,ID320) | Yes | Yes(BIFF4,ID320) | n/a (no xlsx export) | None found | P3 | HIGH (source) |
530224e3dcSPedro Giffuni
540224e3dcSPedro GiffuniNotes:
554f909564SJim Jagielski
560224e3dcSPedro Giffuni- "Native Opcode" column lists SC_OPCODE_* constants from
570224e3dcSPedro Giffuni  main/formula/inc/formula/compiler.hrc.
580224e3dcSPedro Giffuni- "Evaluator" column lists the ScInterpreter::Sc*() implementation
590224e3dcSPedro Giffuni  or Analysis AddIn entry point.
604f909564SJim Jagielski- "Help" column indicates whether a HID_FUNC_* help ID exists. Help ID
614f909564SJim Jagielski  names are German (HID_FUNC_ZGZ, HID_FUNC_HARMITTEL); the help text is
624f909564SJim Jagielski  English.
634f909564SJim Jagielski- FDIST and FINV rows are the ODF functions (left tail), which AOO calls
644f909564SJim Jagielski  F.DIST and F.INV since 2026-11-18. AOO's FDIST and FINV are ODF
654f909564SJim Jagielski  LEGACY.FDIST and LEGACY.FINV and did not change.
660224e3dcSPedro Giffuni- "ODF Group" classification requires verification against the final
670224e3dcSPedro Giffuni  ODF 1.4 Part 4 specification (Small/Medium/Large Group).
680224e3dcSPedro Giffuni- Priority: P1 = ODF-required behavior missing/incorrect; P2 = Excel
690224e3dcSPedro Giffuni  compatibility gap; P3 = documentation/alias/minor edge case;
700224e3dcSPedro Giffuni  DEFER = substantial platform/application-level work or unresolved
710224e3dcSPedro Giffuni  spec/security questions.
724f909564SJim Jagielski- Confidence: HIGH = direct source evidence; "run" = also run on builds,
734f909564SJim Jagielski  "Excel 16" = also compared with Excel; "source" = read only.
7447032aefSPedro Giffuni
7547032aefSPedro GiffuniBIFF Compatibility
7647032aefSPedro Giffuni------------------
7747032aefSPedro Giffuni
7847032aefSPedro GiffuniSee audit/openformula-function-gaps/biff-function-mapping-audit.md for the
7947032aefSPedro Giffunidetailed BIFF import/export mapping audit and
8047032aefSPedro Giffuniaudit/openformula-function-gaps/biff-function-mapping.tsv for the
8147032aefSPedro Giffunimachine-readable inventory.
8247032aefSPedro Giffuni
8347032aefSPedro GiffuniKey findings:
844f909564SJim Jagielski
8547032aefSPedro Giffuni- DATEDIF BIFF5 ID 351 was mapped to ocNoName (→ #NAME! on import) and
864f909564SJim Jagielski  was fixed to ocDateDif in commit a330cbd51b. Since 2026-11-18 it is
874f909564SJim Jagielski  also written to xls.
884f909564SJim Jagielski- FINDB, SEARCHB, REPLACEB had no BIFF mapping; since 2026-11-18 they are
894f909564SJim Jagielski  BIFF2 IDs 205-207, next to LEFTB/RIGHTB/MIDB/LENB (208-211), and xlsx
904f909564SJim Jagielski  reads them.
914f909564SJim Jagielski- ISOWEEKNUM (ocIsoWeekNum) is written as `_xlfn.ISOWEEKNUM` since
924f909564SJim Jagielski  2026-11-18; WEEKNUM keeps its `_xlfnodf.ISOWEEKNUM` entry.
934f909564SJim Jagielski- ERROR.TYPE (BIFF4 ID 261) reads as the new ocErrorTypeODF; ERRORTYPE
944f909564SJim Jagielski  is still written as 261.
954f909564SJim Jagielski- HYPGEOMDIST with the fifth argument cannot be written to xls (NA()).
9647032aefSPedro Giffuni- DDE has a native opcode and evaluator but no BIFF mapping (intentional
9747032aefSPedro Giffuni  limitation — DDE is not a BIFF function).
9847032aefSPedro Giffuni- ODDFPRICE and ODDFYIELD are Analysis AddIn functions with no native
9947032aefSPedro Giffuni  opcode and no BIFF mapping (intentional limitation).
10047032aefSPedro Giffuni- OOXML future functions (IFNA, IFERROR, SKEWP, BITAND, BITOR, BITXOR,
1014f909564SJim Jagielski  BITLSHIFT, BITRSHIFT, ISOWEEKNUM, F.DIST, F.INV) use `_xlfn.` names and
1024f909564SJim Jagielski  ODF namespace functions (WEEKNUM as ISOWEEKNUM, NUMBERVALUE, RRI) use
1034f909564SJim Jagielski  `_xlfnodf.` names, with NOID/255 BIFF IDs. Excel shows #NAME? for the
1044f909564SJim Jagielski  `_xlfnodf.` ones. `_xlfnodf.FDIST` and `_xlfnodf.FINV`, which older
1054f909564SJim Jagielski  versions wrote for FDIST and FINV, import as FDIST and FINV.
10647032aefSPedro Giffuni
1074f909564SJim JagielskiClassification summary (32 rows in biff-function-mapping.tsv):
1084f909564SJim Jagielski
1094f909564SJim Jagielski- Correct: 28
1104f909564SJim Jagielski- Limitations: 4 (DDE, ODDFPRICE, ODDFYIELD intentional; HYPGEOMDIST with
1114f909564SJim Jagielski  5 arguments)
1124f909564SJim Jagielski- Import defects: 0
11347032aefSPedro Giffuni- Export defects: 0
114