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 — Semantic Test Plan 21================================================================= 22 23Branch: openformula 24Starting commit: 6a2f742073 25Audit type: Read-only source inspection (tests prepared, NOT executed) 26 27Updated 2026-11-18 to match the code after the post-merge review. The SG.* 28rows were replaced by 99 rows at the end of 29test/odf-validate/calcval-cases14.tsv, checked against Excel 16 where Excel 30has the function and run on macOS and Linux builds (all match). Expected 31values below that were wrong are corrected; where AOO and Excel differ on 32purpose, the row note says so. 33 34This document provides a test matrix for all 15 audited functions. 35Tests are designed but not run. Each test distinguishes: 36 37 1. Formula parser rejection 38 2. Unknown function 39 3. Wrong argument count 40 4. Evaluation failure 41 5. Incorrect numerical result 42 6. Incorrect error result 43 7. Import failure 44 8. Export failure 45 9. Missing platform service 46 47Expected results are derived from ODF 1.4 Part 4 (where applicable) 48or independently calculated mathematical examples. No test result is 49claimed as verified. 50 511\. FINDB 52-------- 53 54Defined by ODF 1.2 and 1.4 (§6.7.2: "The same as FIND, but using byte 55positions"). Byte model: DBCS (CJK characters count 2 bytes). 56 57Test cases (rows `FINDB.*` in `test/odf-validate/calcval-cases14.tsv`): 58 a) =FINDB("b";"abc") — basic byte position (2) 59 b) =FINDB("b";"abcb";3) — start position (4) 60 c) =FINDB("x";"abc") — not found (#VALUE!) 61 d) =FINDB("B";"abc") — case-sensitive (#VALUE!) 62 e) =FINDB("a";"abc";0) — start < 1 (#VALUE!) 63 f) =FINDB("a";"abc";4) — start past the text (#VALUE!) 64 g) =FINDB("国";"中国") — DBCS second char (3) 65 h) =FINDB("b";"a中b") — mixed ASCII/DBCS (4) 66 67Implemented: ScFindB() (interpr1.cxx) uses the AOO DBCS byte model 68 (DBCS=2 bytes, ASCII=1) via getLengthB, lcl_BytePosToCharIndex, 69 lcl_CharIndexToBytePos. Case-sensitive, no wildcards. The two text 70 arguments were popped in the wrong order until 2026-11-18. 71 Excel 16 counts a CJK character as 1 byte unless a DBCS language is set, 72 so g) and h) give 2 and 3 there. 73 74Purpose: Confirm FINDB byte-position semantics and DBCS handling. 75 762\. REPLACEB 77----------- 78 79Defined by ODF 1.2 and 1.4 (§6.7.6: "As REPLACE, but using byte 80positions"). 81 82Test cases (rows `REPLACEB.*` in `test/odf-validate/calcval-cases14.tsv`): 83 a) =REPLACEB("abc";2;1;"X") — basic replace (aXc) 84 b) =REPLACEB("abc";2;0;"X") — zero count inserts (aXbc) 85 c) =REPLACEB("abc";4;1;"X") — start after the end appends (abcX) 86 d) =REPLACEB("abc";2;10;"X") — count past the end (aX) 87 e) =REPLACEB("abc";0;1;"X") — start < 1 (error) 88 f) =REPLACEB("abc";2;-1;"X") — negative count (error) 89 g) =REPLACEB("中国";3;2;"日") — second DBCS character (中日) 90 91Implemented: ScReplaceB() (interpr1.cxx) uses the AOO DBCS byte model 92 via getLengthB, lcl_BytePosToCharIndex, lcl_CharIndexToBytePos. 93 Clamps position/count, handles DBCS boundaries. 94 95Purpose: Confirm REPLACEB byte-position replace and boundary handling. 96 973\. SEARCHB 98---------- 99 100Defined by ODF 1.2 and 1.4 (§6.7.8: "As SEARCH, but using byte 101positions"). 102 103Test cases (rows `SEARCHB.*` in `test/odf-validate/calcval-cases14.tsv`): 104 a) =SEARCHB("B";"abc") — case-insensitive (2) 105 b) =SEARCHB("b";"abcb";3) — start position (4) 106 c) =SEARCHB("x";"abc") — not found (#VALUE!) 107 d) =SEARCHB("国";"中国") — DBCS second char (3) 108 109Implemented: ScSearchB() (interpr1.cxx) uses the AOO DBCS byte model 110 via getLengthB, lcl_BytePosToCharIndex, lcl_CharIndexToBytePos. 111 Case-insensitive like SEARCH; regular expressions when the Calc option 112 for them is on. AOO 4 has no ? and * wildcards, so the earlier wildcard 113 cases (=SEARCHB("a?c";"abc") expected 1) were wrong. The two text 114 arguments were popped in the wrong order until 2026-11-18. 115 116Purpose: Confirm SEARCHB byte-position search. 117 1184\. DATEDIF 119---------- 120 121ODF 1.4 Part 4 §6.10.3. Excel-compatible date difference function. 122 123Test cases (rows `DATEDIF.*` in `test/odf-validate/calcval-cases14.tsv`): 124 a) =DATEDIF(DATE(2024;1;1);DATE(2024;12;31);"d") — days difference (365) 125 b) =DATEDIF(DATE(2024;1;15);DATE(2024;6;14);"m") — complete months (4) 126 c) =DATEDIF(DATE(2024;1;31);DATE(2024;2;29);"m") — no complete month (0) 127 d) =DATEDIF(DATE(2020;6;15);DATE(2024;6;14);"y") — complete years (3) 128 e) =DATEDIF(DATE(2024;2;29);DATE(2025;2;28);"y") — leap-day anniversary (0) 129 f) =DATEDIF(DATE(2024;1;15);DATE(2024;3;10);"md") — Excel borrow from the 130 month before the end month (24; the old expectation 26 was wrong) 131 g) =DATEDIF(DATE(2024;1;31);DATE(2024;3;1);"md") — Excel gives -1, so does AOO 132 h) =DATEDIF(DATE(1995;5;3);DATE(2009;4;28);"yd") — 361, as Excel 133 i) =DATEDIF(DATE(2024;12;31);DATE(2024;1;1);"d") — start after end (error, as Excel) 134 j) =DATEDIF(DATE(2024;1;1);DATE(2024;12;31);"D") — upper case accepted (365) 135 k) "x" and "" formats (error); time fractions ignored 136 l) xls: written and read (BIFF 351); xlsx: read 137 138Purpose: Validate DATEDIF semantics; see datedif-semantic-design.md. 139Run 2026-11-18 on macOS and Linux builds and against Excel 16 (630 cases, 140one difference, documented there). 141 1425\. ISOWEEKNUM 143------------- 144 145ODF 1.4: Defined by ODF 1.4 Part 4. ISO 8601 week numbering. 146 147Test cases (rows `ISOWEEKNUM.*` in `test/odf-validate/calcval-cases14.tsv`): 148 a) =ISOWEEKNUM(DATE(2024;12;29)) — Sunday, week 52 149 b) =ISOWEEKNUM(DATE(2024;12;30)) — ISO week 1 of 2025 (1) 150 c) =ISOWEEKNUM(DATE(2020;12;31)) — ISO week 53 151 d) =ISOWEEKNUM(DATE(2021;1;3)) — still week 53 of 2020 152 e) =ISOWEEKNUM(DATE(2026;1;1)) — Thursday, week 1 (an earlier row expected 53) 153 f) =ISOWEEKNUM() — wrong arg count (error) 154 g) =WEEKNUM(DATE(2024;12;30)) — WEEKNUM still needs two arguments (error) 155 156Fixed: Added separate opcode SC_OPCODE_ISOWEEKNUM (416) and 157 ScIsoWeekNum() (interpr2.cxx) implementing ISO 8601 via 158 Date::GetWeekOfYear(MONDAY, 4). Takes exactly 1 argument (date). 159 Does not affect existing WEEKNUM (ScGetWeekOfYear) which keeps 160 2-argument SUN/MON mode semantics. On ODF import, ISOWEEKNUM with one 161 argument becomes ocIsoWeekNum (FormulaCompiler::Factor); with two it 162 stays WEEKNUM (checked with an .ods holding both forms). 163 164Purpose: Validate ISO 8601 week numbering at year boundaries. 300 random 165 dates matched Excel 16. 166 1676\. DDE 168------ 169 170Application-level DDE function. Platform-dependent. 171 172Test cases: 173 a) =DDE("soffice";"table";"A1") — creates DDE link (platform-dependent) 174 b) =DDE("nonexistent";"topic";"item") — unavailable application (error expected) 175 c) =DDE("app";"topic") — 2 args (wrong arg count, needs 3) 176 d) DDE on Linux without DDE support — missing platform service 177 e) DDE in headless/server mode — DDE disabled in server mode 178 (appdde.cxx:494, 526) 179 180Purpose: Confirm DDE works where platform supports it, fails gracefully 181 otherwise. Not a pure formula test — requires DDE subsystem. 182 1837\. ODDFPRICE 184------------ 185 186Analysis AddIn function. Odd-period bond price. ODF 1.2/1.4 §6.12.31. 187 188Test cases (rows `ODDFPRICE.*` in `test/odf-validate/calcval-cases14.tsv`): 189 a) Excel's short odd first period example: 113.597717474079 190 b) long odd first periods, semiannual and annual 191 c) first coupon not on the maturity's coupon schedule (error, as Excel) 192 d) redemption 0 (error) 193 e) XLS/XLSX: no mapping (add-in function) 194 195Implemented 2026-11-18 in analysishelper.cxx GetOddfprice() (trunk threw 196 #87380#). Checked against Excel 16 on random cases with all bases and 197 frequencies, short and long periods, many month ends: see 198 function-implementation-roadmap.md, Group 3, for the 14 differences in 199 4524 ODDFPRICE/ODDFYIELD cases. 200 201Purpose: Confirm the add-in matches Excel. 202 2038\. ODDFYIELD 204------------ 205 206Analysis AddIn function. Odd-period bond yield. ODF 1.2/1.4 §6.12.32. 207 208Test cases (rows `ODDFYIELD.*`): 209 a) Excel's example: 0.0772455415978 (Excel 0.077245541597) 210 b) ODDFYIELD of an ODDFPRICE result gives the yield back (0.1492) 211 c) redemption 0 (error) 212 213Implemented 2026-11-18: bracketed Illinois iteration on GetOddfprice(). 214 In the 222 cases where the two yields differed, feeding both to Excel's 215 ODDFPRICE gave back the price within 1e-10 for AOO's yield and within 216 1.8e-7 for Excel's. 217 218Purpose: Confirm the add-in matches Excel. 219 2209\. RRI 221------ 222 223Rate of return per period: RRI(N; Pv; Fv) = (Fv/Pv)^(1/N) - 1. 224ODF 1.2/1.4 §6.12.44, constraint N > 0. 225 226Test cases: 227 a) =RRI(10;100;200) — 0.0717734625362931 (row RRI.basic; Excel the same) 228 b) =ZGZ(10;100;200) — old name still read (row RRI.zgz) 229 c) =RRI(0;100;200) — N = 0 (ODF: error; not run) 230 d) =RRI(-5;100;200) — N < 0 (ODF: error; not run) 231 e) =RRI(10;-100;200) — negative Pv (not run) 232 f) =RRI(10;100) — wrong arg count (error) 233 g) xls: `_xlfnodf.RRI` (AOO reads it back, Excel #NAME?); xlsx 234 `_xlfn.RRI` is read 235 236Expected: ScZGZ() (interpr2.cxx) computes pow(Fv/Pv, 1/N) - 1 237 with NO validation. c) to e) are not checked by the code. 238 239Purpose: Confirm validation gaps in RRI implementation. 240 24110\. ERROR.TYPE 242-------------- 243 244ODF 1.4: Returns error number (1=#NULL!, 2=#DIV/0!, ..., 7=#N/A). 245 246Test cases (rows `ERROR.TYPE.*` and `ERRORTYPE.*`): 247 a) =ERROR.TYPE(1/0) — 2 248 b) =ERROR.TYPE(NA()) — 7 249 c) =ERROR.TYPE(AM1), AM1 = NA() — 7; =ERROR.TYPE(AM2), AM2 = 1/0 — 2 250 d) =ERROR.TYPE(SQRT(-1)) — 3 (AOO gives Err:502, which the xls export 251 writes as #VALUE!; Excel gives 6) 252 e) =ERROR.TYPE(5), =ERROR.TYPE("t") — non-error → #N/A 253 f) =ERROR.TYPE() — wrong arg count (error) 254 g) =ERRORTYPE(1/0) — 532 and =ERRORTYPE(NA()) — 32767 (legacy, unchanged) 255 256 The old cases with error literals (=ERROR.TYPE(#REF!) etc.) do not parse 257 in AOO's English API grammar (Err:511/520), so they were dropped. 258 259Fixed 2026-11-18: ERROR.TYPE is its own opcode (ocErrorTypeODF, 419); 260 ScErrorType( true ) maps with lcl_ErrorTypeODF() like 261 XclTools::GetXclErrorCode (unlisted errors give 7). H.4 had changed 262 ERRORTYPE instead; that is undone. 263 264Purpose: Validate error-code mapping to ODF return values. 265 26611\. NUMBERVALUE 267--------------- 268 269ODF 1.4: Converts text to number using explicit separators. 270 271Test cases (rows `NUMBERVALUE.*`): 272 a) =NUMBERVALUE("1.234";".";",") — 1.234 273 b) =NUMBERVALUE("1.234,56";",";".") — 1234.56 274 c) =NUMBERVALUE("1 234.56";".";" ") — space grouping sep: 1234.56 275 d) =NUMBERVALUE("1.234,5";".";",") — group sep after the decimal sep (error) 276 e) =NUMBERVALUE(" 1 2 3 ";".";",") — whitespace removed: 123 277 f) =NUMBERVALUE("12%";".";",") — 0.12; "1.5%%" — 0.00015 278 g) =NUMBERVALUE(".5";".";",") — 0.5; "-1.5e3" — -1500; "1e" (error) 279 h) =NUMBERVALUE("1.2.3";".";",") — second decimal sep (error) 280 i) =NUMBERVALUE("";".";",") — error (Excel 0) 281 j) =NUMBERVALUE("1,5";",,";".") — DecimalSeparator longer than 1 (error; 282 Excel uses the first character) 283 k) =NUMBERVALUE("1,5";",";",.") — DecimalSeparator in GroupSeparator (error) 284 l) =NUMBERVALUE("1234.5";".";"") — no group separator (1234.5; Excel error) 285 m) =NUMBERVALUE("1,5";",") — locale group sep dropped when it equals the 286 decimal sep (1.5) 287 288Expected: ScNumberValue() (interpr1.cxx) implements the ODF steps (see 289 function-implementation-roadmap.md, Group 6). Before H.1 there was no 290 evaluator; the H.1 version removed group separators everywhere and 291 replaced every decimal separator. 292 293Purpose: Confirm the ODF transformation steps. 294 29512\. GETPIVOTDATA 296---------------- 297 298Extracts data from a DataPilot (pivot) table. 299 300Test cases: 301 a) =GETPIVOTDATA("Sales";$A$1:C$10;"Region";"West") — field/item pair 302 b) =GETPIVOTDATA("Sales";$A$1) — old syntax with constraint string 303 c) =GETPIVOTDATA("Sales";$A$1:C$10;"Region";"West";"Year";2024) — multiple filters 304 d) =GETPIVOTDATA("Nonexistent";$A$1:C$10) — missing field (error) 305 e) =GETPIVOTDATA("Sales";$A$1:C$10;"Region";"Nonexistent") — missing item (error) 306 f) =GETPIVOTDATA() — wrong arg count (error) 307 g) =GETPIVOTDATA("Sales";A1:C10;"Region";"West") — odd arg count (error) 308 h) XLS import (BIFF8) of GETPIVOTDATA — recognized (function ID 358) 309 i) XLSX import of GETPIVOTDATA — formulabase.cxx has an import-only row 310 (not run) 311 j) XLSX export — there is no xlsx export 312 k) Refresh pivot table then recalc — DataPilot dependency test 313 314Expected: ScGetPivotData() (interpr2.cxx) uses 315 pDok->GetDPAtBlock() to locate DataPilot object. 316 317Purpose: Confirm DataPilot dependency. 318 31913\. FDIST 320--------- 321 322ODF FDIST (§6.18.22) is the left tail with an optional Cumulative; AOO's 323FDIST is ODF LEGACY.FDIST (§6.18.23, right tail). Since 2026-11-18 the ODF 324function is F.DIST in AOO (ocFDistLeft, 417); FDIST is unchanged. 325 326Test cases (rows `F.DIST.*`, `FDIST.*`): 327 a) =F.DIST(2.5;5;10) — 0.8979977233557302 (Excel F.DIST the same) 328 b) =F.DIST(2.5;5;10;0) — density 0.0935947544496855 329 c) =F.DIST(-1;5;10) — 0 (ODF; Excel #NUM!) 330 d) =F.DIST(0;1;5;0) — pole (error); =F.DIST(0;2;5;0) — 1 331 e) =F.DIST(2.5;0;10) — r1 < 1 (error) 332 f) =F.DIST(2.5;5;10)+FDIST(2.5;5;10) — 1 333 g) =FDIST(2.5;5;10) — 0.10200227664427 (legacy right tail) 334 h) =FDIST(2.5;5;10;1) — legacy takes three arguments (error) 335 336Expected: ScFDistLeft() and ScFDist() (interpr3.cxx). xls: F.DIST as 337 `_xlfn.F.DIST` (Excel calculates 4 arguments), FDIST as BIFF 281, and 338 `_xlfnodf.FDIST` from an older xls reads as FDIST. 339 340Purpose: Confirm left and right tail. 341 34214\. FINV 343-------- 344 345ODF FINV (§6.18.24) is the inverse of the left tail; AOO's FINV is ODF 346LEGACY.FINV (§6.18.25). Since 2026-11-18 the ODF function is F.INV in AOO 347(ocFInvLeft, 418); FINV is unchanged. 348 349Test cases (rows `F.INV.*`, `FINV.*`): 350 a) =F.INV(0.5;5;10) — 0.931933160851048 351 b) =F.INV(0;5;10) — 0; =F.INV(1;5;10) — error 352 c) =F.INV(F.DIST(2.5;5;10);5;10) — 2.5 353 d) =FINV(0.1;5;10) — 2.52164068620962 (legacy) 354 355Expected: ScFInvLeft() uses ScFDistLeftFunction + lcl_IterateInverse; 356 ScFInv() unchanged. 357 358Purpose: Confirm the inverses. 359 36015\. HARMEAN 361----------- 362 363Harmonic mean: H = n / Σ(1/xᵢ), counting only positive values. 364 365Test cases: 366 a) =HARMEAN(1;2;4) — 3 / (1/1 + 1/2 + 1/4) = 3/1.75 ≈ 1.714 367 b) =HARMEAN(1;0;2) — zero value → errIllegalArgument 368 c) =HARMEAN(1;-2;4) — negative value → errIllegalArgument 369 d) =HARMEAN(5) — single value → 5 370 e) =HARMEAN() — wrong arg count (error) 371 f) =HARMEAN(A1:A5) where A1:A5 contains text — text cells skipped 372 g) =HARMEAN(A1:A5) where A1:A5 contains error — error propagates 373 374Expected: ScHarMean() (interpr3.cxx) counts only x > 0 values, 375 sets errIllegalArgument for x <= 0, skips empty/text cells. 376 377Purpose: Confirm implementation is correct. English help exists 378 (04060182.xhp; HARMITTEL is only the help ID name). Not run. 379 380Cross-Function Test: Byte-Oriented Text Family 381---------------------------------------------- 382 383FINDB, SEARCHB, REPLACEB (412–414) join LENB, RIGHTB, LEFTB, MIDB 384(400–403). All seven are written to xls as Excel's own functions (BIFF 385205–211). 386 387Purpose: Confirm the whole byte-oriented text family works. 388 389Test Execution Note 390------------------- 391 392Originally designed but not executed. Since 2026-11-18 the rows named 393above are run by test/odf-validate/calcval.py on macOS and Linux builds; 394cases marked "not run" are still hypotheses. DDE, GETPIVOTDATA and HARMEAN 395were not run. 396