Bug 71339 - EDITING sigma icon (sum function) operating on selected range
Summary: EDITING sigma icon (sum function) operating on selected range
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium major
Assignee: Eike Rathke
URL:
Whiteboard: target:6.0.0 target:5.4.0.1 target:6....
Keywords:
: 93257 (view as bug list)
Depends on:
Blocks: Function-Sum
  Show dependency treegraph
 
Reported: 2013-11-07 09:59 UTC by Charles Cunningham
Modified: 2022-04-15 05:57 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Charles Cunningham 2013-11-07 09:59:52 UTC
The SUM function (this is the SIGMA icon next to the formula bar) does not work as expected.

In some instances: if the range selected has a blank cell in it, the selected range is truncated to include only cells after the blank cell.
The range has to be updated by editing the SUM() function within the formula bar to include cells that occurred before the blank cell.


Under all circumstances: if the range selected starts with blank cells in it, the selected range is truncated to include only cells after the blank cells.
The range has to be updated by editing the SUM() function within the formula bar to include the blank cells. 
The user reason for the blank cells,  is that values are entered into the blank cells at some later time and so should be reflected in the SUM.

This behaviour is a disaster from a business user perspective.

We simply can't explain to non technical office workers why LibreOffice should appear to be so capricious in this way.

We have found that this behaviour makes it impossible to convert Excel users to using Libreoffice. This is blocking adoption of Libreoffice in our customer organizations. 


Please correct this behaviour so that Libreoffice sheet behaves in a similar way to all other major spreadsheet programs such as:
Excel, Google Sheet, Zoho, Kingsoft etc.

Thanks for reading this - I sincerely hope that you choose to fix this as LibreOffice is such a great alternative to MS Office.
Comment 1 ign_christian 2013-11-08 09:28:56 UTC
I agree with you regarding that function. Let some QA guy set proper status/importance for your report. Hope that can be implemented..
Comment 2 Dominique Boutry 2013-11-21 10:49:57 UTC
Just to be sure that we are speaking about the same behaviour :
- in a new spreadsheet, I set A1 to 1, A2 to 2, A4 to 4 and A5 to 5
- and I select A6 and press the SIGMA icon next to the formula bar
- this result to a proposal of range A4:A5, resulting from the hypothesis that the A3 empty cell marks the end of the current table and the beginning of a new one, above; of course I'm supposed to correct the range if the hypothesis is wrong (LibO can't know...)
- the following major spreadsheet programs (Works ; Excel 97 ; AOO 4.0 ; OOo as far as I remember) work this way.

Can I humbly, without any "troll" tentative, ask you to write down the expected SIGMA behaviour, for you and your organisation ?

