Summary: | EDITING sigma icon (sum function) operating on selected range | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | Charles Cunningham <charles> |
Component: | Spreadsheet | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | NEW --- | QA Contact: | |
Severity: | blocker | ||
Priority: | medium | CC: | gerard.fargeot |
Version: | 4.0.2.2 release | ||
Hardware: | Other | ||
OS: | All | ||
Whiteboard: | |||
i915 platform: | i915 features: |
Description
Charles Cunningham
2013-11-07 09:59:52 UTC
I agree with you regarding that function. Let some QA guy set proper status/importance for your report. Hope that can be implemented.. 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 (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. 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...") 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. "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. |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.