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).

FunctionSupport StatusNative OpcodeEvaluatorHelpODF 1.4 DefinedODF GroupExcel XLS ImportExcel XLSX ImportExcel XLS ExportExcel XLSX ExportFailure TypePriorityConfidence
FINDBPresentocFindB(412)ScFindB()Yes (HID_FUNC_FINDB)Yes (§6.7.2)not checkedYes(BIFF2,ID205)YesYes(BIFF2,ID205)n/a (no xlsx export)None; argument order fixed 2026-11-18ResolvedHIGH (run, Excel 16)
REPLACEBPresentocReplaceB(414)ScReplaceB()Yes (HID_FUNC_REPLACEB)Yes (§6.7.6)not checkedYes(BIFF2,ID207)YesYes(BIFF2,ID207)n/a (no xlsx export)NoneResolvedHIGH (run, Excel 16)
SEARCHBPresentocSearchB(413)ScSearchB()Yes (HID_FUNC_SEARCHB)Yes (§6.7.8)not checkedYes(BIFF2,ID206)YesYes(BIFF2,ID206)n/a (no xlsx export)None; argument order fixed 2026-11-18; regular expressions, no wildcardsResolvedHIGH (run, Excel 16)
DATEDIFPresentocDateDif(415)ScDateDif()Yes (HID_FUNC_DATEDIF)Yes (§6.10.3)Large Group (not rechecked)Yes(BIFF5,ID351)YesYes(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 ExcelResolvedHIGH (run, Excel 16)
ISOWEEKNUMPresentocIsoWeekNum(416)ScIsoWeekNum()Yes (HID_FUNC_ISOWEEKNUM)YesLarge 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 WEEKNUMResolvedHIGH (run, Excel 16)
DDEPresent (platform-dependent)ocDde(369)ScDde()Yes (HID_FUNC_DDE)not checkedN/ANoNoNon/a (no xlsx export)Platform-dependent; no file-format mappingDEFERHIGH (source)
ODDFPRICEPresent (AddIn)NonegetOddfprice() (AddIn)Yes (HID_AAI_FUNC_ODDFPRICE)Yes (§6.12.31)not checkedNoNoNon/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)
ODDFYIELDPresent (AddIn)NonegetOddfyield() (AddIn)Yes (HID_AAI_FUNC_ODDFYIELD)Yes (§6.12.32)not checkedNoNoNon/a (no xlsx export)Implemented 2026-11-18; bracketed Illinois solverResolved (native opcode deferred)HIGH (run, Excel 16)
RRIPresentocZGZ(256)ScZGZ()Yes (HID_FUNC_ZGZ, 04060118.xhp)Yes (§6.12.44)not checkedYes(_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 aliasP3HIGH (run, Excel 16)
ERROR.TYPEPresentocErrorTypeODF(419)ScErrorType(true)Yes (HID_FUNC_ERROR_TYPE_ODF)Yes (§6.13.11)Large Group (not rechecked)Yes(BIFF4,ID261)YesYes(BIFF4,ID261)n/a (no xlsx export)Own opcode since 2026-11-18 (Jim); mapping like XclTools::GetXclErrorCode; ERRORTYPE unchangedResolvedHIGH (run, Excel 16)
NUMBERVALUEPresentocNumberValue(392)ScNumberValue()Yes (HID_FUNC_NUMBERVALUE)Yes (§6.13.28)not checkedYes(_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 purposeResolvedHIGH (run, Excel 16)
GETPIVOTDATAPresentocGetPivotData(390)ScGetPivotData()Yes (HID_FUNC_GETPIVOTDATA)not checkedN/AYes(BIFF8,ID358)Yes (import-only row, not run)Yes(BIFF8)n/a (no xlsx export)DataPilot-dependentP3HIGH (source)
FDISTPresentocFDistLeft(417) for ODF FDIST; ocFDist(315) is LEGACY.FDISTScFDistLeft()Yes (HID_FUNC_F_DIST_LEFT)Yes (§6.18.22)N/AYes(_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 unchangedResolvedHIGH (run, Excel 16)
FINVPresentocFInvLeft(418) for ODF FINV; ocFInv(359) is LEGACY.FINVScFInvLeft()Yes (HID_FUNC_F_INV_LEFT)Yes (§6.18.24)N/AYes(_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 unchangedResolvedHIGH (run, Excel 16)
HYPGEOMDISTPresentocHypGeomDist(312)ScHypGeomDist()Yes (HID_FUNC_HYPGEOMVERT)Yes (§6.18.37)not checkedYes(BIFF4,ID289)YesYes(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)ResolvedHIGH (run, Excel 16)
HARMEANPresentocHarMean(321)ScHarMean()Yes (HID_FUNC_HARMITTEL, 04060182.xhp)Yes (not rechecked)not checkedYes(BIFF4,ID320)YesYes(BIFF4,ID320)n/a (no xlsx export)None foundP3HIGH (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