OpenFormula Function Compatibility Gap Audit — Implementation Roadmap
Branch: openformula Starting commit: 6a2f742073 Audit type: Read-only source inspection (roadmap is a proposal, not committed work)
This document proposes an implementation sequence for the 15 audited functions, grouping related work where appropriate. No production code changes have been made.
Updated 2026-11-18 to match the code after the post-merge review: the branch tip did not compile and several H.1-H.9 changes were wrong; the fixes are noted per group. The H.x regression rows were replaced by 99 rows checked against Excel 16 (end of test/odf-validate/calcval-cases14.tsv), which match on macOS and Linux builds.
Group 1 — Byte-Oriented Text Functions (FINDB, REPLACEB, SEARCHB) — COMPLETED
Status: Implemented in H.2.
Changes:
- Added SC_OPCODE_FINDB=412, SC_OPCODE_SEARCHB=413, SC_OPCODE_REPLACEB=414 to compiler.hrc. Existing opcodes (BITAND=404 through IFERROR=411) were NOT renumbered — new opcodes were added at the end of the function range. (The earlier note that XLS files store 8192 + opcode was wrong: TokenPool::nScTokenOff in tokstack.cxx is an in-memory offset.)
- Added ocFindB, ocSearchB, ocReplaceB to opcode.hxx enum.
- Registered function names in core_resource.src (ODF and Excel sections, plus localized en-US text).
- Added HID_FUNC_FINDB, HID_FUNC_SEARCHB, HID_FUNC_REPLACEB to helpids.h and hidother.src.
- Added dispatch cases in interpr4.cxx.
- Implemented ScFindB(), ScSearchB(), ScReplaceB() in interpr1.cxx using DBCS byte semantics (consistent with existing LENB/LEFTB/RIGHTB/ MIDB). Added lcl_BytePosToCharIndex() and lcl_CharIndexToBytePos() helpers for byte↔character position conversion. 2026-11-18: ScFindB() and ScSearchB() popped the two text arguments in the wrong order (FINDB(“b”;“abc”) searched “b” for “abc”); fixed. xls: now BIFF 205-207 both ways (xlformula.cxx saFuncTable_2), and Excel 16 calculates FINDB from an AOO xls; xlsx: read (formulabase.cxx, ODF names FINDB/SEARCHB/REPLACEB).
- Regression rows in calcval-cases14.tsv (FINDB., SEARCHB., REPLACEB.*; rewritten 2026-11-18).
- Added Function Wizard documentation in main/sc/source/ui/src/scfuncs.src (SC_OPCODE_FINDB, SC_OPCODE_SEARCHB, SC_OPCODE_REPLACEB resource blocks with parameter names and descriptions).
Byte semantics: DBCS model (1 byte for ASCII/non-DBCS chars, 2 bytes for DBCS chars per IsDBCS()). Consistent with existing LENB/LEFTB/RIGHTB/ MIDB implementations. Byte positions are 1-based. Partial DBCS character boundaries are avoided in REPLACEB by scanning forward byte-by-byte.
Rationale: These three functions were entirely missing from the byte- oriented text function family. Now implemented following the existing B-function patterns.
Group 2 — Date/Time Compatibility (DATEDIF, ISOWEEKNUM) — DATEDIF COMPLETED
DATEDIF Status: Implemented in H.3.
Changes:
- Added SC_OPCODE_DATEDIF = 415 to compiler.hrc. Existing opcodes unchanged. (STOP_2_PAR is now 420 and LAST_OPCODE_ID 419.)
- Added ocDateDif to opcode.hxx enum.
- Registered “DATEDIF” name in core_resource.src (ODF and Excel sections, plus localized en-US text).
- Added HID_FUNC_DATEDIF to helpids.h and hidother.src.
- Added dispatch case in interpr4.cxx.
- Implemented ScInterpreter::ScDateDif() in interpr2.cxx implementing ODF 1.4 §6.10.3 with all six formats (y, m, d, md, ym, yd). 2026-11-18: rewritten to Excel‘s behavior where ODF is silent (Jim’s decision): start after end is an error, the format ignores case, “md” and “yd” follow Excel; “yd” was wrong before. See datedif-semantic-design.md.
- Added Function Wizard entry in scfuncs.src (Start_date, End_date, Format parameters, ID_FUNCTION_GRP_DATETIME group).
- Regression rows DATEDIF.* in calcval-cases14.tsv (rewritten 2026-11-18).
- Fixed BIFF5/BIFF7 import mapping in xlformula.cxx: changed
ocNoName → ocDateDif for function ID 351 (DATEDIF). 2026-11-18: the import-only flag was dropped, so DATEDIF is written to xls too (Excel 16 calculates it); xlsx reads DATEDIF.
Semantic design: See datedif-semantic-design.md for detailed algorithm specification including reverse-date policy, time fraction handling, and month/year boundary semantics.
ISOWEEKNUM: COMPLETED in H.5 (commit on openformula branch).
Changes:
- Added SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc, opcode.hxx).
- Added ScIsoWeekNum() in interpr2.cxx using Date::GetWeekOfYear(MONDAY, 4).
- Added dispatch case ocIsoWeekNum in interpr4.cxx.
- Registered ISOWEEKNUM name in core_resource.src (ODF and Excel namespaces). The ODF list also has ISOWEEKNUM for ocWeek, which is how AOO writes WEEKNUM(d;mode); on reading, the first entry wins. 2026-11-18: FormulaCompiler::Factor turns an ODF ISOWEEKNUM with one argument into ocIsoWeekNum, so files from other applications load; two arguments stay WEEKNUM.
- Added function wizard resource in scfuncs.src (1 argument, no mode; its ExtraData had one number too many, fixed 2026-11-18).
- Added HID_FUNC_ISOWEEKNUM in helpids.h and hidother.src.
- Added help topic func_isoweeknum.xhp, registered in makefile and 04060102.xhp.
- Calcval rows ISOWEEKNUM.* (rewritten 2026-11-18; several SG.ISO expectations were wrong, e.g. week 1 for Sunday 2024-12-29). xls: written as
_xlfn.ISOWEEKNUM (2026-11-18). xlsx _xlfn.ISOWEEKNUM is read too (2026-11-18). - WEEKNUM (ScGetWeekOfYear) unchanged: retains 2-arg SUN/MON mode.
Rationale: ISOWEEKNUM needed separate opcode and evaluator for ISO 8601 compliance (1 arg, Monday start, week 1 = first Thursday). Now resolved.
Group 3 — Financial Odd-Period Functions (ODDFPRICE, ODDFYIELD)
Status: native opcodes DEFERRED — pending financial function registration policy. The Analysis add-in functions were implemented on 2026-11-18 (Jim's decision).
Rationale: H.6.1 opcode registration was reverted (f4f3de4f92). A broader financial function registration policy must be established before allocating native opcodes to ODDFPRICE/ODDFYIELD. This includes:
- Inventory of native vs AddIn-only financial functions.
- Consistent criteria for opcode allocation, import/export, Function Wizard, and help registration.
- Decision on whether ODDFPRICE/ODDFYIELD should receive native implementations or remain AddIn-only.
Current state: Implemented as Analysis AddIn only (financial.cxx getOddfprice/getOddfyield, analysishelper.cxx GetOddfprice/GetOddfyield). On trunk both threw (THROW_RTE, #87380#); 8ce50c3485 brought in an unfinished version. The 2026-11-18 code follows Excel‘s short and long odd first period formulas, requires the first coupon to be on the maturity’s coupon schedule (as Excel), and solves the yield with a bracketed Illinois iteration. Against Excel 16 on 4524 random cases: 14 differences, all classified (4 where DFC equals E and Excel gives #NUM!, 5 annual 30/360 bonds with Feb-end coupons off by 2/360 of a coupon, 5 where Excel's yield is less exact or wrong). No native opcode; Function Wizard and English help (04060118.xhp) come from the add-in.
PRIORITY: P2 (Excel compatibility gap). DEFERRED until policy defined.
Group 4 — RRI (Rate of Return)
Recommended sequence:
- Review ScZGZ() (interpr2.cxx:1198) for missing validation: zero/negative present value, zero periods, overflow/underflow.
- Add input validation (PushIllegalArgument for invalid inputs).
- English help exists (04060118.xhp, HID_FUNC_ZGZ).
- Verify Excel import/export mapping (
_xlfnodf.RRI may cause Excel compatibility issues — Excel has RRI natively). - Add test cases.
Rationale: Implementation exists but lacks validation (ScZGZ() checks nothing; ODF requires N > 0). Export naming may cause Excel compatibility issues.
2026-11-18: the ODF and English names are RRI (ZGZ is still read, through OpCodeMap::putOpCodeAlias). The RRI commit had also renamed ocZGZ, SC_OPCODE_ZGZ, ScZGZ() and HID_FUNC_ZGZ and reworded the help; that was undone, because translations are keyed on SC_OPCODE_ZGZ and the German function name ZGZ would have been lost.
Group 5 — ERROR.TYPE
Status: COMPLETED in H.4, redone 2026-11-18.
H.4 changed ScErrorType() itself, so ERRORTYPE (ODF name ORG.OPENOFFICE.ERRORTYPE) stopped returning the internal error numbers its help documents (Err:518 gives 518). Jim's decision: ERRORTYPE is back to the trunk behavior and ERROR.TYPE is its own opcode.
Changes now:
- SC_OPCODE_ERROR_TYPE_ODF = 419, ocErrorTypeODF, names “ERROR.TYPE” (ODF, English, UI). ScErrorType( bool bODF ) with lcl_ErrorTypeODF() in interpr1.cxx; the mapping is that of XclTools::GetXclErrorCode, so Err:502 gives 3, anything not listed 7. Listed in CASE_OCERRFUNC.
- xls: BIFF 261 reads as ERROR.TYPE; ERRORTYPE is still written as 261. xlsx: ERROR.TYPE reads as ERROR.TYPE.
- Help: func_errortype.xhp with its own help ID HID_FUNC_ERROR_TYPE_ODF (it had reused ERRORTYPE's), embedded in 04060109.xhp next to ERRORTYPE instead of the date functions page.
- Calcval rows
ERROR.TYPE.* and ERRORTYPE.legacy*.
Group 6 — NUMBERVALUE (Critical Gap) — COMPLETED
Status: Implemented in H.1.1 (commit on openformula branch); rewritten 2026-11-18 to the ODF step order and moved to interpr1.cxx next to VALUE.
Changes:
- Added case ocNumberValue : ScNumberValue(); to the interpr4.cxx dispatch switch.
- ScInterpreter::ScNumberValue() in interpr1.cxx, following ODF 1.2/1.4 §6.13.28: group separators removed only before the decimal separator, the first decimal separator becomes ‘.’, whitespace removed, trailing percent signs divide by 100, and the rest must be a plain xsd:float (lcl_IsPlainFloat()). LEN(DecimalSeparator) must be 1 and it must not occur in GroupSeparator. Omitted separators come from the locale; the locale group separator is dropped when it equals the given decimal separator.
- Regression rows NUMBERVALUE.* in calcval-cases14.tsv (rewritten 2026-11-18).
- Function Wizard resource in scfuncs.src: the trunk strings kept, unsuppressed, a third parameter added, parameters 2 and 3 optional (the H.1 block had one flag too few). Differences from Excel 16 (checked): empty text is an error (Excel 0); an empty GroupSeparator is accepted (Excel error); a two-character DecimalSeparator is an error (Excel uses the first).
Rationale: CRITICAL gap resolved — opcode (392) and name registered but no evaluator existed. Function now compiles and evaluates correctly. P1 priority resolved.
Group 7 — GETPIVOTDATA
Recommended sequence:
- Review ScGetPivotData() (interpr2.cxx:2972) for correctness.
- XLSX import exists (formulabase.cxx GETPIVOTDATA row, import-only); there is no xlsx export at all. Not run.
- Verify DataPilot dependency handling (refresh, multiple tables).
- Add test cases.
Rationale: Implementation present. DataPilot-dependent application-level function.
Group 8 — FDIST, FINV (Legacy Statistical)
2026-11-18: H.8/H.8.1 turned ocFDist/ocFInv into the ODF left-tail functions and added ocLegacyFDist/ocLegacyFInv, which changed the meaning of FDIST/FINV typed in every UI language, in macros and in .sxc files; the H.6.1 revert also dropped the new opcode defines, so the branch did not build. Jim's decision: FDIST and FINV stay as on trunk (right tail; ODF LEGACY.FDIST/LEGACY.FINV; xls 281/282), and the ODF left-tail functions are new opcodes F.DIST (SC_OPCODE_F_DIST_LEFT 417, ScFDistLeft()) and F.INV (SC_OPCODE_F_INV_LEFT 418, ScFInvLeft()), with ODF names FDIST and FINV. F.DIST takes an optional Cumulative (default TRUE), returns 0 for x < 0 as ODF says, and gives the density through GetBetaDistPDF(). F.DIST matches Excel 16 on 300 random cases; F.INV on 298 of 300, the other two within 3e-10 where the distribution is almost flat (p = 0.999, r2 = 1). xls: _xlfn.F.DIST/_xlfn.F.INV (Excel calculates them; F.DIST needs four arguments there), and _xlfnodf.FDIST/_xlfnodf.FINV from older files import as FDIST/FINV. Help: F.DIST and F.INV sections in 04060182.xhp.
Original recommended sequence:
- Review ScFDist() (interpr3.cxx:1588) — enforces exactly 3 params; ODF allows 3–4 with cumulative flag.
- Review ScFInv() (interpr3.cxx:2154) — numerical inversion via iteration.
- Consider adding optional 4th cumulative parameter to FDIST.
- Review export naming:
_xlfnodf.FDIST/_xlfnodf.FINV — Excel has these natively without _xlfn prefix; may cause compatibility issues. - English help exists for both (04060182.xhp; FVERT is only the help ID name).
- Add test cases.
Rationale: Implementations present but export naming and parameter count are gaps. P2 priority.
Group 9 — HARMEAN
Recommended sequence:
- Review ScHarMean() (interpr3.cxx:2712) — counts only x>0, errIllegalArgument for x<=0. Verify against ODF definition.
- English help exists (04060182.xhp; HARMITTEL is only the help ID name).
- BIFF 320 both ways (xlformula.cxx saFuncTable_4) and xlsx 320 (formulabase.cxx).
- Add test cases.
Rationale: Implementation present and appears correct. No gap left in this audit's terms.
Group 10 — DDE (Deferred)
Recommended sequence:
- Evaluate platform support (Linux, Windows, macOS).
- Address security concerns (external application access, user confirmation).
- Consider whether DDE should be available in headless/server mode (currently disabled).
- If implemented: add Excel import/export mappings, test cases.
Rationale: DEFER — requires substantial platform/application-level work; unresolved security and platform-appropriateness questions. Not a pure formula function.
Group 11 — HYPGEOMDIST Cumulative (H.9, added 2026-11-18)
H.9/H.9.1 added the optional fifth argument Cumulative (default FALSE). The optional argument was popped after the others, one line of the trunk PMF algorithm was changed (wrong in 12240 of 135750 exhaustive small cases), and the function header was lost in a later merge. 2026-11-18: the trunk algorithm itself gave wrong results for some large arguments (HYPGEOMDIST(900;1828;2033;4167) gave 3.2E+181 instead of 0.0219), so GetHypGeomDist() now multiplies the C(n,x) factors and the probability ratios in an order that keeps the product near 1 (same size limit as before). Cumulative sums the smaller tail with term ratios. Checked: all 635375 small cases against exact values (worst relative error 9e-14), 400 large cases against exact big-integer values, and Excel 16 HYPGEOM.DIST/HYPGEOMDIST on 895 cases. xls cannot hold five arguments (written as NA()).
Prioritization Summary
P1 (Critical — ODF-required behavior missing):
- ERROR.TYPE: RESOLVED in H.4
- NUMBERVALUE: RESOLVED in H.1
- FINDB/REPLACEB/SEARCHB: RESOLVED in H.2
- DATEDIF: RESOLVED in H.3/H.3.1
P1 (High — semantic gaps):
- ISOWEEKNUM: RESOLVED in H.5
P2 (Excel compatibility):
- ODDFPRICE/ODDFYIELD: add-in implemented 2026-11-18; native opcodes DEFERRED — pending financial registration policy
- RRI: no validation of N > 0; xls export as
_xlfnodf.RRI - GETPIVOTDATA: xlsx import present, not run
- F.DIST: xls export with three arguments shows #N/A in Excel
P3 (Minor):
DEFER (Platform/application-level):
- DDE: Requires DDE subsystem; security questions
Confidence Levels
Run and checked against Excel 16 (2026-11-18): FINDB, REPLACEB, SEARCHB (ASCII; CJK counts 2 bytes in AOO always), DATEDIF, ISOWEEKNUM, NUMBERVALUE, ERROR.TYPE, F.DIST, F.INV, FDIST, FINV, HYPGEOMDIST, ODDFPRICE, ODDFYIELD, RRI (one case). Source only: GETPIVOTDATA, HARMEAN, DDE.