Description: A cell outside an autofiltered array accumulating values on a line within the array will fail when the array is sorted and the numerical data moves to a new location. HOWEVER, the cell references appear to be updated but the result is incorrect. Simple .ods attached Steps to Reproduce: Ensure the CALC option "update reference when sorting range of cells" is enabled. With the attached sheet; Observe the formula in L10 and the result. I know it looks "odd". I was experimenting to assess what failures relate to null cells with the alternate sum() syntax variations. Sort Descending with any autofilter on Row 7 If you filter on G7 on the first attempt it will be necessary to refilter any other row A-Z to reinstate the correct assessment Focus L10 Observe the amended formula at L10 Observe the incorrect result of that formula With L10 still focused, activate the fx button adjacent to the "address box" Observe the correct identification and assessment of the formula Left click the default button [OK] Observe the value change at L10 Observe that L12 (a simple assignment of G10) is correctly evaluated throughout Reversing the change with the tool button or CTRL+Z will revert to the error Neither F9 nor SHFT+CTRL+F9 impact the result. Actual Results: Failure to correctly assess the formula Expected Results: Correct assessment of the formula Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 7.2.2.2 (x64) / LibreOffice Community Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56 CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 176203 [details] Simple .ods with embedded screen dump of option settings
Reproduced in Version: 7.3.0.0.alpha1+ / LibreOffice Community Build ID: a14b783bbe8eda32b4b79530d85ffc48b6ed0305 CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded and Version: 5.2.0.0.alpha1+ Build ID: 5b168b3fa568e48e795234dc5fa454bf24c9805e CPU Threads: 4; OS Version: Linux 5.10; UI Render: default; Locale: en-US (en_US.UTF-8)
@Eike, I thought you might be interested in this issue
Note that the expression =SUM(F10:G10:H10:I10:J10:K10) uses the ':' range operator repeatedly to extend the range, effectively resulting in the range F10:K10. Apparently that somehow makes a difference when the references are updated during sort that then forms for example =SUM(F21:G21:H21:I21:J21:K21) but the internal reference does not result in the effective range F21:K21. Re-entering the formula, by Function Wizard or editing the formula, of course produces the expected result again.
(In reply to Eike Rathke from comment #4) > Note that the expression =SUM(F10:G10:H10:I10:J10:K10) uses the ':' range > operator repeatedly to extend the range, effectively resulting in the range > F10:K10. Apparently that somehow makes a difference when the references are > updated during sort that then forms for example > =SUM(F21:G21:H21:I21:J21:K21) but the internal reference does not result in > the effective range F21:K21. Re-entering the formula, by Function Wizard or > editing the formula, of course produces the expected result again. I explained why the syntax might appear odd. I never rationalised it. The major problem I have encountered is that if a formula in a range to be sorted contains a "normally" defined range viz if(L10>5;SUM(G10:G40);"") then G10:G40 can end up anywhere in the column and the formula doesn't follow the new locations. In my use, they don't randomise but normally it's a Z-A sort so the cells end up in reverse order. If the formula is changed to read ;G10+G11+G12+G13................then it keeps perfect track of the new locations. However, if one of those accumulating cells contains null "" then the summation fails with the wrong data type. At least the array function SUM() seems to correctly filter the alpha characters to return a numerical result. I was trying to "engineer" a syntax that would correctly track the cells when the array is nested into a function.
(In reply to Colin from comment #5) > (In reply to Eike Rathke from comment #4) I have failed to properly identify that if the summation range is a column then the references work correctly (3700+ cells and counting) but if they are a row then it goes awry.
(In reply to Colin from comment #6) > (In reply to Colin from comment #5) > > (In reply to Eike Rathke from comment #4) > I have failed to properly identify that if the summation range is a column > then the references work correctly (3700+ cells and counting) but if they > are a row then it goes awry. =IF(K414="";"";ROUND(SUM(J413;J412;J411;J410;J409;J408;J407);1)) This correctly summs clusters of eight events regardless of whether they are sorted in ascending or descending order.
Bug 144967 also demonstrates anomalous behaviour when assessing Rows. Is there a potential relationship?
No.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/79589afe173ba8f17bfbbc6b38f0dfbc5fd9e0c9 Resolves: tdf#145640 MoveReference...Reorder() there are RPN tokens as well It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/124985 for 7-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/d1635040cc4f017855f0c7805487b5b1065a7cdd Resolves: tdf#145640 MoveReference...Reorder() there are RPN tokens as well It will be available in 7.2.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/499b6d679866a952e40e16ce16f997ecb5ec5853 tdf#145640: sc_uicalc: Add unittest It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
7.2.4 was a hotfix release, updating target in status-whiteboard