| Summary: | CALC Update references when sorting on an autofiltered range of cells evaluates incorrectly | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Colin <that.man.colin> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | erack, xiscofauli |
| Priority: | medium | ||
| Version: | 5.3 all versions | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:7.3.0 target:7.2.5 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | Simple .ods with embedded screen dump of option settings | ||
|
Description
Colin
2021-11-12 09:15:18 UTC
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 |