Created attachment 112706 [details] test files Steps to reproduce: - open xls and xlsx file Actual results result of C2:F2 is Error:522 Expected results result of C2:F2 is 1,4142 1,7321 2,0000 2,2361 Printscreen from excel and LO in the test file.
Created attachment 112710 [details] test file with additions I don't know if this is a Calc-bug. The OpenFormula definition of SQRT says that the argument is a number and so does the Excel function description. In the attachments, the argument is not a number, but a range. Were the range B2:F2, it would have worked in Calc, but the range includes the cells where the results are stored. This is IMHO an incorrect function call. If I enter in cell B8: "=SQRT(B2:F3)<Ctrl-Shift-Enter>", I get expected correct results in row 8 and expected error messages in row 9 (I would even expect an error in B9). The results in B3:F3 are IMHO not right: the input is a 2-row array and the output should be a 2-row array as well.
Winfried, in your document the range references are in normal formula mode and as such are evaluated as position dependent automatic intersections instead of array references. If you put the same formula in A12 you'd get an error because there is no intersection between the formula cell position and the range referenced. Raal's documents contain array formulas, the Err:522 circular reference error we produce is certainly wrong, and I'm quite sure it worked some time ago.. This should not be related to the SQRT() function put probably any function that expects a scalar value parameter (e.g. ABS(), ...) when passed a range in array mode when imported from Excel documents exposes this behavior. I didn't check.
(In reply to Eike Rathke from comment #2) > > Raal's documents contain array formulas, the Err:522 circular reference > error we produce is certainly wrong, and I'm quite sure it worked some time > ago.. => regression, bibisectrequest
maybe i do not understand the problem, but when i enter 1,2,3,4,5 in cells C2:F2 and select cells C3:F3 and enter the formula: =SQRT(C2:F2) (CTRL+SHIFT_ENTER) i do get 5 SQRT's.... This happens in XLS (2013) and in Calc (4.4.1.2) The attachment, correctly, produces a circular reference (because a cell is updated with a formula that references to the cell itself)
So the issue is apparently that while the second row of this array formula, if one were present, would have circular references, the first row should work as it is now. The behaviour of this is the same back to OOo 3.3.0 -> + Version: Inherited from OOo -> - Keywords: regression
Regarding my earlier comment 2, that was wrong, this case never worked in Calc. So, to recap, the situation here is that the array formula overlaps a region of the range its argument references, hence the circular reference error. This is not particular to SQRT, it would happen with any function taking a scalar value. Short reproducer: * in A1 enter 2 * select A2:A3 * enter array formula =SQRT(A1:A2) When the SQRT function is interpreted at A2, an array of the contents of A1:A2 is created, and at the position of A2 it is detected that a formula is running at that position. When the second element of the array formula in A3 is interpreted, that error is processed. This is a very rare situation and Excel seems to handle this differently. Apparently it processes each array formula part individually and obtains the argument's value of the formula in A3 after it already calculated A2, i.e. first A2:=SQRT(A1) then A3:=SQRT(A2) Handling this could mean having to rewrite, likely entirely, how Calc processes array formulas, something not done easily and error prone and IMHO not worth the effort for this twisted corner case. In the test case document 1 it would actually work if the formula in B3:F3 referenced B2:F2 instead of B2:F3. Now for the case that multiple columns are also involved, like in * in A1 enter 2 * in B1 enter 3 * select A2:B3 * enter array formula =SQRT(A1:B2) internally the other cells of the array formula reference the top left cell that actually will hold the result matrix, so while generating the argument array interpreting A2, when obtaining the value for B2 that also sees A2 already running so the circular reference error is propagated. I'm not sure if that part of the bug can be fixed without too much effort, need to investigate further. Again, this happens only if the argument's range overlaps the array formula range.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ac00b3c256933e667f022a49854a05e40e3d0630 tdf#88737 handle temporary array formula matrix circular reference It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Solves only the non-overlapping data range part, i.e. first row in this case.
This is already covered in sc/qa/unit/data/functions/date_time/fods/eastersunday.fods