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