1<!-- 2 Licensed to the Apache Software Foundation (ASF) under one 3 or more contributor license agreements. See the NOTICE file 4 distributed with this work for additional information 5 regarding copyright ownership. The ASF licenses this file 6 to you under the Apache License, Version 2.0 (the 7 "License"); you may not use this file except in compliance 8 with the License. You may obtain a copy of the License at 9 10 http://www.apache.org/licenses/LICENSE-2.0 11 12 Unless required by applicable law or agreed to in writing, 13 software distributed under the License is distributed on an 14 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 15 KIND, either express or implied. See the License for the 16 specific language governing permissions and limitations 17 under the License. 18--> 19 20OpenFormula Function Compatibility Gap Audit — Implementation Roadmap 21===================================================================== 22 23Branch: openformula 24Starting commit: 6a2f742073 25Audit type: Read-only source inspection (roadmap is a proposal, not committed work) 26 27This document proposes an implementation sequence for the 15 audited 28functions, grouping related work where appropriate. No production 29code changes have been made. 30 31Updated 2026-11-18 to match the code after the post-merge review: the branch 32tip did not compile and several H.1-H.9 changes were wrong; the fixes are 33noted per group. The H.x regression rows were replaced by 99 rows checked 34against Excel 16 (end of test/odf-validate/calcval-cases14.tsv), which match 35on macOS and Linux builds. 36 37Group 1 — Byte-Oriented Text Functions (FINDB, REPLACEB, SEARCHB) — COMPLETED 38-------------------------------------------------------------------------------- 39 40Status: Implemented in H.2. 41 42Changes: 43 44 1. Added SC_OPCODE_FINDB=412, SC_OPCODE_SEARCHB=413, SC_OPCODE_REPLACEB=414 45 to compiler.hrc. Existing opcodes (BITAND=404 through IFERROR=411) were 46 NOT renumbered — new opcodes were added at the end of the function range. 47 (The earlier note that XLS files store 8192 + opcode was wrong: 48 TokenPool::nScTokenOff in tokstack.cxx is an in-memory offset.) 49 2. Added ocFindB, ocSearchB, ocReplaceB to opcode.hxx enum. 50 3. Registered function names in core_resource.src (ODF and Excel sections, 51 plus localized en-US text). 52 4. Added HID_FUNC_FINDB, HID_FUNC_SEARCHB, HID_FUNC_REPLACEB to helpids.h 53 and hidother.src. 54 5. Added dispatch cases in interpr4.cxx. 55 6. Implemented ScFindB(), ScSearchB(), ScReplaceB() in interpr1.cxx 56 using DBCS byte semantics (consistent with existing LENB/LEFTB/RIGHTB/ 57 MIDB). Added lcl_BytePosToCharIndex() and lcl_CharIndexToBytePos() 58 helpers for byte↔character position conversion. 59 2026-11-18: ScFindB() and ScSearchB() popped the two text arguments in 60 the wrong order (FINDB("b";"abc") searched "b" for "abc"); fixed. 61 xls: now BIFF 205-207 both ways (xlformula.cxx saFuncTable_2), and 62 Excel 16 calculates FINDB from an AOO xls; xlsx: read 63 (formulabase.cxx, ODF names FINDB/SEARCHB/REPLACEB). 64 7. Regression rows in calcval-cases14.tsv (FINDB.*, SEARCHB.*, 65 REPLACEB.*; rewritten 2026-11-18). 66 8. Added Function Wizard documentation in main/sc/source/ui/src/scfuncs.src 67 (SC_OPCODE_FINDB, SC_OPCODE_SEARCHB, SC_OPCODE_REPLACEB resource 68 blocks with parameter names and descriptions). 69 70Byte semantics: DBCS model (1 byte for ASCII/non-DBCS chars, 2 bytes for 71 DBCS chars per IsDBCS()). Consistent with existing LENB/LEFTB/RIGHTB/ 72 MIDB implementations. Byte positions are 1-based. Partial DBCS character 73 boundaries are avoided in REPLACEB by scanning forward byte-by-byte. 74 75Rationale: These three functions were entirely missing from the byte- 76 oriented text function family. Now implemented following the existing 77 B-function patterns. 78 79Group 2 — Date/Time Compatibility (DATEDIF, ISOWEEKNUM) — DATEDIF COMPLETED 80-------------------------------------------------------------------------------- 81 82DATEDIF Status: Implemented in H.3. 83 84Changes: 85 86 1. Added SC_OPCODE_DATEDIF = 415 to compiler.hrc. Existing opcodes 87 unchanged. (STOP_2_PAR is now 420 and LAST_OPCODE_ID 419.) 88 2. Added ocDateDif to opcode.hxx enum. 89 3. Registered "DATEDIF" name in core_resource.src (ODF and Excel sections, 90 plus localized en-US text). 91 4. Added HID_FUNC_DATEDIF to helpids.h and hidother.src. 92 5. Added dispatch case in interpr4.cxx. 93 6. Implemented ScInterpreter::ScDateDif() in interpr2.cxx 94 implementing ODF 1.4 §6.10.3 with all six formats (y, m, d, md, ym, yd). 95 2026-11-18: rewritten to Excel's behavior where ODF is silent (Jim's 96 decision): start after end is an error, the format ignores case, "md" 97 and "yd" follow Excel; "yd" was wrong before. See 98 datedif-semantic-design.md. 99 7. Added Function Wizard entry in scfuncs.src (Start_date, End_date, Format 100 parameters, ID_FUNCTION_GRP_DATETIME group). 101 8. Regression rows DATEDIF.* in calcval-cases14.tsv (rewritten 102 2026-11-18). 103 9. Fixed BIFF5/BIFF7 import mapping in xlformula.cxx: 104 changed `ocNoName` → `ocDateDif` for function ID 351 (DATEDIF). 105 2026-11-18: the import-only flag was dropped, so DATEDIF is written 106 to xls too (Excel 16 calculates it); xlsx reads DATEDIF. 107 108Semantic design: See datedif-semantic-design.md for detailed algorithm 109specification including reverse-date policy, time fraction handling, and 110month/year boundary semantics. 111 112ISOWEEKNUM: COMPLETED in H.5 (commit on openformula branch). 113 114Changes: 115 116 1. Added SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc, opcode.hxx). 117 2. Added ScIsoWeekNum() in interpr2.cxx using Date::GetWeekOfYear(MONDAY, 4). 118 3. Added dispatch case ocIsoWeekNum in interpr4.cxx. 119 4. Registered ISOWEEKNUM name in core_resource.src (ODF and Excel namespaces). 120 The ODF list also has ISOWEEKNUM for ocWeek, which is how AOO writes 121 WEEKNUM(d;mode); on reading, the first entry wins. 2026-11-18: 122 FormulaCompiler::Factor turns an ODF ISOWEEKNUM with one argument into 123 ocIsoWeekNum, so files from other applications load; two arguments 124 stay WEEKNUM. 125 5. Added function wizard resource in scfuncs.src (1 argument, no mode; 126 its ExtraData had one number too many, fixed 2026-11-18). 127 6. Added HID_FUNC_ISOWEEKNUM in helpids.h and hidother.src. 128 7. Added help topic func_isoweeknum.xhp, registered in makefile and 04060102.xhp. 129 8. Calcval rows ISOWEEKNUM.* (rewritten 2026-11-18; several SG.ISO 130 expectations were wrong, e.g. week 1 for Sunday 2024-12-29). 131 xls: written as `_xlfn.ISOWEEKNUM` (2026-11-18). xlsx `_xlfn.ISOWEEKNUM` 132 is read too (2026-11-18). 133 9. WEEKNUM (ScGetWeekOfYear) unchanged: retains 2-arg SUN/MON mode. 134 135Rationale: ISOWEEKNUM needed separate opcode and evaluator for ISO 8601 136 compliance (1 arg, Monday start, week 1 = first Thursday). Now resolved. 137 138Group 3 — Financial Odd-Period Functions (ODDFPRICE, ODDFYIELD) 139--------------------------------------------------------------- 140 141Status: native opcodes DEFERRED — pending financial function registration 142policy. The Analysis add-in functions were implemented on 2026-11-18 (Jim's 143decision). 144 145Rationale: H.6.1 opcode registration was reverted (f4f3de4f92). 146 A broader financial function registration policy must be established 147 before allocating native opcodes to ODDFPRICE/ODDFYIELD. This includes: 148 149 1. Inventory of native vs AddIn-only financial functions. 150 2. Consistent criteria for opcode allocation, import/export, 151 Function Wizard, and help registration. 152 3. Decision on whether ODDFPRICE/ODDFYIELD should receive native 153 implementations or remain AddIn-only. 154 155Current state: Implemented as Analysis AddIn only 156 (financial.cxx getOddfprice/getOddfyield, analysishelper.cxx 157 GetOddfprice/GetOddfyield). On trunk both threw (THROW_RTE, #87380#); 158 8ce50c3485 brought in an unfinished version. The 2026-11-18 code follows 159 Excel's short and long odd first period formulas, requires the first coupon 160 to be on the maturity's coupon schedule (as Excel), and solves the yield 161 with a bracketed Illinois iteration. Against Excel 16 on 4524 random cases: 162 14 differences, all classified (4 where DFC equals E and Excel gives #NUM!, 163 5 annual 30/360 bonds with Feb-end coupons off by 2/360 of a coupon, 5 164 where Excel's yield is less exact or wrong). No native opcode; Function 165 Wizard and English help (04060118.xhp) come from the add-in. 166 167PRIORITY: P2 (Excel compatibility gap). DEFERRED until policy defined. 168 169Group 4 — RRI (Rate of Return) 170------------------------------ 171 172Recommended sequence: 173 174 1. Review ScZGZ() (interpr2.cxx:1198) for missing validation: 175 zero/negative present value, zero periods, overflow/underflow. 176 2. Add input validation (PushIllegalArgument for invalid inputs). 177 3. English help exists (04060118.xhp, HID_FUNC_ZGZ). 178 4. Verify Excel import/export mapping (`_xlfnodf.RRI` may cause 179 Excel compatibility issues — Excel has RRI natively). 180 5. Add test cases. 181 182Rationale: Implementation exists but lacks validation (ScZGZ() checks 183 nothing; ODF requires N > 0). Export naming may cause Excel compatibility 184 issues. 185 1862026-11-18: the ODF and English names are RRI (ZGZ is still read, through 187 OpCodeMap::putOpCodeAlias). The RRI commit had also renamed ocZGZ, 188 SC_OPCODE_ZGZ, ScZGZ() and HID_FUNC_ZGZ and reworded the help; that was 189 undone, because translations are keyed on SC_OPCODE_ZGZ and the German 190 function name ZGZ would have been lost. 191 192Group 5 — ERROR.TYPE 193-------------------- 194 195Status: COMPLETED in H.4, redone 2026-11-18. 196 197H.4 changed ScErrorType() itself, so ERRORTYPE (ODF name 198 ORG.OPENOFFICE.ERRORTYPE) stopped returning the internal error numbers its 199 help documents (Err:518 gives 518). Jim's decision: ERRORTYPE is back to 200 the trunk behavior and ERROR.TYPE is its own opcode. 201 202Changes now: 203 204 1. SC_OPCODE_ERROR_TYPE_ODF = 419, ocErrorTypeODF, names "ERROR.TYPE" 205 (ODF, English, UI). ScErrorType( bool bODF ) with lcl_ErrorTypeODF() 206 in interpr1.cxx; the mapping is that of XclTools::GetXclErrorCode, so 207 Err:502 gives 3, anything not listed 7. Listed in CASE_OCERRFUNC. 208 2. xls: BIFF 261 reads as ERROR.TYPE; ERRORTYPE is still written as 261. 209 xlsx: ERROR.TYPE reads as ERROR.TYPE. 210 3. Help: func_errortype.xhp with its own help ID HID_FUNC_ERROR_TYPE_ODF 211 (it had reused ERRORTYPE's), embedded in 04060109.xhp next to 212 ERRORTYPE instead of the date functions page. 213 4. Calcval rows `ERROR.TYPE.*` and ERRORTYPE.legacy*. 214 215Group 6 — NUMBERVALUE (Critical Gap) — COMPLETED 216------------------------------------------------ 217 218Status: Implemented in H.1.1 (commit on openformula branch); rewritten 2192026-11-18 to the ODF step order and moved to interpr1.cxx next to VALUE. 220 221Changes: 222 223 1. Added case ocNumberValue : ScNumberValue(); to the interpr4.cxx 224 dispatch switch. 225 2. ScInterpreter::ScNumberValue() in interpr1.cxx, following ODF 1.2/1.4 226 §6.13.28: group separators removed only before the decimal separator, 227 the first decimal separator becomes '.', whitespace removed, trailing 228 percent signs divide by 100, and the rest must be a plain xsd:float 229 (lcl_IsPlainFloat()). LEN(DecimalSeparator) must be 1 and it must not 230 occur in GroupSeparator. Omitted separators come from the locale; the 231 locale group separator is dropped when it equals the given decimal 232 separator. 233 3. Regression rows NUMBERVALUE.* in calcval-cases14.tsv (rewritten 234 2026-11-18). 235 4. Function Wizard resource in scfuncs.src: the trunk strings kept, 236 unsuppressed, a third parameter added, parameters 2 and 3 optional 237 (the H.1 block had one flag too few). 238 Differences from Excel 16 (checked): empty text is an error (Excel 0); an 239 empty GroupSeparator is accepted (Excel error); a two-character 240 DecimalSeparator is an error (Excel uses the first). 241 242Rationale: CRITICAL gap resolved — opcode (392) and name registered 243 but no evaluator existed. Function now compiles and evaluates 244 correctly. P1 priority resolved. 245 246Group 7 — GETPIVOTDATA 247---------------------- 248 249Recommended sequence: 250 251 1. Review ScGetPivotData() (interpr2.cxx:2972) for correctness. 252 2. XLSX import exists (formulabase.cxx GETPIVOTDATA row, import-only); 253 there is no xlsx export at all. Not run. 254 3. Verify DataPilot dependency handling (refresh, multiple tables). 255 4. Add test cases. 256 257Rationale: Implementation present. DataPilot-dependent application-level 258 function. 259 260Group 8 — FDIST, FINV (Legacy Statistical) 261------------------------------------------ 262 2632026-11-18: H.8/H.8.1 turned ocFDist/ocFInv into the ODF left-tail 264functions and added ocLegacyFDist/ocLegacyFInv, which changed the meaning of 265FDIST/FINV typed in every UI language, in macros and in .sxc files; the H.6.1 266revert also dropped the new opcode defines, so the branch did not build. 267Jim's decision: FDIST and FINV stay as on trunk (right tail; ODF 268LEGACY.FDIST/LEGACY.FINV; xls 281/282), and the ODF left-tail functions are 269new opcodes F.DIST (SC_OPCODE_F_DIST_LEFT 417, ScFDistLeft()) and F.INV 270(SC_OPCODE_F_INV_LEFT 418, ScFInvLeft()), with ODF names FDIST and FINV. 271F.DIST takes an optional Cumulative (default TRUE), returns 0 for x < 0 as 272ODF says, and gives the density through GetBetaDistPDF(). F.DIST matches 273Excel 16 on 300 random cases; F.INV on 298 of 300, the other two within 2743e-10 where the distribution is almost flat (p = 0.999, r2 = 1). xls: `_xlfn.F.DIST`/`_xlfn.F.INV` 275(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 27604060182.xhp. 277 278Original recommended sequence: 279 280 1. Review ScFDist() (interpr3.cxx:1588) — enforces exactly 3 281 params; ODF allows 3–4 with cumulative flag. 282 2. Review ScFInv() (interpr3.cxx:2154) — numerical inversion via 283 iteration. 284 3. Consider adding optional 4th cumulative parameter to FDIST. 285 4. Review export naming: `_xlfnodf.FDIST`/`_xlfnodf.FINV` — Excel has 286 these natively without _xlfn prefix; may cause compatibility 287 issues. 288 5. English help exists for both (04060182.xhp; FVERT is only the help ID 289 name). 290 6. Add test cases. 291 292Rationale: Implementations present but export naming and parameter 293 count are gaps. P2 priority. 294 295Group 9 — HARMEAN 296----------------- 297 298Recommended sequence: 299 300 1. Review ScHarMean() (interpr3.cxx:2712) — counts only x>0, 301 errIllegalArgument for x<=0. Verify against ODF definition. 302 2. English help exists (04060182.xhp; HARMITTEL is only the help ID name). 303 3. BIFF 320 both ways (xlformula.cxx saFuncTable_4) and xlsx 320 304 (formulabase.cxx). 305 4. Add test cases. 306 307Rationale: Implementation present and appears correct. No gap left in 308 this audit's terms. 309 310Group 10 — DDE (Deferred) 311------------------------- 312 313Recommended sequence: 314 315 1. Evaluate platform support (Linux, Windows, macOS). 316 2. Address security concerns (external application access, 317 user confirmation). 318 3. Consider whether DDE should be available in headless/server mode 319 (currently disabled). 320 4. If implemented: add Excel import/export mappings, test cases. 321 322Rationale: DEFER — requires substantial platform/application-level 323 work; unresolved security and platform-appropriateness questions. 324 Not a pure formula function. 325 326Group 11 — HYPGEOMDIST Cumulative (H.9, added 2026-11-18) 327--------------------------------------------------------- 328 329H.9/H.9.1 added the optional fifth argument Cumulative (default FALSE). The 330optional argument was popped after the others, one line of the trunk PMF 331algorithm was changed (wrong in 12240 of 135750 exhaustive small cases), and 332the function header was lost in a later merge. 2026-11-18: the trunk 333algorithm itself gave wrong results for some large arguments 334(HYPGEOMDIST(900;1828;2033;4167) gave 3.2E+181 instead of 0.0219), so 335GetHypGeomDist() now multiplies the C(n,x) factors and the probability ratios 336in an order that keeps the product near 1 (same size limit as before). 337Cumulative sums the smaller tail with term ratios. Checked: all 635375 small 338cases against exact values (worst relative error 9e-14), 400 large cases 339against exact big-integer values, and Excel 16 HYPGEOM.DIST/HYPGEOMDIST on 895 cases. 340xls cannot hold five arguments (written as NA()). 341 342Prioritization Summary 343---------------------- 344 345P1 (Critical — ODF-required behavior missing): 346 347- ERROR.TYPE: RESOLVED in H.4 348- NUMBERVALUE: RESOLVED in H.1 349- FINDB/REPLACEB/SEARCHB: RESOLVED in H.2 350- DATEDIF: RESOLVED in H.3/H.3.1 351 352P1 (High — semantic gaps): 353 354- ISOWEEKNUM: RESOLVED in H.5 355 356P2 (Excel compatibility): 357 358- ODDFPRICE/ODDFYIELD: add-in implemented 2026-11-18; native opcodes 359 DEFERRED — pending financial registration policy 360- RRI: no validation of N > 0; xls export as `_xlfnodf.RRI` 361- GETPIVOTDATA: xlsx import present, not run 362- F.DIST: xls export with three arguments shows #N/A in Excel 363 364P3 (Minor): 365 366- HARMEAN: nothing open 367 368DEFER (Platform/application-level): 369 370- DDE: Requires DDE subsystem; security questions 371 372Confidence Levels 373----------------- 374 375Run and checked against Excel 16 (2026-11-18): FINDB, REPLACEB, SEARCHB 376(ASCII; CJK counts 2 bytes in AOO always), DATEDIF, ISOWEEKNUM, NUMBERVALUE, 377ERROR.TYPE, F.DIST, F.INV, FDIST, FINV, HYPGEOMDIST, ODDFPRICE, ODDFYIELD, 378RRI (one case). Source only: GETPIVOTDATA, HARMEAN, DDE. 379