<!--
 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 — 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:

  1. 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.)
  2. Added ocFindB, ocSearchB, ocReplaceB to opcode.hxx enum.
  3. Registered function names in core_resource.src (ODF and Excel sections,
     plus localized en-US text).
  4. Added HID_FUNC_FINDB, HID_FUNC_SEARCHB, HID_FUNC_REPLACEB to helpids.h
     and hidother.src.
  5. Added dispatch cases in interpr4.cxx.
  6. 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).
  7. Regression rows in calcval-cases14.tsv (FINDB.*, SEARCHB.*,
      REPLACEB.*; rewritten 2026-11-18).
  8. 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:

  1. Added SC_OPCODE_DATEDIF = 415 to compiler.hrc. Existing opcodes
     unchanged. (STOP_2_PAR is now 420 and LAST_OPCODE_ID 419.)
  2. Added ocDateDif to opcode.hxx enum.
  3. Registered "DATEDIF" name in core_resource.src (ODF and Excel sections,
     plus localized en-US text).
  4. Added HID_FUNC_DATEDIF to helpids.h and hidother.src.
  5. Added dispatch case in interpr4.cxx.
  6. 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.
  7. Added Function Wizard entry in scfuncs.src (Start_date, End_date, Format
     parameters, ID_FUNCTION_GRP_DATETIME group).
  8. Regression rows DATEDIF.* in calcval-cases14.tsv (rewritten
     2026-11-18).
  9. 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:

  1. Added SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc, opcode.hxx).
  2. Added ScIsoWeekNum() in interpr2.cxx using Date::GetWeekOfYear(MONDAY, 4).
  3. Added dispatch case ocIsoWeekNum in interpr4.cxx.
  4. 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.
  5. Added function wizard resource in scfuncs.src (1 argument, no mode;
     its ExtraData had one number too many, fixed 2026-11-18).
  6. Added HID_FUNC_ISOWEEKNUM in helpids.h and hidother.src.
  7. Added help topic func_isoweeknum.xhp, registered in makefile and 04060102.xhp.
  8. 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).
  9. 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:

  1. Inventory of native vs AddIn-only financial functions.
  2. Consistent criteria for opcode allocation, import/export,
     Function Wizard, and help registration.
  3. 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:

  1. Review ScZGZ() (interpr2.cxx:1198) for missing validation:
     zero/negative present value, zero periods, overflow/underflow.
  2. Add input validation (PushIllegalArgument for invalid inputs).
  3. English help exists (04060118.xhp, HID_FUNC_ZGZ).
  4. Verify Excel import/export mapping (`_xlfnodf.RRI` may cause
     Excel compatibility issues — Excel has RRI natively).
  5. 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:

  1. 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.
  2. xls: BIFF 261 reads as ERROR.TYPE; ERRORTYPE is still written as 261.
     xlsx: ERROR.TYPE reads as ERROR.TYPE.
  3. 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.
  4. 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:

  1. Added case ocNumberValue : ScNumberValue(); to the interpr4.cxx
     dispatch switch.
  2. 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.
  3. Regression rows NUMBERVALUE.* in calcval-cases14.tsv (rewritten
      2026-11-18).
  4. 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:

  1. Review ScGetPivotData() (interpr2.cxx:2972) for correctness.
  2. XLSX import exists (formulabase.cxx GETPIVOTDATA row, import-only);
     there is no xlsx export at all. Not run.
  3. Verify DataPilot dependency handling (refresh, multiple tables).
  4. 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:

  1. Review ScFDist() (interpr3.cxx:1588) — enforces exactly 3
     params; ODF allows 3–4 with cumulative flag.
  2. Review ScFInv() (interpr3.cxx:2154) — numerical inversion via
     iteration.
  3. Consider adding optional 4th cumulative parameter to FDIST.
  4. Review export naming: `_xlfnodf.FDIST`/`_xlfnodf.FINV` — Excel has
     these natively without _xlfn prefix; may cause compatibility
     issues.
  5. English help exists for both (04060182.xhp; FVERT is only the help ID
     name).
  6. Add test cases.

Rationale: Implementations present but export naming and parameter
  count are gaps. P2 priority.

Group 9 — HARMEAN
-----------------

Recommended sequence:

  1. Review ScHarMean() (interpr3.cxx:2712) — counts only x>0,
     errIllegalArgument for x<=0. Verify against ODF definition.
  2. English help exists (04060182.xhp; HARMITTEL is only the help ID name).
  3. BIFF 320 both ways (xlformula.cxx saFuncTable_4) and xlsx 320
     (formulabase.cxx).
  4. Add test cases.

Rationale: Implementation present and appears correct. No gap left in
  this audit's terms.

Group 10 — DDE (Deferred)
-------------------------

Recommended sequence:

  1. Evaluate platform support (Linux, Windows, macOS).
  2. Address security concerns (external application access,
     user confirmation).
  3. Consider whether DDE should be available in headless/server mode
     (currently disabled).
  4. 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):

- HARMEAN: nothing open

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.
