Created attachment 76458 [details]
Small spreadsheet with a single column where overlapping Sigma (SUM) totals are incorrect
Problem description: If a column of figures has been summed using the Sigma function and the resulting sum total cell is then included in another Sigma sum Function, the second sum total does not include the result in the first sum cell.
Steps to reproduce:
Operating System: Windows (other)
Version: 126.96.36.199 release
In your small spreadsheet, cursor in cell B18, if I select Sigma function, proposal is to add B13:B17 because it detects intermediate calculus. But selecting B11:B17 get the correct result.
Hi there, Jacques,
Sorry, but your analysis is incorrect.
The spreadsheet shows in cell B18 precisely what was included in the purported selection which is not what was selected by the cursor.
The range indicated by the text alongside the column + the empty cell B18 is what was actually selected and then Sigma'd but the process ignored the Sigma function result already in the first selected cell (B11) and artificially selected what is shown in the formula in cell B:18 - B12:B17.
If you now select cells B11:B19 in the spreadsheet you will expect everything in the range to be summed into the empty cell B19 but you will simply still get the result 62 in cell B19 - that is to say, it will now ignore both Sigma'd cells.
Not particularly logical when you're creating a spreadsheet for say a Balance Sheet function where you expect to be able to have a column including sub-totals (B11) and then continue to accumulate to a second sub-total(B18)and potentially further sub totals and then produce a full total much further down the column.
Thank you for precisions. My results:
For range B11:B17, Sigma function give 573 in B19.
For range B11:B18, Sigma function give 635 in B20.
These results on LO 188.8.131.52+, LO 184.108.40.206 and LO 220.127.116.11 on windows 7 Home Premium.
I will download LO 18.104.22.168 to verify on your version.
Hi Again. Jacques,
I'm running Vista (SP2) and all relevant updates. If we continue to achieve different results should I download one of the LO versions you have already verified in your W7 environment and see what happens?
I would only be in a position to attempt this strategy at weekends because my wife uses the current live LO environment for college work on a daily basis so I must leave it stable for her during the week.
The easiest way to do that is to download a LO 4.0.2.x daily build. This is a dev release. It install a second paralell release on your computer and don't touch your installed one.
link : http://dev-builds.libreoffice.org/daily/
But sometimes reinstall the actual version on a new profile may also change its behavior. I have no link for this.
Created attachment 76604 [details]
Hi there, Jacques,
Sorry, power failure right in the middle of sending the update so the covering message somehow got "lost in the post".
Vista Home Basic 32 (SP2) with seven user accounts and UAC
3 Kids with parental controls
2 Adults no controls no admin privileges
2 Admin accounts - one for software testing.
Downloaded LODevVersion 22.214.171.124+
(Build ID: d4b4a6a88bbab383cef4bf4076a84e19efa509a)to the test account so I'm assuming that the Dev version in a different user account with admin privileges is equivalent to a whole new environment.
Still exhibiting the same error type but I actually extended the test, as can be seen from the replacement spreadsheet which did "make the cut" on the last message.
I actually made a short .AVI video with CAMSTUDIO to demonstrate the point but it's too big to upload. If there's an upload location for bigger files (c57Mb)that I can use, then I can distribute it that way.
If I am going to get involved in further testing (again, subject to the spouse priority user caveat), I will probably sort out a personal online store somewhere but that's not going to happen fast enough for this issue!
Hi there again, Jacques,
Sorry if this is becoming verbose, I have now had the opportunity to perform a more systematic test. It is questionable as to whether it is a genuine bug or a simple but fairly significant inconsistency in the manner in which cells can be identified for the SUM and Sigma functions. There appear to be three methods.
Firstly, delete the contents of cells B11, B18 and B21 from the sample spreadsheet.
1. Cursor in B11 > Sigma -- result = selection marquee for SUM of B2:B10
(the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B2:B10 automatically placed in B11 which is the first available empty cell.
alternatively (clear B11)
2. Cursor in cell B1 > left mouse and hold, selecting range B2:B10(orB2:B11) > Sigma -- result = SUM of B2:B10 automatically placed in B11 which is the first available empty cell.
alternatively (clear B11)
3. Cursor in cell B11 > type =SUM( > manually select cells B2:B10 > CR to accept the formula and parameters -- result = SUM of B2:B10 automatically placed in B11 which is the defined target location
All reasonably logical
4. Cursor in B18 > Sigma -- result = selection marquee for SUM of B13:B17
(the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B13:B17 automatically placed in B18 which is the first available empty cell.
alternatively (clear B18)
5. As 4 above but re-position and re-size the selection marquee to include cells B11:B17 -- result = cumulative total of B11:B17 in cell B18.
alternatively (clear B18)
6. Cursor in cell B11 > left mouse and hold, selecting range B11:B17(orB11:B18) > Sigma -- result = SUM of B13:B17 automatically placed in B18 which is the first available empty cell but NOT The Required (SUM(B11:B17)) Calculation. The formula can be edited to provide the desired result.
7. Cursor in B21 > Sigma -- result = selection marquee for SUM of B18:B18
(the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B18:B18 automatically placed in B21 which is the target cell.
alternatively (clear B21 but leave existing formula in B11)
8.Cursor in cell B11 > left mouse and hold, selecting range B11:B21 > Sigma -- result = SUM of B19:B20 automatically placed in B21 which is the first available empty cell but, ironically, the SUM is 0 all "real data" in the range B13:B17 and the preexisting sub totals in B11 & B18 have been completely ignored. Definitely NOT The Required Calculation. The formula can be edited to provide the desired result.
It could be argued that it is illogical to include the real numbers and their sub total in a later total but that should flag an error condition rather than just ignoring all data elements and providing the false result of 0.
For the record
Cursor in B21 > type =sum( > select the range B10:B18 > CR -- result = mathematically correct but a totally illogical use of the data.
We cannot program user data abuse out of the product but we should not leave illogical operation of valid requests in it either
As demonstrated, the results of a manual selection of a range for Sigma are not only handled incorrect but also inconsistently, depending on just how many Sigma'd cells are included in the selection.
I have not attempted to test for a greater number of data blocks with sub totals but could undertake that fairly easily if it becomes a requirement.
It could be that further input from the original analyst may be necessary to ascertain just what they think the procedures should be, or, we can cheat a little and see what happens in Excel :).
Here, It's quite clear and reproductible. I set status to NEW.
Have a nice day,
I have tested this rather awful bug under Ubuntu 10.04 x86_64 and Crunchbang 11 x86_64 running the following versions of LO:
- v126.96.36.199 OOO330m19 (Build:6)
- v188.8.131.52 OOO340m1 (Build:602)
- v184.108.40.206 Build ID: 3215f89-f603614-ab984f2-7348103-1225a5b
- v220.127.116.11 Build ID: e183d5b
- v18.104.22.168 Build ID: 5464147a081647a250913f19c0715bca595af2f
- v22.214.171.124 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a
All exhibit the same behaviour, which is to say that the method of highlighting cells and clicking the AutoSum (Σ) toolbar button results in an =SUM() function range only including any immediately prior cells not containing another =SUM() function. For example:
1. Enter 1 in A1.
2. Enter 1 in A2.
3. Highlight A1:A3 and click Σ; =SUM(A1:A2) is entered in A3.
4. Enter 1 in A4.
5. Enter 1 in A5.
6. Highlight A1:A6 and click Σ; =SUM(A4:A5) is entered in A6.
Setting Version to v3.3.0 release, Platform to All/All.
>[...] this rather awful bug [...]
I would like to clarify my prior comment in the context of what is stated in comment #1, that the AutoSum (Σ) toolbar button "detects intermediate calculus". Evidently the AutoSum function is designed to automatically locate and select prior =SUM() totals i.e., selection / highlighting of the range to sum prior to clicking this button is not required. For example:
1. Enter 1 in A1.
2. Enter 1 in A2.
3. Place cursor on A3.
4. Click Σ; =SUM(A1:A2) is selected / entered in A3.
5. Enter 1 in A4.
6. Enter 1 in A5.
7. Place cursor on A6.
8. Click Σ; =SUM(A4:A5) is selected / entered in A6.
9. Place cursor on A7.
10. Click Σ; =SUM(A3;A6) is selected / entered in A7.
At the very least this would appear to offer a viable workaround.
I am seeing this same problem with LO Mac 126.96.36.199 and whatever is current in Manjaro Linux.
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.4 or later)
If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior
the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword
Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa
Thank you for your help!
-- The LibreOffice QA Team This NEW Message was generated on: 2015-12-20
Still there in 188.8.131.52 Win 10
This bug is still present in LibreOffice 184.108.40.206 (July 2016) on any OS.
With the same behavior reported in 2013.
still present in Version 220.127.116.11 (x64)
This is on purpose. An AutoSum stops at a previous SUM function and does not include it. This is because users enter values, click on AutoSum, enter other values, click on AutoSum again. This should not include the previous SUM. If you want a sum of sums then, after the last sum, click AutoSum and it will sum only the SUM cells.