Steps: 1. Put 1 to A1 2. Put `=INDIRECT("A1")` to A2, `=INDIRECT("A1";)` to A3, `=INDIRECT("R1C1")` to A4, `=INDIRECT("R1C1";)` to A5. Expected: A2 and A3 should show `1`; A4 and A5 should show #REF! (because in all formulas, the second parameter is not given, so all formulas should interpret their strings as using A1 syntax). Actual: A2 and A5 are `1`, A3 and A4 are #REF!, so when there is an argument separator present with absent optional second argument, it is treated as 0. Tested with Version: 6.1.0.2 (x64) Build ID: b3972dcf1284967612d5ee04fea9d15bcf0cc106 CPU threads: 12; OS: Windows 10.0; UI render: GL; Locale: ru-RU (ru_RU); Calc: CL See INDIRECT documentation: https://helponline.libreoffice.org/latest/en-US/text/scalc/01/04060109.html#bm_id3153181 http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018426_715980110 *But* MS Excel (at least 2016) does the same. So possibly this is intentional to be compatible (bug-to-bug?).
Hi Mike, Reproduced with LO 6.0.5.2 Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16 Threads CPU : 2; OS : Windows 6.1; UI Render : par défaut; Locale : fr-FR (fr_FR); Calc: CL and LO 3.5.3.2 Version ID : 235ab8a-3802056-4a8fed3-2d66ea8-e241b80 So probably inherited from OOo.
Created attachment 144020 [details] sample file
The content of attachment 144020 [details] has been deleted for the following reason: incorrect file
Created attachment 144021 [details] sample file
Also reproduced in Version: 4.3.0.0.alpha1+ Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a) In LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4 A3 to A5 give Error...
How LibreOffice handles missing parameter(but how should Libreoffice handle is another question): https://opengrok.libreoffice.org/xref/core/sc/inc/compiler.hxx?r=346ba4be#174 https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/compiler.cxx?r=85f141b5#4546 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1493 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1506 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1521 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1602 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1631 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1661 https://opengrok.libreoffice.org/xref/core/include/formula/token.hxx?r=346ba4be#394 https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr4.cxx?r=a0a8c958#2129 Evaluation stack is, as its name indicates, "stack" and LIFO https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=346ba4be#7901 =INDIRECT("R1C1";) => two arguments(svString and svMissing) and the second parameter is treated as 0 =INDIRECT("R1C1") => only one argument, as the number of parameters is not 2, line 7904 will not be reached in the same way =POWER(,5) is POWER(0,5) =POWER(5,) is POWER(5,0) but =EXP() is not =EXP(0),so Err:511 will be produced.
(In reply to Mike Kaganski from comment #0) > `=INDIRECT("A1";)` to A3, > `=INDIRECT("R1C1";)` to A5. > > Expected: A2 and A3 should show `1`; A4 and A5 should show #REF! (because in > all formulas, the second parameter is not given, This is not true, the second parameter is given but with an empty argument (resulting in an ocMissing opcode). > so all formulas should > interpret their strings as using A1 syntax). No, because an ocMissing evaluates to 0 (in most cases, sometimes different). > Actual: A2 and A5 are `1`, A3 and A4 are #REF!, so when there is an argument > separator present with absent optional second argument, it is treated as 0. Which is correct. > *But* MS Excel (at least 2016) does the same. So possibly this is > intentional to be compatible (bug-to-bug?). Exactly, but it's not a bug, it's a more or less general Excel behaviour with empty arguments.
note: https://gerrit.libreoffice.org/#/c/59823/