Regards
Comment 3 Ady 2013-11-21 11:55:10 UTC
(In reply to comment #2)
> Just to be sure that we are speaking about the same behaviour :
> - in a new spreadsheet, I set A1 to 1, A2 to 2, A4 to 4 and A5 to 5
> - and I select A6 and press the SIGMA icon next to the formula bar
> - this result to a proposal of range A4:A5, resulting from the hypothesis
> that the A3 empty cell marks the end of the current table and the beginning
> of a new one, above; of course I'm supposed to correct the range if the
> hypothesis is wrong (LibO can't know...)
> - the following major spreadsheet programs (Works ; Excel 97 ; AOO 4.0 ; OOo
> as far as I remember) work this way.
> 
> Can I humbly, without any "troll" tentative, ask you to write down the
> expected SIGMA behaviour, for you and your organisation ?
> 
> Regards

No, the behavior the reporter is talking about is for example:
A1: (empty)
A2: 2
A3: 3
A4: (empty)
A5: 5

Then select A1:A6 and press the SIGMA icon. The result is:
A6: =SUM(A2:A5)

but the reporter is expecting:
A6: =SUM(A1:A5)

So, if the initial selection is A6 *only*, then Calc goes up until the first empty cell when SIGMA is pressed. This is expected ( =SUM(A5:A5) ).

But if the initial selection is the *range* A1:A6, then the user is expecting for that selection to be respected, getting the SUM function in the latest lower cell with the whole selected range (except the latest lower cell) included in the SUM function.

For my example here:
_ Actual result (according to the reporter) in A6: =SUM(A2:A5)
_ Expected result (according to the reporter) in A6: =SUM(A1:A5)

I am not saying that the current behavior is necessarily wrong. I am only trying to give an example of what the reporter is saying.

(BTW, this report is currently set as BLOCKER. I am not changing this now, but I doubt this should be really consider as such).

Since the behavior is related to the SIGMA icon, while the SUM function itself is correct, I am editing the Summary accordingly.

Regards,
Ady.
Comment 4 Dominique Boutry 2013-11-21 17:24:14 UTC
OK

To summarize, the lay-out of prior selection infers the way SIGMA works (Recall that it intends to HELP user...) :
- not a rectangular area : LibO refuses (but I have a counter-exemple...), silently or not
- rectangle of R>1 rows by C>1 cols not wholly empty : LibO goes up to a empty line on first column and adjust top of area accordingly (WRONG) ; then LibO goes down to a line without any number and adjust bottom of area accordingly (once more, WRONG ; SIGMA must respect user's choice) ; finally, LibO searches a free line to house the results ; first try on the lower line of the selection : if empty, results are "pasted into" ; else, LibO goes down to a line with empty cells (possibly not connext to the selection, it positions the results in this line,
- wholly empty rectangle of R>1 rows by C>1 cols : if there are numbers in the line just above the rectangle, the sum is vertical : the topmost line of the rectangle is processed as if it was alone, then it is copied down to the other lines of the selection. Same for the other exis : if there are numbers in the column just left to the rectangle, the sum is horizontal: the leftmost column of the rectangle is processed as if it was alone, then it is copied right to the other columns of the selection.
- wholly empty line of 1 row by C>1 columns : the selected cells house the SUM result(s) ; see above for the selection of a vertical or an horizontal sum ; if horizontal, the leftmost cell houses the correct sum, it is copied right to the other cells, resulting in messy sums,
- line of 1 row by C>1 columns not wholly empty : the line contains the data to sum see above for the selection of the cell housing the result.
- idem for columns of R>1 lines by 1 column
- one cell, either non empty (its previous content is lost) or empty : always houses the result of the sum ; see above for the selection of a vertical or an horizontal sum.
- precision : anywhere an empty cell blocks the up/down propagation of test to determine borders, a cell containing a SUM formula has the same blocking effect.

I'm not sure to have been exhaustive... Altogether, I now support the position of Charles : its a *disaster*...

Before any code modification, one should provide a short and complete specification for SIGMA ("simple enough to be explained to one's grandma...")
Comment 5 GerardF 2013-11-21 18:34:07 UTC
The Sigma is called "*auto*sum".

The way to use it is the following :

Put the focus on the cell where you want to have the Sum, then Calc detect the range going up to the 1st empty cell (or to the left is cell above is empty).
If the proposed range is not that you want, select the correct range with mouse.

So it is Click on the Sigma, then select. Not select, then Click.
The AutoSum is not intended to sum the previous selection but to automatically detect the range in function of the position of the cursor.

The way you use it is a misuse.

Despite everything, I set it to NEW to have the opinion of a Calc dev.
Comment 6 Dominique Boutry 2013-11-22 07:09:49 UTC
"Click on the Sigma, then select [the range]" : I appreciate the simplicity of this assertion (understanding it is preceded by "Select the cell where you want the SUM, then").

In this case, there would be no tentative of misuse if the sum was unique and housed only in the (unique) active cell, instead of trying a possibly useless computing on all selected cells.

However, the feature consisting in filling in 1 clic the empty bottom line of a table with column SUMs (on a proposed range which is globally rectangular) is provided both in Excel, AOO and LibO, so we can't get rid of it as easily, even if it is impossible to subsequently adjust the ranges globally. So we really need a specification for AutoSUM.
Comment 7 QA Administrators 2015-04-19 03:21:51 UTC Comment hidden (obsolete)
Comment 8 Goran Rakic 2015-04-24 12:11:39 UTC
This is a duplicate of #62279, but I think this one is better documented.

There is a discussion on LibreOfficeForum.org about the issue: http://en.libreofficeforum.org/node/6918

I know at least one annoyed user that would very much like if the AutoSum can be modified to respect selected range. That would break consolidation usage (making sum of the sums) but it could still work for those users, just without selecting the range.
Comment 9 Anthony Youngman 2015-08-07 21:11:14 UTC
Just to point out that this behaviour was actually probably copied from Excel. Check it out in Excel 2003, but this is exactly what I expect when I'm working with Excel (and I agree, it is a nuisance ... damn programs thinking they know best! :-)
Comment 10 Anthony Youngman 2015-08-07 21:24:15 UTC
Just adding that I've filed a similar bug (93257) again asking for autosum to respect the user's selection. This is behaviour I expect from Excel, and I believe was in QuattroPro before that (that's QP for Win3.11, ie back in the DOS days).

And even used as described here, autosum doesn't work. Enter a column of figures, click in the cell below and click autosum. Works fine. Add more figures below (ie, in the user's mind, the previous sum was a *sub*total). Now autosum the column again. It creates a new total of just the numbers after the previous total!

Okay, that behaviour *could* be seen as logical. But if that behaviour is wanted, surely the user can just stick a blank line after the previous subtotal to indicate it? If I do that, I always want a new (sub)total of the *entire* column.

Basically, it seems to me that the design of autosum makes sense, it just doesn't work in the real world the way most people want to use it :-( (and breaks the "principle of least surprise" in that it produces very different results from what most people expect from other spreadsheets).

Cheers,
Wol
Comment 11 Anthony Youngman 2015-08-08 11:43:22 UTC
Just done even more testing ...

If the user selects a *horizontal* row, the autosum appears to respect the user's request, and sums the selected cells.

If the user does an autosum on a column consisting solely of sums, the autosum does NOT ignore cells containing sums, as claimed, but correctly sums the entire column.

Try the following - create a 4x4 grid of random numbers, select and sum the rows. Now sum the totals column *without* doing a select. See what I mean?

Confusing or what?
Comment 12 raal 2015-08-22 05:51:01 UTC
*** Bug 93257 has been marked as a duplicate of this bug. ***
Comment 13 Robinson Tryon (qubit) 2015-10-07 22:11:03 UTC
Dropping Severity -> critical (we've deprecated the 'blocker' value)
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Severity#Severity_Levels
Comment 14 n.b 2016-07-14 20:53:26 UTC
This bug is still present in LibreOffice 5.1.4.2 (July 2016) on any OS.
With the same behavior reported in 2013.
Comment 15 Eike Rathke 2017-06-16 12:12:20 UTC
For test cases shedding some light how it is to work see https://bz.apache.org/ooo/attachment.cgi?id=46097 (.ods)
For the test case specification/description see https://bz.apache.org/ooo/attachment.cgi?id=46096 (.html)

However, I agree that when selecting additional empty rows/columns on top/left of the actual data range these should be included in the final result and not stripped.
Comment 16 Commit Notification 2017-06-16 13:10:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9c0cd667da908ec1dde193c83d7d69a31b43e934

Resolves: tdf#71339 include selected top/left empty rows/columns in AutoSum

It will be available in 6.0.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.
Comment 17 Commit Notification 2017-06-16 14:27:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b3947785202ac263671113d6c9fd57d3ce6208d5

But not empty top/left in AutoSum of SUMs final selection, tdf#71339

It will be available in 6.0.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.
Comment 18 Commit Notification 2017-06-16 15:09:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f83d8ae84584c0967e2346566d21d65d6d7a432f

For AutoSum up to next sum mark/select the resulting range, tdf#71339

It will be available in 6.0.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.
Comment 19 Eike Rathke 2017-06-16 15:22:59 UTC
Pending https://gerrit.libreoffice.org/38886 for 5-4
Comment 20 Commit Notification 2017-06-16 19:45:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=53437b4db7e8f61155aea0a6e215f67c333bfaab&h=libreoffice-5-4

Resolves: tdf#71339 include selected top/left empty rows/columns in AutoSum

It will be available in 5.4.0.1.

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.
Comment 21 Commit Notification 2018-03-06 20:28:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9d8dcec608dde63c68b34450a95a3b168121e289

Resolves: tdf#116215 separate column sums and row sums, tdf#71339 related

It will be available in 6.1.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.
Comment 22 Xavier Van Wijmeersch 2018-03-07 16:57:31 UTC
tested and its working

Version: 6.1.0.0.alpha0+
Build ID: a790ee54319583897d82d4372243df870d4452a6
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 23 Commit Notification 2018-03-21 11:33:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2f504da556cb091db0ad7c47f1bb90220d890c66&h=libreoffice-6-0

Resolves: tdf#116215 separate column sums and row sums, tdf#71339 related

It will be available in 6.0.4.

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.
Comment 24 Commit Notification 2018-03-21 11:34:56 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4f7250d4bb2b86b1d50e27a72af9bb4d7b01c66a&h=libreoffice-5-4

Resolves: tdf#116215 separate column sums and row sums, tdf#71339 related

It will be available in 5.4.7.

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.
Comment 25 Commit Notification 2018-03-28 21:49:08 UTC
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=09d1353ece5d729d00c7952f640c3c7b94458509

uitests for bugs tdf#71339 tdf#116215 tdf#116421

It will be available in 6.1.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.
Comment 26 Commit Notification 2018-03-28 21:53:28 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5116f74a2fdf8f444cf60f88db042f4b87c25748&h=libreoffice-6-0-3

Resolves: tdf#116215 separate column sums and row sums, tdf#71339 related

It will be available in 6.0.3.

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.
Comment 27 raal 2019-12-07 19:22:13 UTC
The test exist, set status to Verified.