OpenFormula Function Compatibility Gap Audit — Inventory ========================================================= Branch: openformula Starting commit: 6a2f742073 Audit type: Read-only source inspection (no build, no runtime execution) Updated 2026-11-18 to match the code after the post-merge review. The working tree was built on macOS and Linux and the functions were run and, where Excel has them, compared with Excel 16; "run" in the Confidence column means that. The ODF Group column and DDE/GETPIVOTDATA ODF status are from the original audit and were not rechecked. Summary table of the 15 audited functions plus HYPGEOMDIST (H.9). | 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 | |----------|---------------|---------------|-----------|------|-----------------|-----------|------------------|-------------------|------------------|-------------------|--------------|----------|------------| | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | | 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) | Notes: - "Native Opcode" column lists SC_OPCODE_* constants from main/formula/inc/formula/compiler.hrc. - "Evaluator" column lists the ScInterpreter::Sc*() implementation or Analysis AddIn entry point. - "Help" column indicates whether a HID_FUNC_* help ID exists. Help ID names are German (HID_FUNC_ZGZ, HID_FUNC_HARMITTEL); the help text is English. - FDIST and FINV rows are the ODF functions (left tail), which AOO calls F.DIST and F.INV since 2026-11-18. AOO's FDIST and FINV are ODF LEGACY.FDIST and LEGACY.FINV and did not change. - "ODF Group" classification requires verification against the final ODF 1.4 Part 4 specification (Small/Medium/Large Group). - Priority: P1 = ODF-required behavior missing/incorrect; P2 = Excel compatibility gap; P3 = documentation/alias/minor edge case; DEFER = substantial platform/application-level work or unresolved spec/security questions. - Confidence: HIGH = direct source evidence; "run" = also run on builds, "Excel 16" = also compared with Excel; "source" = read only. BIFF Compatibility ------------------ See audit/openformula-function-gaps/biff-function-mapping-audit.md for the detailed BIFF import/export mapping audit and audit/openformula-function-gaps/biff-function-mapping.tsv for the machine-readable inventory. Key findings: - DATEDIF BIFF5 ID 351 was mapped to ocNoName (→ #NAME! on import) and was fixed to ocDateDif in commit a330cbd51b. Since 2026-11-18 it is also written to xls. - FINDB, SEARCHB, REPLACEB had no BIFF mapping; since 2026-11-18 they are BIFF2 IDs 205-207, next to LEFTB/RIGHTB/MIDB/LENB (208-211), and xlsx reads them. - ISOWEEKNUM (ocIsoWeekNum) is written as `_xlfn.ISOWEEKNUM` since 2026-11-18; WEEKNUM keeps its `_xlfnodf.ISOWEEKNUM` entry. - ERROR.TYPE (BIFF4 ID 261) reads as the new ocErrorTypeODF; ERRORTYPE is still written as 261. - HYPGEOMDIST with the fifth argument cannot be written to xls (NA()). - DDE has a native opcode and evaluator but no BIFF mapping (intentional limitation — DDE is not a BIFF function). - ODDFPRICE and ODDFYIELD are Analysis AddIn functions with no native opcode and no BIFF mapping (intentional limitation). - OOXML future functions (IFNA, IFERROR, SKEWP, BITAND, BITOR, BITXOR, BITLSHIFT, BITRSHIFT, ISOWEEKNUM, F.DIST, F.INV) use `_xlfn.` names and ODF namespace functions (WEEKNUM as ISOWEEKNUM, NUMBERVALUE, RRI) use `_xlfnodf.` names, with NOID/255 BIFF IDs. Excel shows #NAME? for the `_xlfnodf.` ones. `_xlfnodf.FDIST` and `_xlfnodf.FINV`, which older versions wrote for FDIST and FINV, import as FDIST and FINV. Classification summary (32 rows in biff-function-mapping.tsv): - Correct: 28 - Limitations: 4 (DDE, ODDFPRICE, ODDFYIELD intentional; HYPGEOMDIST with 5 arguments) - Import defects: 0 - Export defects: 0