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 — Source-Level Findings 21===================================================================== 22 23Branch: openformula 24Starting commit: 6a2f742073 (CONVERT atm/Torr fix) 25PR #523 merge: 1354dfcdad 26Audit type: Read-only source inspection (no build, no runtime execution) 27 28This document records source-level findings for 15 Calc functions. 29All file paths and line numbers are relative to the repository root 30(/home/pedro/projects/aoo). 31 32Updated 2026-11-18 to match the code after the post-merge review. Line 33numbers are those of the 2026-11-18 working tree. "Run" means the working 34tree was built on macOS and Linux and the case was run; "Excel 16" means it 35was also compared with Excel. Sections 6, 12 and 15 were only re-read. 36 371\. FINDB 38-------- 39 40ODF 1.4: Defined (§6.7.2 in ODF 1.2 and 1.4: "The same as FIND, but using 41 byte positions"). Byte positions are implementation-dependent. 42ODF group: not checked. 43 44A. Function registration 45 46- Native opcode: SC_OPCODE_FINDB = 412 (compiler.hrc line 408). 47- Parser name: "FINDB" registered in core_resource.src (ODF and 48 Excel sections). 49- Function-name-to-opcode mapping: ocFindB → FINDB (opcode.hxx 50 line 307). 51- Evaluator implementation: ScInterpreter::ScFindB() at 52 main/sc/source/core/tool/interpr1.cxx:8260. Dispatch via 53 `case ocFindB : ScFindB();` at interpr4.cxx:3774. 54- Function parameter metadata: Via native function resource. 55- Help entry: HID_FUNC_FINDB (helpids.h line 769, hidother.src); 56 topic func_findb.xhp. 57- ODF formula namespace mapping: FINDB in ODF section of 58 core_resource.src. 59- Excel import/export mapping: BIFF2 ID 205 (xlformula.cxx line 211), 60 added 2026-11-18. xlsx: ODF name FINDB (formulabase.cxx line 442). 61 62B. Actual implementation: ScInterpreter::ScFindB() at interpr1.cxx:8260. 63 Implements case-sensitive byte-oriented search using DBCS byte 64 semantics (1 byte for ASCII/non-DBCS, 2 bytes for DBCS characters 65 per IsDBCS()). Uses lcl_BytePosToCharIndex() and 66 lcl_CharIndexToBytePos() helpers to convert between byte positions 67 (1-based) and character indices (0-based). Returns byte position 68 of match, or #VALUE! (PushNoValue) if not found or start invalid. 69 Until 2026-11-18 the two text arguments were popped in the wrong 70 order, so FINDB("b";"abc") searched "b" for "abc". 71 72C. Format compatibility: XLS read and written as BIFF 205; an AOO xls 73 opens in Excel 16 with the value (run). XLSX read (run, hand-made 74 file). AOO has no xlsx export. 75 76D. Documentation: Help topic func_findb.xhp, reachable through its help 77 ID since helpcontent2/util/scalc/makefile.mk lists it (2026-11-18). 78 Function Wizard entry exists in main/sc/source/ui/src/scfuncs.src 79 (SC_OPCODE_FINDB resource block with parameter names and descriptions). 80 81Failure type: None. 82Priority: Resolved (was P1). 83Confidence: HIGH — run; ASCII cases match Excel 16 (Excel counts CJK as 84 1 byte unless a DBCS language is set; AOO always 2). 85 862\. REPLACEB 87----------- 88 89ODF 1.4: Defined (§6.7.6: "As REPLACE, but using byte positions"). 90ODF group: not checked. 91 92A. Function registration 93 94- Native opcode: SC_OPCODE_REPLACEB = 414 (compiler.hrc line 410). 95- Parser name: "REPLACEB" registered in core_resource.src (ODF and 96 Excel sections). 97- Function-name-to-opcode mapping: ocReplaceB → REPLACEB 98 (opcode.hxx line 309). 99- Evaluator implementation: ScInterpreter::ScReplaceB() at 100 interpr1.cxx:8355. Dispatch via 101 `case ocReplaceB : ScReplaceB();` at interpr4.cxx:3776. 102- Function parameter metadata: Via native function resource. 103- Help entry: HID_FUNC_REPLACEB (helpids.h line 771, hidother.src); 104 topic func_replaceb.xhp. 105- ODF formula namespace mapping: REPLACEB in ODF section of 106 core_resource.src. 107- Excel import/export mapping: BIFF2 ID 207 (xlformula.cxx line 213), 108 added 2026-11-18. xlsx: formulabase.cxx line 444. 109 110B. Actual implementation: ScInterpreter::ScReplaceB() at interpr1.cxx:8355. 111 Implements byte-oriented replacement using DBCS byte semantics. 112 Converts start byte position to character index, counts bytes 113 forward to determine character count to erase (avoiding splitting 114 DBCS characters), then Erase/Insert like ScReplace(). Clamps 115 start position to string length + 1 (append at end) and clamps 116 count to available bytes. Returns PushIllegalArgument() for invalid 117 inputs. 118 119C. Format compatibility: XLS read and written as BIFF 207 (source). 120 XLSX read (run). 121 122D. Documentation: Help topic func_replaceb.xhp (help ID resolves since 123 2026-11-18). Function Wizard entry exists in scfuncs.src. 124 125Failure type: None. 126Priority: Resolved (was P1). 127Confidence: HIGH — run; ASCII cases match Excel 16. 128 1293\. SEARCHB 130---------- 131 132ODF 1.4: Defined (§6.7.8: "As SEARCH, but using byte positions"). 133ODF group: not checked. 134 135A. Function registration 136 137- Native opcode: SC_OPCODE_SEARCHB = 413 (compiler.hrc line 409). 138- Parser name: "SEARCHB" registered in core_resource.src (ODF and 139 Excel sections). 140- Function-name-to-opcode mapping: ocSearchB → SEARCHB 141 (opcode.hxx line 308). 142- Evaluator implementation: ScInterpreter::ScSearchB() at 143 interpr1.cxx:8302. Dispatch via 144 `case ocSearchB : ScSearchB();` at interpr4.cxx:3775. 145- Function parameter metadata: Via native function resource. 146- Help entry: HID_FUNC_SEARCHB (helpids.h line 770, hidother.src); 147 topic func_searchb.xhp. 148- ODF formula namespace mapping: SEARCHB in ODF section of 149 core_resource.src. 150- Excel import/export mapping: BIFF2 ID 206 (xlformula.cxx line 212), 151 added 2026-11-18. xlsx: formulabase.cxx line 443. 152 153B. Actual implementation: ScInterpreter::ScSearchB() at interpr1.cxx:8302. 154 Implements case-insensitive byte-oriented search using DBCS byte 155 semantics, following the same pattern as ScSearch() (utl::TextSearch 156 with case-insensitive flag; regular expressions when enabled, no ? or * 157 wildcards). Converts byte positions to/from character 158 indices using lcl_BytePosToCharIndex() and lcl_CharIndexToBytePos(). 159 Returns byte position of match, or #VALUE! (PushNoValue) if not found 160 or start invalid. The text arguments were popped in the wrong order 161 until 2026-11-18, as in FINDB. 162 163C. Format compatibility: XLS read and written as BIFF 206 (source). 164 XLSX read (run). 165 166D. Documentation: Help topic func_searchb.xhp (the wildcard claim and its 167 example were removed 2026-11-18). Function Wizard entry exists in 168 scfuncs.src. 169 170Failure type: None. 171Priority: Resolved (was P1). 172Confidence: HIGH — run; ASCII cases match Excel 16. 173 1744\. DATEDIF 175---------- 176 177ODF 1.4: Defined by ODF 1.4 Part 4, Section 6.10.3. 178ODF group: Large Group (Excel compatibility function; not rechecked). 179 180A. Function registration 181 182- Native opcode: SC_OPCODE_DATEDIF = 415 (compiler.hrc line 411). 183- Parser name: "DATEDIF" registered in core_resource.src (ODF and 184 Excel sections). 185- Function-name-to-opcode mapping: ocDateDif → DATEDIF (opcode.hxx 186 line 310). 187- Evaluator implementation: ScInterpreter::ScDateDif() at 188 main/sc/source/core/tool/interpr2.cxx:461. Dispatch via 189 `case ocDateDif : ScDateDif();` at interpr4.cxx:3777. 190- Function parameter metadata: Via native function resource. 191- Help entry: HID_FUNC_DATEDIF (helpids.h line 772, hidother.src); 192 topic func_datedif.xhp. 193- ODF formula namespace mapping: DATEDIF in ODF section of 194 core_resource.src. 195- Excel import/export mapping: BIFF5/BIFF7 function ID 351 maps to 196 ocDateDif both ways (xlformula.cxx line 346; the import-only flag 197 was dropped 2026-11-18). xlsx: formulabase.cxx line 671. 198 199B. Actual implementation: ScInterpreter::ScDateDif() at interpr2.cxx:461. 200 Implements ODF 1.4 §6.10.3 with all six formats (y, m, d, md, ym, yd). 201 Since 2026-11-18 (Jim's decision) it follows Excel where ODF is 202 silent: start after end is an error, the format ignores case, "md" 203 borrows the month before the end month and "yd" moves the end date into 204 the start year. The H.3 version negated reverse intervals, was 205 case-sensitive and computed "yd" wrongly. 206 Detailed algorithm in audit/openformula-function-gaps/datedif-semantic-design.md. 207 208C. Format compatibility 209 210- XLS import and export: BIFF ID 351 (run; Excel 16 calculates an AOO 211 xls). 212- XLSX import: read (run). 213- XLSX export: AOO has none. 214 215D. Documentation: Help topic func_datedif.xhp (rewritten 2026-11-18). 216 Function Wizard entry exists in main/sc/source/ui/src/scfuncs.src. 217 218Failure type: None. One Excel difference ("yd" from Feb 29, 2020 to 219 Mar 1, 2023). 220Priority: Resolved (was P1). 221Confidence: HIGH — run; 630 cases compared with Excel 16. 222 2235\. ISOWEEKNUM 224------------- 225 226ODF 1.4: Defined (ODF 1.2 §6.10.11, ODF 1.4 §6.10.12): one argument, 227 ISO 8601 week. No Mode parameter in either version. 228ODF group: not checked. 229 230A. Function registration 231 232- Native opcode: SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc line 412), 233 added in H.5. WEEKNUM is SC_OPCODE_WEEK. 234- Function-name-to-opcode mapping: ocIsoWeekNum → ISOWEEKNUM 235 (opcode.hxx line 311; core_resource.src lines 340, 693 and 1635). 236 The ODF list also maps ocWeek to "ISOWEEKNUM" (line 339), which is 237 how AOO has always written WEEKNUM(d;mode) to ODF; when reading, the 238 first entry wins, so FormulaCompiler::Factor (FormulaCompiler.cxx 239 line 1083) turns an ODF ISOWEEKNUM with one argument into 240 ocIsoWeekNum (2026-11-18). With two arguments it stays WEEKNUM. 241- Evaluator implementation: ScIsoWeekNum() at interpr2.cxx:240, 242 Date::GetWeekOfYear(MONDAY, 4). WEEKNUM is ScGetWeekOfYear() at 243 interpr2.cxx:227 and still takes exactly 2 arguments. 244- Help entry: HID_FUNC_ISOWEEKNUM (helpids.h line 506, hidother.src); 245 topic func_isoweeknum.xhp. 246- Excel mapping: `_xlfn.ISOWEEKNUM` in saFuncTable_Oox (xlformula.cxx 247 lines 385-386), added 2026-11-18. WEEKNUM keeps the 248 `_xlfnodf.ISOWEEKNUM` entry (line 422). 249 250B. Actual implementation: ISO 8601 (Monday start, week 1 holds the 251 first Thursday). The Function Wizard resource had one ExtraData number 252 too many; fixed 2026-11-18. 253 254C. Format compatibility 255 256- ODF: one-argument form read and written as ISOWEEKNUM; old files' 257 two-argument form read as WEEKNUM (run, .ods round trip). 258- XLS: written as `_xlfn.ISOWEEKNUM` and read back; Excel 16 calculates 259 it (run). 260- XLSX import: `_xlfn.ISOWEEKNUM` is read (run, 2026-11-18). 261 262D. Documentation: func_isoweeknum.xhp, embedded in 04060102.xhp; help 263 ID resolves since 2026-11-18. 264 265Failure type: None (xlsx import gap above). 266Priority: Resolved (was P1). 267Confidence: HIGH — run; 300 dates compared with Excel 16. 268 2696\. DDE 270------ 271 272ODF 1.4: Not defined by ODF 1.4 Part 4 (not rechecked). Excel-specific / 273 application-level. 274ODF group: Not applicable. 275 276A. Function registration 277 278- Native opcode: SC_OPCODE_DDE = 369 (compiler.hrc line 365). 279- Parser name: "DDE" in core_resource.src lines 345 (ODF) and 698 (Excel). 280- Function-name-to-opcode mapping: ocDde → DDE. 281- Evaluator implementation: ScDde() at interpr2.cxx:2247. 282 Takes 3–4 params: Item, Topic, Appl, [Mode]. 283- Function parameter metadata: Via native function resource. 284- Help entry: HID_FUNC_DDE (hidother.src:330, helpids.h:738). 285- ODF formula namespace mapping: DDE in both ODF and Excel sections. 286- Excel import mapping: Not in xlformula.cxx BIFF tables. 287- Excel export mapping: None. 288 289B. Actual implementation: ScDde() (interpr2.cxx:2247) creates or 290 finds an ScDdeLink via sfx2::LinkManager. Requires a document shell 291 with a LinkManager (fails with PushNoValue() for temporary documents 292 without DocShell). Sets recalc mode on load. Disables idle during 293 evaluation. DDE is platform-dependent (implemented via 294 sfx2/source/appl/impldde.cxx, appdde.cxx). DDE links can be created 295 new (not just opening existing links). Updates are driven by the 296 DDE link mechanism, not automatic per-recalc. External application 297 failures are handled via error codes. Security: no user confirmation 298 visible in source; DDE is disabled in server mode 299 (appdde.cxx:494, 526: "Im Serverbetrieb ist DDE abgeklemmt!"). 300 301C. Format compatibility: DDE is not a file-format function per se; 302 no XLS/XLSX import/export mapping for the DDE function itself. 303 DDE links (external references) are handled separately in Excel 304 filters (xilink.cxx, xelink.cxx, xehelper.cxx). 305 306D. Documentation: HID_FUNC_DDE help entry exists. 307 308Failure type: Platform-dependent application-level feature. 309 Not a pure formula function; requires DDE subsystem availability. 310Priority: DEFER — requires DDE service platform support; security 311 and platform-appropriateness questions unresolved. 312Confidence: HIGH — source evidence clear; implementation exists but 313 is platform-dependent. 314 3157\. ODDFPRICE 316------------ 317 318ODF 1.4: Defined (§6.12.31 in ODF 1.2 and 1.4). The spec gives no formula. 319ODF group: not checked. 320 321A. Function registration 322 323- Native opcode: NONE. Not a native Calc opcode (H.6.1 added one and 324 f4f3de4f92 reverted it). 325- Parser name: "ODDFPRICE" registered as AddIn in 326 odffmap.cxx line 127: 327 { "ODDFPRICE", "ODDFPRICE", false, 328 "com.sun.star.sheet.addin.Analysis.getOddfprice", 329 "COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETODDFPRICE" } 330- Function-name-to-opcode mapping: Via AddIn map, not native opcode. 331- Evaluator implementation: AnalysisAddIn::getOddfprice() at 332 scaddins/source/analysis/financial.cxx:404. Validates 333 fRate>=0, fYield>=0, fRedemp>0, frequency, nMat>nFirstCoup>nSettle>nIssue. 334 Delegates to GetOddfprice() (analysishelper.cxx:1038). 335- Function parameter metadata: Via AddIn collection (ScUnoAddInCollection). 336- Help entry: HID_AAI_FUNC_ODDFPRICE (hidother.src:414, helpids.h:318); 337 English topic in 04060118.xhp. 338- ODF formula namespace mapping: None (AddIn, not ODF namespace). 339- Excel import mapping: Not in xlformula.cxx BIFF tables. 340- Excel export mapping: None. 341 342B. Actual implementation: on trunk GetOddfprice() threw (THROW_RTE, 343 #87380#). 8ce50c3485 (message "No production code changes") brought in 344 an unfinished version: the long-period terms were multiplied by 0 and a 345 placeholder line did not compile. Jim chose to finish it; the 346 2026-11-18 version: 347 348- Requires the first coupon to be on the maturity's coupon schedule 349 (lcl_GetCouppcd), as Excel does (#NUM! otherwise). 350- Short first period (DFC < E): Excel's short formula, DSC, A and DFC 351 counted with ScaDate::getDiff(). 352- Long first period: Excel's long formula, summed per quasi-coupon 353 period; a whole quasi-coupon period inside the odd period counts as 1, 354 the quasi-coupon dates are stepped back by plain months, and Nq follows 355 Excel's count for month-end first coupons 356 (lcl_GetOddfQuasiPeriods()). 357 These rules were found by comparing with Excel 16, not taken from a 358 spec. 359 360C. Format compatibility: AddIn function; not part of BIFF/OOXML 361 function tables. Import/export via AddIn mechanism only. 362 363D. Documentation: Help entry exists (AAI = Analysis AddIn). 364 365Failure type: RESOLVED 2026-11-18 (was: throws unconditionally on trunk). 366 4524 random ODDFPRICE/ODDFYIELD cases compared with Excel 16: 14 367 differences in 4 classes (DFC equal to E where Excel gives #NUM!: 4; 368 annual 30/360 US bonds with Feb-end coupons, 2/360 of a coupon: 5; 369 Excel's yield less exact: 4; Excel's yield wrong: 1). 370Priority: P2 (native opcode deferred). 371Confidence: HIGH — run, Excel 16. 372 3738\. ODDFYIELD 374------------ 375 376ODF 1.4: Defined (§6.12.32 in ODF 1.2 and 1.4). 377ODF group: not checked. 378 379A. Function registration 380 381- Native opcode: NONE. 382- Parser name: "ODDFYIELD" registered as AddIn in odffmap.cxx line 128: 383 { "ODDFYIELD", "ODDFYIELD", false, 384 "com.sun.star.sheet.addin.Analysis.getOddfyield", ... } 385- Evaluator implementation: AnalysisAddIn::getOddfyield() at 386 financial.cxx:416. Validates fRate>=0, fPrice>0, fRedemp>0 (added 387 2026-11-18), frequency, nMat>nFirstCoup>nSettle>nIssue. Delegates to 388 GetOddfyield() (analysishelper.cxx:1177). 389- Help entry: HID_AAI_FUNC_ODDFYIELD (hidother.src:415, helpids.h:319). 390 391B. Actual Implementation: 2026-11-18. The price falls as the yield 392 rises, so GetOddfyield() brackets the yield (above -frequency) and 393 narrows it with the Illinois variant of regula falsi, stopping when the 394 price is within 1e-12 relative. No bracket or no convergence: #NUM!. 395 396C. Format compatibility: AddIn only; no BIFF/OOXML table mapping. 397 398D. Documentation: Help entry exists. 399 400Failure type: RESOLVED 2026-11-18 (was: throws unconditionally on trunk). 401 See ODDFPRICE for the Excel comparison; where the yields differ, 402 AOO's reproduces the price within 1e-10 in Excel's own ODDFPRICE, 403 Excel's within 1.8e-7. 404Priority: P2 (native opcode deferred). 405Confidence: HIGH — run, Excel 16. 406 4079\. RRI 408------ 409 410ODF 1.4: Defined (§6.12.44 in ODF 1.2 and 1.4; constraint N > 0). 411ODF group: not checked. 412 413A. Function registration 414 415- Native opcode: SC_OPCODE_ZGZ = 256 (compiler.hrc line 252). 416- Parser names: "RRI" in the ODF and English lists since 2026-11-18 417 (core_resource.src lines 216 and 569; trunk had "ZGZ" there, so 418 older ODF files hold of:ZGZ). "ZGZ" is still read through 419 OpCodeMap::putOpCodeAlias() (FormulaCompiler.cxx line 695), which adds 420 the name for input only. The UI name is RRI in en-US and ZGZ in 421 German (translation keyed on SC_OPCODE_ZGZ). 422- The RRI commit (6fefc3f19c) had renamed ocZGZ, SC_OPCODE_ZGZ, ScZGZ() 423 and HID_FUNC_ZGZ and added the alias with putOpCode(), which also 424 replaced the name AOO writes; both were undone 2026-11-18. 425- Excel mapping: saFuncTable_Odf line 428, `_xlfnodf.RRI`. 426- Evaluator implementation: ScZGZ() at interpr2.cxx:1295. 427 Takes exactly 3 params: periods, present value, future value. 428 Computes pow(Fv/Pv, 1/N) - 1. Format: percent. 429- Help entry: HID_FUNC_ZGZ (hidother.src:136, helpids.h:528); English 430 RRI topic in 04060118.xhp. 431 432B. Actual implementation: ScZGZ() computes pow(Fv/Pv, 1/N) - 1 and 433 checks nothing; ODF requires N > 0. 8ce50c3485 reclassified the earlier 434 validation concern as a test-data error. The formula itself is right: 435 RRI(10;100;200) = 0.0717734625362931, as Excel 16. 436 437C. Format compatibility 438 439- ODF: of:RRI written; of:ZGZ and of:RRI read (run). 440- XLS: `_xlfnodf.RRI`; AOO reads it back, Excel shows #NAME? (run). 441- XLSX import: `_xlfn.RRI` is read (run, 2026-11-18). 442 443D. Documentation: English help (04060118.xhp, unchanged from trunk). 444 445Failure type: No N > 0 check; xls/xlsx naming. 446Priority: P3. 447Confidence: HIGH — run. 448 44910\. ERROR.TYPE 450-------------- 451 452ODF 1.4: Defined (§6.13.11 in ODF 1.2 and 1.4). Not propagating; a 453 non-error gives an error; #NULL!..#N/A are 1..7. 454ODF group: Verify against final spec (likely Large Group). 455 456A. Function registration 457 458- Native opcode: SC_OPCODE_ERROR_TYPE_ODF = 419 (compiler.hrc line 459 415), ocErrorTypeODF (opcode.hxx line 177), since 2026-11-18. The 460 legacy SC_OPCODE_ERROR_TYPE = 151 (line 182) stays ERRORTYPE. 461- Parser names: 462 - ERROR.TYPE: "ERROR.TYPE" in ODF, English and UI lists 463 (core_resource.src lines 147, 500, 1129) 464 - ERRORTYPE: "ORG.OPENOFFICE.ERRORTYPE" (ODF, line 146), "ERRORTYPE" 465 (English, line 499; UI, line 1125) 466- Evaluator implementation: ScErrorType( bool bODF ) at 467 interpr1.cxx:8703. Both opcodes are in CASE_OCERRFUNC 468 (interpr4.cxx:3863-3864), so an error argument reaches them. 469- Help entry: HID_FUNC_ERROR_TYPE_ODF (helpids.h:736, hidother.src:328), 470 topic func_errortype.xhp embedded in 04060109.xhp next to ERRORTYPE 471 (HID_FUNC_FEHLERTYP). H.4's topic had reused HID_FUNC_FEHLERTYP and 472 was embedded in the date functions page. 473- Excel mapping: BIFF4 ID 261 (xlformula.cxx lines 260-261): import to 474 ocErrorTypeODF, export from both. xlsx: ERROR.TYPE (formulabase.cxx 475 line 486). 476 477B. Actual implementation: ScErrorType() examines the stack type: 478 479- svRefList: gets error code from first cell in ref list; 480 errNoRef if empty, errNoValue if >1 ref. 481- svDoubleRef/svSingleRef: gets cell error code via pDok->GetErrCode(). 482- default: PopError(), pushes error number. 483 If no error (nErr == 0), pushes NA (PushNA()). 484 For ERROR.TYPE, lcl_ErrorTypeODF() (interpr1.cxx:8680) maps the code as 485 XclTools::GetXclErrorCode does: errNoCode 1; errDivisionByZero 2; 486 errNoValue, errIllegalArgument, errIllegalParameter, errPairExpected, 487 errOperatorExpected, errVariableExpected, errParameterExpected, 488 errCircularReference 3; errNoRef 4; errNoName, errNoAddin, errNoMacro 5; 489 errIllegalFPOperation 6; everything else 7. 490 ERROR.TYPE is in the multi-argument opcode range, so it checks for exactly 491 one argument itself (MustHaveParamCount); `=ERROR.TYPE()` is an error 492 (calcval row ERROR.TYPE.args, run). 493 H.4 had changed ScErrorType() itself, so ERRORTYPE returned 1-7 and 494 lost the internal codes its help documents (Err:518 gives 518); Jim's 495 decision: separate function, ERRORTYPE as on trunk. 496 497C. Format compatibility: xls ERROR.TYPE reads as ERROR.TYPE; ERRORTYPE 498 is written as ERROR.TYPE and comes back as ERROR.TYPE (run). xlsx 499 ERROR.TYPE read (run). 500 501D. Documentation: English topics for both functions. 502 503Failure type: None. ERROR.TYPE(SQRT(-1)) is 3 in AOO (Err:502) and 6 in 504 Excel. 505Priority: Resolved (was P1). 506Confidence: HIGH — run, Excel 16. 507 50811\. NUMBERVALUE 509--------------- 510 511ODF 1.4: Defined (§6.13.28 in ODF 1.2 and 1.4). 512ODF group: Verify against final spec (likely Large Group). 513 514A. Function registration 515 516- Native opcode: SC_OPCODE_NUMBERVALUE = 392 (compiler.hrc line 388). 517- Parser name: "NUMBERVALUE" in core_resource.src lines 356 (ODF) 518 and 709 (Excel). 519- Function-name-to-opcode mapping: ocNumberValue → NUMBERVALUE. 520- Evaluator implementation: ScInterpreter::ScNumberValue() at 521 main/sc/source/core/tool/interpr1.cxx:3102 (moved from interpr4.cxx 522 2026-11-18). Dispatch via `case ocNumberValue : ScNumberValue();` at 523 interpr4.cxx:3558. 524- Function parameter metadata: Function Wizard resource with 3 525 parameters, 2 and 3 optional (the H.1 block had one flag too few). 526- Help entry: HID_FUNC_NUMBERVALUE (hidother.src:378, helpids.h:784); 527 topic func_numbervalue.xhp. 528- Excel mapping: saFuncTable_Odf line 424: 529 EXC_FUNCENTRY_ODF( ocNumberValue, 1, 3, 0, "NUMBERVALUE" ) 530 (was 2, 2). 531 532B. Actual implementation: the ODF steps in order: group separators 533 removed only before the first decimal separator, that separator turned 534 into '.', whitespace removed, trailing percent signs stripped (each 535 divides by 100), and the rest must be a plain xsd:float without INF/NaN 536 (lcl_IsPlainFloat(), interpr1.cxx:3072). LEN(DecimalSeparator) must be 1 537 and it must not occur in GroupSeparator; omitted separators come from 538 the locale, and the locale group separator is dropped when it equals 539 the given decimal separator. The H.1 version removed group separators 540 everywhere and replaced every decimal separator. 541 542C. Format compatibility 543 544- XLS: `_xlfnodf.NUMBERVALUE`; AOO reads it back, Excel shows #NAME? (run). 545- XLSX import: `_xlfn.NUMBERVALUE` is read (run, 2026-11-18). 546 547D. Documentation: func_numbervalue.xhp (rules added 2026-11-18). 548 549Failure type: None. Differences from Excel 16 on purpose: empty text is 550 an error (Excel 0), an empty GroupSeparator is accepted (Excel error), 551 a two-character DecimalSeparator is an error (Excel uses the first). 552Priority: Resolved (was P1). 553Confidence: HIGH — run, Excel 16. 554 55512\. GETPIVOTDATA 556---------------- 557 558ODF 1.4: Not defined by ODF 1.4 Part 4. Excel-specific function. 559ODF group: Not applicable. 560 561A. Function registration 562 563- Native opcode: SC_OPCODE_GET_PIVOT_DATA = 390 (compiler.hrc line 386). 564- Parser name: "GETPIVOTDATA" in core_resource.src lines 354 (ODF) 565 and 707 (Excel). 566- Function-name-to-opcode mapping: ocGetPivotData → GETPIVOTDATA. 567- Evaluator implementation: ScGetPivotData() at interpr2.cxx:3069. 568 Takes 2–30 params (must be even count). Supports old syntax 569 (constraint string) and new syntax (field/item pairs). Uses 570 pDok->GetDPAtBlock() to find the DataPilot object. 571- Function parameter metadata: Via native function resource. 572- Help entry: HID_FUNC_GETPIVOTDATA (hidother.src:334, helpids.h:742). 573- ODF formula namespace mapping: GETPIVOTDATA in both sections. 574- Excel import mapping: saFuncTable_8 line 355 (BIFF8): 575 { ocGetPivotData, 358, 2, MX, V, { RR, RR, VR }, 0, 0 } 576- Excel export mapping: BIFF8 export (same table entry, no importonly/exportonly flag). 577 578B. Actual implementation: ScGetPivotData() parses target field name 579 and field/item filter pairs, locates the DataPilot table via 580 ScDPObject, calls GetPivotData(). Comment at interpr2.cxx:3035–3036 581 notes: "MS Excel docs claim to use the 'most recent' which is not 582 exactly the same as what we do in ScDocument::GetDPAtBlock." (not 583 re-read 2026-11-18) The 584 function depends entirely on the DataPilot subsystem 585 (dptablecache.cxx:517 mentions GETPIVOTDATA disabling nested 586 formula interpretation). 587 588C. Format compatibility 589 590- XLS import: BIFF8 (function ID 358), recognized and mapped. 591- XLSX import: oox/source/xls/formulabase.cxx line 692 has an 592 import-only GETPIVOTDATA row (xlsx import does not use 593 saFuncTable_Oox, which is xls-only). Not run. 594- XLS export: BIFF8 export supported. 595- XLSX export: AOO has no xlsx export. 596 597D. Documentation: Help entry exists. 598 599Failure type: Implementation present. DataPilot dependency makes it 600 application-level. 601Priority: P3. 602Confidence: HIGH — source evidence clear; runtime unverified. 603 60413\. FDIST 605--------- 606 607ODF 1.4: ODF FDIST (§6.18.22) is the left tail with optional Cumulative 608 (default TRUE; 0 for x < 0; density error only at x = 0, r1 = 1). 609 LEGACY.FDIST (§6.18.23) is the right tail, three arguments. 610ODF group: Not applicable. 611 612A. Function registration 613 614- AOO FDIST = ODF LEGACY.FDIST: SC_OPCODE_F_DIST = 315 (compiler.hrc 615 line 311), ocFDist, ScFDist() at interpr3.cxx:1588, three arguments, 616 unchanged from trunk. Names: "LEGACY.FDIST" (ODF, core_resource.src 617 line 287), "FDIST" (English line 640, UI line 1695). xls BIFF4 281. 618 Help HID_FUNC_FVERT. 619- AOO F.DIST = ODF FDIST, since 2026-11-18: SC_OPCODE_F_DIST_LEFT = 417 620 (compiler.hrc line 413), ocFDistLeft (opcode.hxx line 372), 621 ScFDistLeft() at interpr3.cxx:1604. Names: "FDIST" (ODF, line 288), 622 "F.DIST" (English line 641, UI line 1699). xls: saFuncTable_Oox 623 lines 385-386, `_xlfn.F.DIST`; `_xlfnodf.FDIST` imports as FDIST 624 (line 421). Help HID_FUNC_F_DIST_LEFT (helpids.h:689, 625 hidother.src:285), F.DIST section in 04060182.xhp. 626 627B. History: H.8/H.8.1 changed ocFDist itself into the left tail (with a 628 4th argument popped after the others) and added ocLegacyFDist. That 629 changed FDIST typed in every UI language, in macros and in .sxc files, 630 and f4f3de4f92 then dropped the SC_OPCODE_LEGACY_F_* defines, so the 631 branch did not build. Jim's decision: keep FDIST, add F.DIST. 632 ScFDistLeft() computes the left tail with GetBetaDist() at 633 r1*x/(r1*x+r2) and the density with GetBetaDistPDF(). 634 635C. Format compatibility 636 637- ODF: of:FDIST ↔ F.DIST, of:LEGACY.FDIST ↔ FDIST (run, round trip). 638- XLS: FDIST as 281 (run); F.DIST as `_xlfn.F.DIST`, Excel calculates the 4-argument form (run); `_xlfnodf.FDIST` from an older xls reads as FDIST (run). 639- XLSX: FDIST (281) read (run); `_xlfn.F.DIST` is read (run, 2026-11-18). 640 641D. Documentation: FDIST help as on trunk; F.DIST section added. 642 643Failure type: None. 644Priority: Resolved. 645Confidence: HIGH — run; F.DIST and FDIST match Excel 16 on 300 and 263 646 random cases. 647 64814\. FINV 649-------- 650 651ODF 1.4: ODF FINV (§6.18.24) inverts FDIST (left tail); LEGACY.FINV 652 (§6.18.25) inverts LEGACY.FDIST. 653ODF group: Not applicable. 654 655A. Function registration 656 657- AOO FINV = ODF LEGACY.FINV: SC_OPCODE_F_INV = 359 (compiler.hrc line 658 355), ScFInv() at interpr3.cxx:2052, unchanged. Names "LEGACY.FINV" 659 (ODF, line 332), "FINV" (English line 685, UI line 1879). xls BIFF4 660 282. 661- AOO F.INV = ODF FINV, since 2026-11-18: SC_OPCODE_F_INV_LEFT = 418 662 (compiler.hrc line 414), ocFInvLeft, ScFInvLeft() at 663 interpr3.cxx:2074, using ScFDistLeftFunction + lcl_IterateInverse. 664 Names "FINV" (ODF, line 333), "F.INV" (English line 686, UI line 665 1883). xls `_xlfnodf.FINV` (saFuncTable_Odf line 417). Help 666 HID_FUNC_F_INV_LEFT, F.INV section in 04060182.xhp. 667 668B. Actual implementation: F.INV accepts 0 <= p < 1 (0 gives 0). 669 670C. Format compatibility: as FDIST/F.DIST. 671 672D. Documentation: FINV help as on trunk; F.INV section added. 673 674Failure type: None. 675Priority: Resolved. 676Confidence: HIGH — run; FINV matches Excel 16 on 300 random cases, F.INV 677 on 298 (the other two within 3e-10 at p = 0.999, r2 = 1). 678 67915\. HARMEAN 680----------- 681 682ODF 1.4: Defined by ODF 1.4 Part 4 (statistical function). 683ODF group: Verify against final spec (likely Large Group). 684 685A. Function registration 686 687- Native opcode: SC_OPCODE_HAR_MEAN = 321 (compiler.hrc line 317). 688- Parser name: "HARMEAN" in core_resource.src lines 294 (ODF) 689 and 647 (Excel). 690- Function-name-to-opcode mapping: ocHarMean → HARMEAN. 691- Evaluator implementation: ScHarMean() at interpr3.cxx:2637. 692 Iterates over params, accumulates 1/x for x>0, counts values. 693 Sets errIllegalArgument for x<=0. Skips empty cells. 694- Function parameter metadata: Via native function resource. 695- Help entry: HID_FUNC_HARMITTEL (hidother.src:248, helpids.h:652). 696 The help ID name is German; the help text (04060182.xhp) is English. 697- ODF formula namespace mapping: HARMEAN in both sections. 698- Excel import/export mapping: BIFF4 ID 320 both ways 699 (xlformula.cxx line 313, saFuncTable_4); xlsx 320 700 (formulabase.cxx line 538). 701 702B. Actual implementation: ScHarMean() implements H = n / Σ(1/xᵢ) 703 counting only positive values. Zero or negative values trigger 704 errIllegalArgument. Text values and empty cells are skipped 705 (handled by the switch's default case / HasCellValueData check). 706 Error values propagate via nGlobalError. 707 708C. Format compatibility: BIFF 320 and xlsx (source; not run). 709 710D. Documentation: English help in 04060182.xhp. 711 712Failure type: None found. 713Priority: P3. 714Confidence: HIGH — source. 715 716Cross-Cutting Observation: LENB/RIGHTB/LEFTB/MIDB Opcodes 717---------------------------------------------------------- 718 719compiler.hrc defines SC_OPCODE_LENB=400, SC_OPCODE_RIGHTB=401, 720SC_OPCODE_LEFTB=402, SC_OPCODE_MIDB=403, and since H.2 721SC_OPCODE_FINDB=412, SC_OPCODE_SEARCHB=413, SC_OPCODE_REPLACEB=414. 722All seven map to Excel's BIFF IDs 205-211. 723