xref: /trunk/audit/openformula-function-gaps/function-implementation-roadmap.md (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
14f909564SJim Jagielski<!--
24f909564SJim Jagielski Licensed to the Apache Software Foundation (ASF) under one
34f909564SJim Jagielski or more contributor license agreements.  See the NOTICE file
44f909564SJim Jagielski distributed with this work for additional information
54f909564SJim Jagielski regarding copyright ownership.  The ASF licenses this file
64f909564SJim Jagielski to you under the Apache License, Version 2.0 (the
74f909564SJim Jagielski "License"); you may not use this file except in compliance
84f909564SJim Jagielski with the License.  You may obtain a copy of the License at
94f909564SJim Jagielski
104f909564SJim Jagielski   http://www.apache.org/licenses/LICENSE-2.0
114f909564SJim Jagielski
124f909564SJim Jagielski Unless required by applicable law or agreed to in writing,
134f909564SJim Jagielski software distributed under the License is distributed on an
144f909564SJim Jagielski "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
154f909564SJim Jagielski KIND, either express or implied.  See the License for the
164f909564SJim Jagielski specific language governing permissions and limitations
174f909564SJim Jagielski under the License.
184f909564SJim Jagielski-->
194f909564SJim Jagielski
200224e3dcSPedro GiffuniOpenFormula Function Compatibility Gap Audit — Implementation Roadmap
210224e3dcSPedro Giffuni=====================================================================
220224e3dcSPedro Giffuni
230224e3dcSPedro GiffuniBranch: openformula
240224e3dcSPedro GiffuniStarting commit: 6a2f742073
250224e3dcSPedro GiffuniAudit type: Read-only source inspection (roadmap is a proposal, not committed work)
260224e3dcSPedro Giffuni
270224e3dcSPedro GiffuniThis document proposes an implementation sequence for the 15 audited
280224e3dcSPedro Giffunifunctions, grouping related work where appropriate. No production
290224e3dcSPedro Giffunicode changes have been made.
300224e3dcSPedro Giffuni
314f909564SJim JagielskiUpdated 2026-11-18 to match the code after the post-merge review: the branch
324f909564SJim Jagielskitip did not compile and several H.1-H.9 changes were wrong; the fixes are
334f909564SJim Jagielskinoted per group. The H.x regression rows were replaced by 99 rows checked
344f909564SJim Jagielskiagainst Excel 16 (end of test/odf-validate/calcval-cases14.tsv), which match
354f909564SJim Jagielskion macOS and Linux builds.
360224e3dcSPedro Giffuni
370224e3dcSPedro GiffuniGroup 1 — Byte-Oriented Text Functions (FINDB, REPLACEB, SEARCHB) — COMPLETED
380224e3dcSPedro Giffuni--------------------------------------------------------------------------------
390224e3dcSPedro Giffuni
400224e3dcSPedro GiffuniStatus: Implemented in H.2.
410224e3dcSPedro Giffuni
420224e3dcSPedro GiffuniChanges:
434f909564SJim Jagielski
440224e3dcSPedro Giffuni  1. Added SC_OPCODE_FINDB=412, SC_OPCODE_SEARCHB=413, SC_OPCODE_REPLACEB=414
450224e3dcSPedro Giffuni     to compiler.hrc. Existing opcodes (BITAND=404 through IFERROR=411) were
464f909564SJim Jagielski     NOT renumbered — new opcodes were added at the end of the function range.
474f909564SJim Jagielski     (The earlier note that XLS files store 8192 + opcode was wrong:
484f909564SJim Jagielski     TokenPool::nScTokenOff in tokstack.cxx is an in-memory offset.)
494f909564SJim Jagielski  2. Added ocFindB, ocSearchB, ocReplaceB to opcode.hxx enum.
500224e3dcSPedro Giffuni  3. Registered function names in core_resource.src (ODF and Excel sections,
510224e3dcSPedro Giffuni     plus localized en-US text).
520224e3dcSPedro Giffuni  4. Added HID_FUNC_FINDB, HID_FUNC_SEARCHB, HID_FUNC_REPLACEB to helpids.h
530224e3dcSPedro Giffuni     and hidother.src.
544f909564SJim Jagielski  5. Added dispatch cases in interpr4.cxx.
550224e3dcSPedro Giffuni  6. Implemented ScFindB(), ScSearchB(), ScReplaceB() in interpr1.cxx
560224e3dcSPedro Giffuni     using DBCS byte semantics (consistent with existing LENB/LEFTB/RIGHTB/
574f909564SJim Jagielski     MIDB). Added lcl_BytePosToCharIndex() and lcl_CharIndexToBytePos()
580224e3dcSPedro Giffuni     helpers for byte↔character position conversion.
594f909564SJim Jagielski     2026-11-18: ScFindB() and ScSearchB() popped the two text arguments in
604f909564SJim Jagielski     the wrong order (FINDB("b";"abc") searched "b" for "abc"); fixed.
614f909564SJim Jagielski     xls: now BIFF 205-207 both ways (xlformula.cxx saFuncTable_2), and
624f909564SJim Jagielski     Excel 16 calculates FINDB from an AOO xls; xlsx: read
634f909564SJim Jagielski     (formulabase.cxx, ODF names FINDB/SEARCHB/REPLACEB).
644f909564SJim Jagielski  7. Regression rows in calcval-cases14.tsv (FINDB.*, SEARCHB.*,
654f909564SJim Jagielski      REPLACEB.*; rewritten 2026-11-18).
660224e3dcSPedro Giffuni  8. Added Function Wizard documentation in main/sc/source/ui/src/scfuncs.src
670224e3dcSPedro Giffuni      (SC_OPCODE_FINDB, SC_OPCODE_SEARCHB, SC_OPCODE_REPLACEB resource
680224e3dcSPedro Giffuni      blocks with parameter names and descriptions).
690224e3dcSPedro Giffuni
700224e3dcSPedro GiffuniByte semantics: DBCS model (1 byte for ASCII/non-DBCS chars, 2 bytes for
710224e3dcSPedro Giffuni  DBCS chars per IsDBCS()). Consistent with existing LENB/LEFTB/RIGHTB/
720224e3dcSPedro Giffuni  MIDB implementations. Byte positions are 1-based. Partial DBCS character
730224e3dcSPedro Giffuni  boundaries are avoided in REPLACEB by scanning forward byte-by-byte.
740224e3dcSPedro Giffuni
750224e3dcSPedro GiffuniRationale: These three functions were entirely missing from the byte-
760224e3dcSPedro Giffuni  oriented text function family. Now implemented following the existing
770224e3dcSPedro Giffuni  B-function patterns.
780224e3dcSPedro Giffuni
79f8b15cbbSPedro GiffuniGroup 2 — Date/Time Compatibility (DATEDIF, ISOWEEKNUM) — DATEDIF COMPLETED
80f8b15cbbSPedro Giffuni--------------------------------------------------------------------------------
810224e3dcSPedro Giffuni
82f8b15cbbSPedro GiffuniDATEDIF Status: Implemented in H.3.
83f8b15cbbSPedro Giffuni
84f8b15cbbSPedro GiffuniChanges:
854f909564SJim Jagielski
864f909564SJim Jagielski  1. Added SC_OPCODE_DATEDIF = 415 to compiler.hrc. Existing opcodes
874f909564SJim Jagielski     unchanged. (STOP_2_PAR is now 420 and LAST_OPCODE_ID 419.)
88f8b15cbbSPedro Giffuni  2. Added ocDateDif to opcode.hxx enum.
89f8b15cbbSPedro Giffuni  3. Registered "DATEDIF" name in core_resource.src (ODF and Excel sections,
90f8b15cbbSPedro Giffuni     plus localized en-US text).
91f8b15cbbSPedro Giffuni  4. Added HID_FUNC_DATEDIF to helpids.h and hidother.src.
924f909564SJim Jagielski  5. Added dispatch case in interpr4.cxx.
934f909564SJim Jagielski  6. Implemented ScInterpreter::ScDateDif() in interpr2.cxx
94f8b15cbbSPedro Giffuni     implementing ODF 1.4 §6.10.3 with all six formats (y, m, d, md, ym, yd).
954f909564SJim Jagielski     2026-11-18: rewritten to Excel's behavior where ODF is silent (Jim's
964f909564SJim Jagielski     decision): start after end is an error, the format ignores case, "md"
974f909564SJim Jagielski     and "yd" follow Excel; "yd" was wrong before. See
984f909564SJim Jagielski     datedif-semantic-design.md.
99f8b15cbbSPedro Giffuni  7. Added Function Wizard entry in scfuncs.src (Start_date, End_date, Format
100f8b15cbbSPedro Giffuni     parameters, ID_FUNCTION_GRP_DATETIME group).
1014f909564SJim Jagielski  8. Regression rows DATEDIF.* in calcval-cases14.tsv (rewritten
1024f909564SJim Jagielski     2026-11-18).
1034f909564SJim Jagielski  9. Fixed BIFF5/BIFF7 import mapping in xlformula.cxx:
1044f909564SJim Jagielski     changed `ocNoName` → `ocDateDif` for function ID 351 (DATEDIF).
1054f909564SJim Jagielski     2026-11-18: the import-only flag was dropped, so DATEDIF is written
1064f909564SJim Jagielski     to xls too (Excel 16 calculates it); xlsx reads DATEDIF.
107f8b15cbbSPedro Giffuni
108f8b15cbbSPedro GiffuniSemantic design: See datedif-semantic-design.md for detailed algorithm
109f8b15cbbSPedro Giffunispecification including reverse-date policy, time fraction handling, and
110f8b15cbbSPedro Giffunimonth/year boundary semantics.
111f8b15cbbSPedro Giffuni
112517a2e3bSPedro GiffuniISOWEEKNUM: COMPLETED in H.5 (commit on openformula branch).
113f8b15cbbSPedro Giffuni
114517a2e3bSPedro GiffuniChanges:
1154f909564SJim Jagielski
116517a2e3bSPedro Giffuni  1. Added SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc, opcode.hxx).
117517a2e3bSPedro Giffuni  2. Added ScIsoWeekNum() in interpr2.cxx using Date::GetWeekOfYear(MONDAY, 4).
118517a2e3bSPedro Giffuni  3. Added dispatch case ocIsoWeekNum in interpr4.cxx.
119517a2e3bSPedro Giffuni  4. Registered ISOWEEKNUM name in core_resource.src (ODF and Excel namespaces).
1204f909564SJim Jagielski     The ODF list also has ISOWEEKNUM for ocWeek, which is how AOO writes
1214f909564SJim Jagielski     WEEKNUM(d;mode); on reading, the first entry wins. 2026-11-18:
1224f909564SJim Jagielski     FormulaCompiler::Factor turns an ODF ISOWEEKNUM with one argument into
1234f909564SJim Jagielski     ocIsoWeekNum, so files from other applications load; two arguments
1244f909564SJim Jagielski     stay WEEKNUM.
1254f909564SJim Jagielski  5. Added function wizard resource in scfuncs.src (1 argument, no mode;
1264f909564SJim Jagielski     its ExtraData had one number too many, fixed 2026-11-18).
127517a2e3bSPedro Giffuni  6. Added HID_FUNC_ISOWEEKNUM in helpids.h and hidother.src.
128517a2e3bSPedro Giffuni  7. Added help topic func_isoweeknum.xhp, registered in makefile and 04060102.xhp.
1294f909564SJim Jagielski  8. Calcval rows ISOWEEKNUM.* (rewritten 2026-11-18; several SG.ISO
1304f909564SJim Jagielski     expectations were wrong, e.g. week 1 for Sunday 2024-12-29).
1314f909564SJim Jagielski     xls: written as `_xlfn.ISOWEEKNUM` (2026-11-18). xlsx `_xlfn.ISOWEEKNUM`
132*83f26b2aSJim Jagielski     is read too (2026-11-18).
133517a2e3bSPedro Giffuni  9. WEEKNUM (ScGetWeekOfYear) unchanged: retains 2-arg SUN/MON mode.
1340224e3dcSPedro Giffuni
135517a2e3bSPedro GiffuniRationale: ISOWEEKNUM needed separate opcode and evaluator for ISO 8601
136517a2e3bSPedro Giffuni  compliance (1 arg, Monday start, week 1 = first Thursday). Now resolved.
1370224e3dcSPedro Giffuni
1380224e3dcSPedro GiffuniGroup 3 — Financial Odd-Period Functions (ODDFPRICE, ODDFYIELD)
1390224e3dcSPedro Giffuni---------------------------------------------------------------
1400224e3dcSPedro Giffuni
1414f909564SJim JagielskiStatus: native opcodes DEFERRED — pending financial function registration
1424f909564SJim Jagielskipolicy. The Analysis add-in functions were implemented on 2026-11-18 (Jim's
1434f909564SJim Jagielskidecision).
1440224e3dcSPedro Giffuni
1454f909564SJim JagielskiRationale: H.6.1 opcode registration was reverted (f4f3de4f92).
146ed55d629SPedro Giffuni  A broader financial function registration policy must be established
147ed55d629SPedro Giffuni  before allocating native opcodes to ODDFPRICE/ODDFYIELD. This includes:
1484f909564SJim Jagielski
149ed55d629SPedro Giffuni  1. Inventory of native vs AddIn-only financial functions.
150ed55d629SPedro Giffuni  2. Consistent criteria for opcode allocation, import/export,
151ed55d629SPedro Giffuni     Function Wizard, and help registration.
152ed55d629SPedro Giffuni  3. Decision on whether ODDFPRICE/ODDFYIELD should receive native
153ed55d629SPedro Giffuni     implementations or remain AddIn-only.
1548ce50c34SPedro Giffuni
155ed55d629SPedro GiffuniCurrent state: Implemented as Analysis AddIn only
1564f909564SJim Jagielski  (financial.cxx getOddfprice/getOddfyield, analysishelper.cxx
1574f909564SJim Jagielski  GetOddfprice/GetOddfyield). On trunk both threw (THROW_RTE, #87380#);
1584f909564SJim Jagielski  8ce50c3485 brought in an unfinished version. The 2026-11-18 code follows
1594f909564SJim Jagielski  Excel's short and long odd first period formulas, requires the first coupon
1604f909564SJim Jagielski  to be on the maturity's coupon schedule (as Excel), and solves the yield
1614f909564SJim Jagielski  with a bracketed Illinois iteration. Against Excel 16 on 4524 random cases:
1624f909564SJim Jagielski  14 differences, all classified (4 where DFC equals E and Excel gives #NUM!,
1634f909564SJim Jagielski  5 annual 30/360 bonds with Feb-end coupons off by 2/360 of a coupon, 5
1644f909564SJim Jagielski  where Excel's yield is less exact or wrong). No native opcode; Function
1654f909564SJim Jagielski  Wizard and English help (04060118.xhp) come from the add-in.
1668ce50c34SPedro Giffuni
167ed55d629SPedro GiffuniPRIORITY: P2 (Excel compatibility gap). DEFERRED until policy defined.
1680224e3dcSPedro Giffuni
1690224e3dcSPedro GiffuniGroup 4 — RRI (Rate of Return)
1700224e3dcSPedro Giffuni------------------------------
1710224e3dcSPedro Giffuni
172ed55d629SPedro GiffuniRecommended sequence:
1734f909564SJim Jagielski
174ed55d629SPedro Giffuni  1. Review ScZGZ() (interpr2.cxx:1198) for missing validation:
175ed55d629SPedro Giffuni     zero/negative present value, zero periods, overflow/underflow.
176ed55d629SPedro Giffuni  2. Add input validation (PushIllegalArgument for invalid inputs).
1774f909564SJim Jagielski  3. English help exists (04060118.xhp, HID_FUNC_ZGZ).
1784f909564SJim Jagielski  4. Verify Excel import/export mapping (`_xlfnodf.RRI` may cause
179ed55d629SPedro Giffuni     Excel compatibility issues — Excel has RRI natively).
180ed55d629SPedro Giffuni  5. Add test cases.
1810224e3dcSPedro Giffuni
1824f909564SJim JagielskiRationale: Implementation exists but lacks validation (ScZGZ() checks
1834f909564SJim Jagielski  nothing; ODF requires N > 0). Export naming may cause Excel compatibility
1844f909564SJim Jagielski  issues.
1850224e3dcSPedro Giffuni
1864f909564SJim Jagielski2026-11-18: the ODF and English names are RRI (ZGZ is still read, through
1874f909564SJim Jagielski  OpCodeMap::putOpCodeAlias). The RRI commit had also renamed ocZGZ,
1884f909564SJim Jagielski  SC_OPCODE_ZGZ, ScZGZ() and HID_FUNC_ZGZ and reworded the help; that was
1894f909564SJim Jagielski  undone, because translations are keyed on SC_OPCODE_ZGZ and the German
1904f909564SJim Jagielski  function name ZGZ would have been lost.
1910224e3dcSPedro Giffuni
1920224e3dcSPedro GiffuniGroup 5 — ERROR.TYPE
1930224e3dcSPedro Giffuni--------------------
1940224e3dcSPedro Giffuni
1954f909564SJim JagielskiStatus: COMPLETED in H.4, redone 2026-11-18.
1960224e3dcSPedro Giffuni
1974f909564SJim JagielskiH.4 changed ScErrorType() itself, so ERRORTYPE (ODF name
1984f909564SJim Jagielski  ORG.OPENOFFICE.ERRORTYPE) stopped returning the internal error numbers its
1994f909564SJim Jagielski  help documents (Err:518 gives 518). Jim's decision: ERRORTYPE is back to
2004f909564SJim Jagielski  the trunk behavior and ERROR.TYPE is its own opcode.
201cf30ea8bSPedro Giffuni
2024f909564SJim JagielskiChanges now:
2030224e3dcSPedro Giffuni
2044f909564SJim Jagielski  1. SC_OPCODE_ERROR_TYPE_ODF = 419, ocErrorTypeODF, names "ERROR.TYPE"
2054f909564SJim Jagielski     (ODF, English, UI). ScErrorType( bool bODF ) with lcl_ErrorTypeODF()
2064f909564SJim Jagielski     in interpr1.cxx; the mapping is that of XclTools::GetXclErrorCode, so
2074f909564SJim Jagielski     Err:502 gives 3, anything not listed 7. Listed in CASE_OCERRFUNC.
2084f909564SJim Jagielski  2. xls: BIFF 261 reads as ERROR.TYPE; ERRORTYPE is still written as 261.
2094f909564SJim Jagielski     xlsx: ERROR.TYPE reads as ERROR.TYPE.
2104f909564SJim Jagielski  3. Help: func_errortype.xhp with its own help ID HID_FUNC_ERROR_TYPE_ODF
2114f909564SJim Jagielski     (it had reused ERRORTYPE's), embedded in 04060109.xhp next to
2124f909564SJim Jagielski     ERRORTYPE instead of the date functions page.
2134f909564SJim Jagielski  4. Calcval rows `ERROR.TYPE.*` and ERRORTYPE.legacy*.
2140224e3dcSPedro Giffuni
2150224e3dcSPedro GiffuniGroup 6 — NUMBERVALUE (Critical Gap) — COMPLETED
2160224e3dcSPedro Giffuni------------------------------------------------
2170224e3dcSPedro Giffuni
2184f909564SJim JagielskiStatus: Implemented in H.1.1 (commit on openformula branch); rewritten
2194f909564SJim Jagielski2026-11-18 to the ODF step order and moved to interpr1.cxx next to VALUE.
2200224e3dcSPedro Giffuni
2210224e3dcSPedro GiffuniChanges:
2224f909564SJim Jagielski
2234f909564SJim Jagielski  1. Added case ocNumberValue : ScNumberValue(); to the interpr4.cxx
2240224e3dcSPedro Giffuni     dispatch switch.
2254f909564SJim Jagielski  2. ScInterpreter::ScNumberValue() in interpr1.cxx, following ODF 1.2/1.4
2264f909564SJim Jagielski     §6.13.28: group separators removed only before the decimal separator,
2274f909564SJim Jagielski     the first decimal separator becomes '.', whitespace removed, trailing
2284f909564SJim Jagielski     percent signs divide by 100, and the rest must be a plain xsd:float
2294f909564SJim Jagielski     (lcl_IsPlainFloat()). LEN(DecimalSeparator) must be 1 and it must not
2304f909564SJim Jagielski     occur in GroupSeparator. Omitted separators come from the locale; the
2314f909564SJim Jagielski     locale group separator is dropped when it equals the given decimal
2324f909564SJim Jagielski     separator.
2334f909564SJim Jagielski  3. Regression rows NUMBERVALUE.* in calcval-cases14.tsv (rewritten
2344f909564SJim Jagielski      2026-11-18).
2354f909564SJim Jagielski  4. Function Wizard resource in scfuncs.src: the trunk strings kept,
2364f909564SJim Jagielski      unsuppressed, a third parameter added, parameters 2 and 3 optional
2374f909564SJim Jagielski      (the H.1 block had one flag too few).
2384f909564SJim Jagielski   Differences from Excel 16 (checked): empty text is an error (Excel 0); an
2394f909564SJim Jagielski   empty GroupSeparator is accepted (Excel error); a two-character
2404f909564SJim Jagielski   DecimalSeparator is an error (Excel uses the first).
2410224e3dcSPedro Giffuni
2420224e3dcSPedro GiffuniRationale: CRITICAL gap resolved — opcode (392) and name registered
2430224e3dcSPedro Giffuni  but no evaluator existed. Function now compiles and evaluates
2440224e3dcSPedro Giffuni  correctly. P1 priority resolved.
2450224e3dcSPedro Giffuni
2460224e3dcSPedro GiffuniGroup 7 — GETPIVOTDATA
2470224e3dcSPedro Giffuni----------------------
2480224e3dcSPedro Giffuni
2490224e3dcSPedro GiffuniRecommended sequence:
2504f909564SJim Jagielski
2510224e3dcSPedro Giffuni  1. Review ScGetPivotData() (interpr2.cxx:2972) for correctness.
2524f909564SJim Jagielski  2. XLSX import exists (formulabase.cxx GETPIVOTDATA row, import-only);
2534f909564SJim Jagielski     there is no xlsx export at all. Not run.
2540224e3dcSPedro Giffuni  3. Verify DataPilot dependency handling (refresh, multiple tables).
2550224e3dcSPedro Giffuni  4. Add test cases.
2560224e3dcSPedro Giffuni
2574f909564SJim JagielskiRationale: Implementation present. DataPilot-dependent application-level
2584f909564SJim Jagielski  function.
2590224e3dcSPedro Giffuni
2600224e3dcSPedro GiffuniGroup 8 — FDIST, FINV (Legacy Statistical)
2610224e3dcSPedro Giffuni------------------------------------------
2620224e3dcSPedro Giffuni
2634f909564SJim Jagielski2026-11-18: H.8/H.8.1 turned ocFDist/ocFInv into the ODF left-tail
2644f909564SJim Jagielskifunctions and added ocLegacyFDist/ocLegacyFInv, which changed the meaning of
2654f909564SJim JagielskiFDIST/FINV typed in every UI language, in macros and in .sxc files; the H.6.1
2664f909564SJim Jagielskirevert also dropped the new opcode defines, so the branch did not build.
2674f909564SJim JagielskiJim's decision: FDIST and FINV stay as on trunk (right tail; ODF
2684f909564SJim JagielskiLEGACY.FDIST/LEGACY.FINV; xls 281/282), and the ODF left-tail functions are
2694f909564SJim Jagielskinew opcodes F.DIST (SC_OPCODE_F_DIST_LEFT 417, ScFDistLeft()) and F.INV
2704f909564SJim Jagielski(SC_OPCODE_F_INV_LEFT 418, ScFInvLeft()), with ODF names FDIST and FINV.
2714f909564SJim JagielskiF.DIST takes an optional Cumulative (default TRUE), returns 0 for x < 0 as
2724f909564SJim JagielskiODF says, and gives the density through GetBetaDistPDF(). F.DIST matches
2734f909564SJim JagielskiExcel 16 on 300 random cases; F.INV on 298 of 300, the other two within
2744f909564SJim Jagielski3e-10 where the distribution is almost flat (p = 0.999, r2 = 1). xls: `_xlfn.F.DIST`/`_xlfn.F.INV`
2754f909564SJim Jagielski(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
2764f909564SJim Jagielski04060182.xhp.
2774f909564SJim Jagielski
2784f909564SJim JagielskiOriginal recommended sequence:
2794f909564SJim Jagielski
2800224e3dcSPedro Giffuni  1. Review ScFDist() (interpr3.cxx:1588) — enforces exactly 3
2810224e3dcSPedro Giffuni     params; ODF allows 3–4 with cumulative flag.
2820224e3dcSPedro Giffuni  2. Review ScFInv() (interpr3.cxx:2154) — numerical inversion via
2830224e3dcSPedro Giffuni     iteration.
2840224e3dcSPedro Giffuni  3. Consider adding optional 4th cumulative parameter to FDIST.
2854f909564SJim Jagielski  4. Review export naming: `_xlfnodf.FDIST`/`_xlfnodf.FINV` — Excel has
2860224e3dcSPedro Giffuni     these natively without _xlfn prefix; may cause compatibility
2870224e3dcSPedro Giffuni     issues.
2884f909564SJim Jagielski  5. English help exists for both (04060182.xhp; FVERT is only the help ID
2894f909564SJim Jagielski     name).
2900224e3dcSPedro Giffuni  6. Add test cases.
2910224e3dcSPedro Giffuni
2920224e3dcSPedro GiffuniRationale: Implementations present but export naming and parameter
2930224e3dcSPedro Giffuni  count are gaps. P2 priority.
2940224e3dcSPedro Giffuni
2950224e3dcSPedro GiffuniGroup 9 — HARMEAN
2960224e3dcSPedro Giffuni-----------------
2970224e3dcSPedro Giffuni
2980224e3dcSPedro GiffuniRecommended sequence:
2994f909564SJim Jagielski
3000224e3dcSPedro Giffuni  1. Review ScHarMean() (interpr3.cxx:2712) — counts only x>0,
3010224e3dcSPedro Giffuni     errIllegalArgument for x<=0. Verify against ODF definition.
3024f909564SJim Jagielski  2. English help exists (04060182.xhp; HARMITTEL is only the help ID name).
3034f909564SJim Jagielski  3. BIFF 320 both ways (xlformula.cxx saFuncTable_4) and xlsx 320
3044f909564SJim Jagielski     (formulabase.cxx).
3050224e3dcSPedro Giffuni  4. Add test cases.
3060224e3dcSPedro Giffuni
3074f909564SJim JagielskiRationale: Implementation present and appears correct. No gap left in
3084f909564SJim Jagielski  this audit's terms.
3090224e3dcSPedro Giffuni
3100224e3dcSPedro GiffuniGroup 10 — DDE (Deferred)
3110224e3dcSPedro Giffuni-------------------------
3120224e3dcSPedro Giffuni
3130224e3dcSPedro GiffuniRecommended sequence:
3144f909564SJim Jagielski
3150224e3dcSPedro Giffuni  1. Evaluate platform support (Linux, Windows, macOS).
3160224e3dcSPedro Giffuni  2. Address security concerns (external application access,
3170224e3dcSPedro Giffuni     user confirmation).
3180224e3dcSPedro Giffuni  3. Consider whether DDE should be available in headless/server mode
3190224e3dcSPedro Giffuni     (currently disabled).
3200224e3dcSPedro Giffuni  4. If implemented: add Excel import/export mappings, test cases.
3210224e3dcSPedro Giffuni
3220224e3dcSPedro GiffuniRationale: DEFER — requires substantial platform/application-level
3230224e3dcSPedro Giffuni  work; unresolved security and platform-appropriateness questions.
3240224e3dcSPedro Giffuni  Not a pure formula function.
3250224e3dcSPedro Giffuni
3264f909564SJim JagielskiGroup 11 — HYPGEOMDIST Cumulative (H.9, added 2026-11-18)
3274f909564SJim Jagielski---------------------------------------------------------
3284f909564SJim Jagielski
3294f909564SJim JagielskiH.9/H.9.1 added the optional fifth argument Cumulative (default FALSE). The
3304f909564SJim Jagielskioptional argument was popped after the others, one line of the trunk PMF
3314f909564SJim Jagielskialgorithm was changed (wrong in 12240 of 135750 exhaustive small cases), and
3324f909564SJim Jagielskithe function header was lost in a later merge. 2026-11-18: the trunk
3334f909564SJim Jagielskialgorithm itself gave wrong results for some large arguments
3344f909564SJim Jagielski(HYPGEOMDIST(900;1828;2033;4167) gave 3.2E+181 instead of 0.0219), so
3354f909564SJim JagielskiGetHypGeomDist() now multiplies the C(n,x) factors and the probability ratios
3364f909564SJim Jagielskiin an order that keeps the product near 1 (same size limit as before).
3374f909564SJim JagielskiCumulative sums the smaller tail with term ratios. Checked: all 635375 small
3384f909564SJim Jagielskicases against exact values (worst relative error 9e-14), 400 large cases
3394f909564SJim Jagielskiagainst exact big-integer values, and Excel 16 HYPGEOM.DIST/HYPGEOMDIST on 895 cases.
3404f909564SJim Jagielskixls cannot hold five arguments (written as NA()).
3410224e3dcSPedro Giffuni
3420224e3dcSPedro GiffuniPrioritization Summary
3430224e3dcSPedro Giffuni----------------------
3440224e3dcSPedro Giffuni
3450224e3dcSPedro GiffuniP1 (Critical — ODF-required behavior missing):
3464f909564SJim Jagielski
3478ce50c34SPedro Giffuni- ERROR.TYPE: RESOLVED in H.4
3488ce50c34SPedro Giffuni- NUMBERVALUE: RESOLVED in H.1
3498ce50c34SPedro Giffuni- FINDB/REPLACEB/SEARCHB: RESOLVED in H.2
3508ce50c34SPedro Giffuni- DATEDIF: RESOLVED in H.3/H.3.1
3510224e3dcSPedro Giffuni
3520224e3dcSPedro GiffuniP1 (High — semantic gaps):
3534f909564SJim Jagielski
3548ce50c34SPedro Giffuni- ISOWEEKNUM: RESOLVED in H.5
3550224e3dcSPedro Giffuni
3560224e3dcSPedro GiffuniP2 (Excel compatibility):
3574f909564SJim Jagielski
3584f909564SJim Jagielski- ODDFPRICE/ODDFYIELD: add-in implemented 2026-11-18; native opcodes
3594f909564SJim Jagielski    DEFERRED — pending financial registration policy
3604f909564SJim Jagielski- RRI: no validation of N > 0; xls export as `_xlfnodf.RRI`
3614f909564SJim Jagielski- GETPIVOTDATA: xlsx import present, not run
3624f909564SJim Jagielski- F.DIST: xls export with three arguments shows #N/A in Excel
3630224e3dcSPedro Giffuni
3640224e3dcSPedro GiffuniP3 (Minor):
3654f909564SJim Jagielski
3664f909564SJim Jagielski- HARMEAN: nothing open
3670224e3dcSPedro Giffuni
3680224e3dcSPedro GiffuniDEFER (Platform/application-level):
3694f909564SJim Jagielski
3700224e3dcSPedro Giffuni- DDE: Requires DDE subsystem; security questions
3710224e3dcSPedro Giffuni
3720224e3dcSPedro GiffuniConfidence Levels
3730224e3dcSPedro Giffuni-----------------
3744f909564SJim Jagielski
3754f909564SJim JagielskiRun and checked against Excel 16 (2026-11-18): FINDB, REPLACEB, SEARCHB
3764f909564SJim Jagielski(ASCII; CJK counts 2 bytes in AOO always), DATEDIF, ISOWEEKNUM, NUMBERVALUE,
3774f909564SJim JagielskiERROR.TYPE, F.DIST, F.INV, FDIST, FINV, HYPGEOMDIST, ODDFPRICE, ODDFYIELD,
3784f909564SJim JagielskiRRI (one case). Source only: GETPIVOTDATA, HARMEAN, DDE.
379