Bug 147398 - In Calc ver 7.3.0.3. =Sum covering a range of cells fails to adjust when additional values are added within the range
Summary: In Calc ver 7.3.0.3. =Sum covering a range of cells fails to adjust when addi...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.3.0.1 rc
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:7.4.0 target:7.3.1
Keywords: bibisectNotNeeded, regression
Depends on:
Blocks:
 
Reported: 2022-02-12 21:24 UTC by John Murphy
Modified: 2022-02-23 09:49 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
ODS of 2 sheets each displaying the same issue slightly differently. (21.60 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-02-12 21:45 UTC, John Murphy
Details

Note You need to log in before you can comment on or make changes to this bug.
Description John Murphy 2022-02-12 21:24:56 UTC
Description:
I ran an auto update to ver 7.3.0.3. yesterday and when I added a new entry to my existing spreadsheet running in Ubuntu 20.04 I have found that the simple =Sum(:) will not calculate beyond the last old entry before the update. I have ensured that the references to the cells include those in which I have put the new entries but the calc just ignores the new entries. if there were 20 entries before the update and I add a further 3 entries it still includes only 20, even though the range of fields include the new entries. I have checked that the cell format is numeric and it is.
Copying the offending sheet contents (mark all/copy/paste) to a new document still repeats the anomaly.

Description:

A calculated cell dependent on several other calculated cells does not update even what certain antecedent cells see a change in data and update correctly.
Copied versions of the sheet contents in other sheets seem to vary in which antecedent cell dependencies do not trigger update of the dependent cell.
Toggling auto-calculate has no effect.
Toggling (and restarting) multi-threading seems to have no effect.
Dependent cell does update after hard recalculate.
Only the built-in SUM and COUNTA functions are in use at any level.

Steps to Reproduce:
1.Create a multi column sheet with a row for Sum and an additional row for CountA.
2.Set the Sum to cover the data entered and some additional empty cells in the column.
3.Set the CountA as per step 2 above
4.In a separate cell set the formula =Sum(*:*)/Sum(*.*) the first Sum being the sum of the column Sum cells the second being the sum of the CountA cell values.  The objective being to give an overall avg.

Actual Results:
When established the O/A Avg is produced ok.  However when additional data is added into the data columns in cells covered by the column Sum and CountA the O/A Avg does not recognise the changed values displayed in the column Sum & CountA.

Expected Results:
The O/A Avg should adjust to reflect the new values in the column Sum and Count.


Reproducible: Always


User Profile Reset: No



Additional Info:
A copy of the calc has been provided on Ask LibrsOffice.org and @Joshua4 has had the same result on his system.
Comment 1 John Murphy 2022-02-12 21:45:40 UTC
Created attachment 178247 [details]
ODS of 2 sheets each displaying the same issue slightly differently.

Sheet 1 is a multi column data range with an =Sum and an =CountA at the top. In this sheet the column Sum and Count appear to work correctly when additional data is added to the range covered.  However when the totals of the Sum and Count of all the columns is used in a simple =Sum(*:*)/Sum(*:*) to give an overall average it works initially but will not readjust if additional data is addte to other cells covered in the range.

Sheet 2 Is a simple two column data range set up as per Sheet 1.  However I have noticed that when initially created, if data is added to the Left Hand column the overall average will not update, while data added to the RH column did update the O/A Avg.  However having saved and closed the file, upon reopening it now does the same as Sheet 1 above.
Comment 2 m_a_riosv 2022-02-13 00:36:29 UTC
I can't repro.
Version: 7.3.0.3 (x64) / LibreOffice Community
Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3
CPU threads: 4; OS: Windows 10.0 Build 21390; UI render: default; VCL: win
Locale: es-ES (es_ES); UI: en-US Calc: CL

Please test with a clean profile, Menu/Help/Restart in Safe Mode
Comment 3 Eike Rathke 2022-02-16 14:28:08 UTC
I can reproduce.

Already changing values in column Sheet2.E does not trigger recalculation of Sheet2.I7, or similar any value on Sheet1 of Sheet1.M7

Version: 7.3.2.0.0+ / LibreOffice Community
Build ID: a9f6d98859321e1b9029acc0c6e9347f4c1cf3b7
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded

and
Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 0bf0feb40ae5c394a14cdfe4f94fb98dd9053597
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded

but not in
Version: 7.2.5.2.0+ / LibreOffice Community
Build ID: 9c01d1c680939900bb2ff564a6c999fd8c8bb8c1
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded
Comment 4 Eike Rathke 2022-02-16 14:32:21 UTC
Odd enough, on Sheet2 that happened only on one load, but reproducible on Sheet1.
Comment 5 Commit Notification 2022-02-16 17:46:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a1f21d7094deb6f1ae5388718f2bc28eecd5737a

Resolves: tdf#147398 Test Intersects() instead of Contains(), tdf#119083

It will be available in 7.4.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.
Comment 6 Eike Rathke 2022-02-16 18:01:22 UTC
Pending review
https://gerrit.libreoffice.org/c/core/+/130012 for 7-3
https://gerrit.libreoffice.org/c/core/+/130013 for 7-3-1
Comment 7 Commit Notification 2022-02-17 10:48:37 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

https://git.libreoffice.org/core/commit/963330b5605e2350a34405c4752989c43c223b78

Resolves: tdf#147398 Test Intersects() instead of Contains(), tdf#119083

It will be available in 7.3.2.

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.
Comment 8 Commit Notification 2022-02-17 13:43:01 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/51648c8de8dc060abc8dff1af105acfccadab77d

tdf#147398: sc_ucalc_formula: Add unittest

It will be available in 7.4.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.
Comment 9 Commit Notification 2022-02-21 14:15:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3-1":

https://git.libreoffice.org/core/commit/eb07d94074cbc06cea4d938068161bbb25442d0a

Resolves: tdf#147398 Test Intersects() instead of Contains(), tdf#119083

It will be available in 7.3.1.

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.
Comment 10 Xisco Faulí 2022-02-23 08:58:50 UTC
*** Bug 147298 has been marked as a duplicate of this bug. ***