1<!-- 2 Licensed to the Apache Software Foundation (ASF) under one 3 or more contributor license agreements. See the NOTICE file 4 distributed with this work for additional information 5 regarding copyright ownership. The ASF licenses this file 6 to you under the Apache License, Version 2.0 (the 7 "License"); you may not use this file except in compliance 8 with the License. You may obtain a copy of the License at 9 10 http://www.apache.org/licenses/LICENSE-2.0 11 12 Unless required by applicable law or agreed to in writing, 13 software distributed under the License is distributed on an 14 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 15 KIND, either express or implied. See the License for the 16 specific language governing permissions and limitations 17 under the License. 18--> 19 20OpenFormula Function Compatibility Gap Audit — Inventory 21========================================================= 22 23Branch: openformula 24Starting commit: 6a2f742073 25Audit type: Read-only source inspection (no build, no runtime execution) 26 27Updated 2026-11-18 to match the code after the post-merge review. The 28working tree was built on macOS and Linux and the functions were run and, 29where Excel has them, compared with Excel 16; "run" in the Confidence column 30means that. The ODF Group column and DDE/GETPIVOTDATA ODF status are from 31the original audit and were not rechecked. 32 33Summary table of the 15 audited functions plus HYPGEOMDIST (H.9). 34 35| 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 | 36|----------|---------------|---------------|-----------|------|-----------------|-----------|------------------|-------------------|------------------|-------------------|--------------|----------|------------| 37| 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) | 38| 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) | 39| 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) | 40| 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| 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) | 42| 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) | 43| 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) | 44| 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) | 45| 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) | 46| 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) | 47| 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) | 48| 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) | 49| 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) | 50| 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) | 51| 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) | 52| 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) | 53 54Notes: 55 56- "Native Opcode" column lists SC_OPCODE_* constants from 57 main/formula/inc/formula/compiler.hrc. 58- "Evaluator" column lists the ScInterpreter::Sc*() implementation 59 or Analysis AddIn entry point. 60- "Help" column indicates whether a HID_FUNC_* help ID exists. Help ID 61 names are German (HID_FUNC_ZGZ, HID_FUNC_HARMITTEL); the help text is 62 English. 63- FDIST and FINV rows are the ODF functions (left tail), which AOO calls 64 F.DIST and F.INV since 2026-11-18. AOO's FDIST and FINV are ODF 65 LEGACY.FDIST and LEGACY.FINV and did not change. 66- "ODF Group" classification requires verification against the final 67 ODF 1.4 Part 4 specification (Small/Medium/Large Group). 68- Priority: P1 = ODF-required behavior missing/incorrect; P2 = Excel 69 compatibility gap; P3 = documentation/alias/minor edge case; 70 DEFER = substantial platform/application-level work or unresolved 71 spec/security questions. 72- Confidence: HIGH = direct source evidence; "run" = also run on builds, 73 "Excel 16" = also compared with Excel; "source" = read only. 74 75BIFF Compatibility 76------------------ 77 78See audit/openformula-function-gaps/biff-function-mapping-audit.md for the 79detailed BIFF import/export mapping audit and 80audit/openformula-function-gaps/biff-function-mapping.tsv for the 81machine-readable inventory. 82 83Key findings: 84 85- DATEDIF BIFF5 ID 351 was mapped to ocNoName (→ #NAME! on import) and 86 was fixed to ocDateDif in commit a330cbd51b. Since 2026-11-18 it is 87 also written to xls. 88- FINDB, SEARCHB, REPLACEB had no BIFF mapping; since 2026-11-18 they are 89 BIFF2 IDs 205-207, next to LEFTB/RIGHTB/MIDB/LENB (208-211), and xlsx 90 reads them. 91- ISOWEEKNUM (ocIsoWeekNum) is written as `_xlfn.ISOWEEKNUM` since 92 2026-11-18; WEEKNUM keeps its `_xlfnodf.ISOWEEKNUM` entry. 93- ERROR.TYPE (BIFF4 ID 261) reads as the new ocErrorTypeODF; ERRORTYPE 94 is still written as 261. 95- HYPGEOMDIST with the fifth argument cannot be written to xls (NA()). 96- DDE has a native opcode and evaluator but no BIFF mapping (intentional 97 limitation — DDE is not a BIFF function). 98- ODDFPRICE and ODDFYIELD are Analysis AddIn functions with no native 99 opcode and no BIFF mapping (intentional limitation). 100- OOXML future functions (IFNA, IFERROR, SKEWP, BITAND, BITOR, BITXOR, 101 BITLSHIFT, BITRSHIFT, ISOWEEKNUM, F.DIST, F.INV) use `_xlfn.` names and 102 ODF namespace functions (WEEKNUM as ISOWEEKNUM, NUMBERVALUE, RRI) use 103 `_xlfnodf.` names, with NOID/255 BIFF IDs. Excel shows #NAME? for the 104 `_xlfnodf.` ones. `_xlfnodf.FDIST` and `_xlfnodf.FINV`, which older 105 versions wrote for FDIST and FINV, import as FDIST and FINV. 106 107Classification summary (32 rows in biff-function-mapping.tsv): 108 109- Correct: 28 110- Limitations: 4 (DDE, ODDFPRICE, ODDFYIELD intentional; HYPGEOMDIST with 111 5 arguments) 112- Import defects: 0 113- Export defects: 0 114