blob: 3946bcbc6226003e9fd02e1c465d8e9da3c39564 [file] [view]
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
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