Created attachment 180066 [details] Calc test document The SUM button in the Calc button works in a confusing way if a range of cells is selected. Steps to reproduce: - Open the attached document. - Select cells A4-A6 (or A4-A7). - Select SUM from the dropdown to the left of Calc's formula lineedit in the toolbar. The button fills in =SUM(A5:A6) into A7. That's confusing, as A4 is selected as well. When nothing is selected and the cursor is in A7, then the buttons fills in the same, but it also highlights only A5-A6, suggesting that this is a feature. But if cells are selected, there's no such feedback.
Eike: I have found no documentation for this feature. The only thing I have found is https://bugs.documentfoundation.org/show_bug.cgi?id=71339#c15 , but that's unclear on what should happen in this specific case. Can you please clarify what Calc should do here?
The confusion is that the selected range remains selected while the final formula only get the cells below A4. In the sample file, A4: =SUM(A1:A3) You can select Ax:A6, being x 1 to 4, and will alwais get =SUM(A5:A6). Reproducible with: Version: 7.1.8.1 (x86) / LibreOffice Community Build ID: e1f30c802c3269a1d052614453f260e49458c82c CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win Locale: es-MX (es_MX); UI: en-US Calc: threaded Version: 7.3.2.2 (x86) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win Locale: es-MX (es_MX); UI: es-ES Calc: threaded
The button (maybe should be called "AutoFunction" instead of "Select Function", Excel still calls it "AutoSum" I think) inserts a function taking a data range up to and excluding the last formula cell within a selection, or if the previous cell is already a formula cell then collecting all formula cells of a contiguous cell range within the selection, acting similar to a SUBTOTAL() function on the overall range. Shortening the cell range passed in this case when a formula cell was encountered is correct behaviour. As Leroy said, the confusion is mainly because the original selection is kept, if it was adjusted to the resulting A5:A6 it would be clearer in this case.
User documentation for this is also bad and lacks details https://help.libreoffice.org/7.3/en-GB/text/scalc/02/06030000.html?DbPAR=CALC
Olivier: Can somebody please improve the documentation?
FYI: In MS Excel the behavior is a bit different than in LO Calc. In Excel if no range is selected and the cell cursor is positioned in A7, applying "AutoSum" will only select cells A5:A6 and leave the formula open for the user to change it. This is exactly the same LO Calc does. The difference is when there's a range selected. For instance, in Excel if we select A4:A6 and click AutoSum, it will consider cell A4 in the resulting SUM formula. LO Calc won't consider cell A4 since it has a formula in it. IMO the way MS Excel behaves is more natural from a user's point of view, since the user explicitly selected a range of cells and clicked the SUM formula in the AutoSum, so this means that he/she wants that cell to be considered. There's one side note: The behavior I reported for Excel won't work if cell A1 is selected. For example, if you select A1:A6 and apply AutoSum, only cells A5:A6 will be included in the SUM formula. However if you select A2:A6 then the SUM formula will include A2:A6. This looks like an Excel bug to me.
Maybe a duplicate of bug 144170 ?
(In reply to Rafael Lima from comment #7) > Maybe a duplicate of bug 144170 ? May be but I wouldn't consider it. That bug wants a different suggestion of sum range (which I closed as NAB) and this one points to unexpected behavior that need to be resolved.
Bug 124643 says: Inherited From OOo.