Description: On pc Debian x86-64 with LO 7.4.2.3, I got "#Value!" when trying a formula like: =A1:A2+A5:A6+A9:A10 I must recognize I'm not sure if it's a bug or not. Perhaps it's the way it should be. Steps to Reproduce: 1. Retrieve attachment 2. See A13 Actual Results: #Value! Expected Results: Either 210 or if we absolutely want to mimick Excel: 90 120 Reproducible: Always User Profile Reset: No Additional Info: Version: 7.4.2.3 / LibreOffice Community Build ID: 40(Build:3) CPU threads: 12; OS: Linux 6.0; UI render: default; VCL: gtk3 Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Debian package version: 1:7.4.2-3 Calc: threaded I could also reproduce this with master sources updated today.
I think that it is not a bug. You must opt for =SUM(A1:A2;A5:A6;A9:A10) (or not usually with =SUM(A1:A2~A4:A5~A7:A8)) =A1:A2;A5:A6;A9:A10 will give Err:509 Operator missing. Just try in B1 with =A1:A2 and you will get only the A1 value. And with the same formula in B2 you will get only the A2 value. Tested with: Version: 7.3.6.2 / LibreOffice Community Build ID: 30(Build:2) CPU threads: 1; OS: Linux 5.14; UI render: default; VCL: gtk3 Locale: es-MX (en_US.UTF-8); UI: en-US Calc: threaded (In reply to Julien Nabet from comment #0) > 1. Retrieve attachment Attachment is missing.
Or introducing it as an array, you get two cells result. The first summing A1+A5+A9 and second one A2+A6+A10. For me not a bug.
=A1:A2 doesn't tell Calc what to do with this matrix, do Calc cannot know the user wants to sum the matrix. LeroyG is right, OP should tell Calc about it by using the SUM function. Not a bug.
Created attachment 183657 [details] testfile Sorry I had forgotten the attachment. BTW, sum in status bar at bottom right (don't know if it's the right naming) provides correct sum, 210, so Calc considers it as a plain sum in this case.
(In reply to Julien Nabet from comment #4) > Created attachment 183657 [details] > testfile > > Sorry I had forgotten the attachment. > > BTW, sum in status bar at bottom right (don't know if it's the right naming) > provides correct sum, 210, so Calc considers it as a plain sum in this case. Unless because it would be equivalent here as =SUM(A1:A2;A5:A6;A9:A10) which indeed works. So if this one isn't considered as a bug, at least it means we mustn't mimick Excel in all cases (because I consider its result, 90 120, not intuitive here) :-)
(In reply to LeroyG from comment #1) > ... > =A1:A2;A5:A6;A9:A10 will give Err:509 Operator missing. Indeed > Just try in B1 with =A1:A2 and you will get only the A1 value. And with the > same formula in B2 you will get only the A2 value. > Yes and if I do =A1:A2 in A18, I got "#VALUE!" quite weird!
(In reply to Julien Nabet from comment #6) > (In reply to LeroyG from comment #1) > > Just try in B1 with =A1:A2 and you will get only the A1 value. And with the > > same formula in B2 you will get only the A2 value. > > > Yes and if I do =A1:A2 in A18, I got "#VALUE!" quite weird! Not weird, because formula references only until A2. Change the formula to =A1:A10 and you will get "0". Otherwise use =SUM(A1:A2).
(In reply to LeroyG from comment #1) > I think that it is not a bug. > > You must opt for =SUM(A1:A2;A5:A6;A9:A10) (or not usually with > =SUM(A1:A2~A4:A5~A7:A8)) > > =A1:A2;A5:A6;A9:A10 will give Err:509 Operator missing. > > Just try in B1 with =A1:A2 and you will get only the A1 value. And with the > same formula in B2 you will get only the A2 value. I was trying to see where this interesting "formula that is a cell range will return the unambiguous value located at the corresponding coordinate" behaviour is documented, but couldn't find it: - https://help.libreoffice.org/latest/en-US/text/scalc/guide/relativ_absolut_ref.html - https://help.libreoffice.org/latest/en-US/text/scalc/guide/formulas.html - https://help.libreoffice.org/latest/en-US/text/scalc/guide/formula_enter.html Any idea where that is / where it should be? If it is an expected behaviour, we should turn this bug into a documentation issue.
Created attachment 183695 [details] test file for formula = single-row or single-column cell range (LO 7.5 alpha) To show different cases of formulas that simply reference cell ranges.
(In reply to Stéphane Guillou (stragu) from comment #8) > > Any idea where that is / where it should be? If it is an expected behaviour, > we should turn this bug into a documentation issue. No idea here but perhaps Eike will have some opinion here.
(In reply to Stéphane Guillou (stragu) from comment #9) > Created attachment 183695 [details] > test file for formula = single-row or single-column cell range (LO 7.5 alpha) > > To show different cases of formulas that simply reference cell ranges. If there are no values at all in A2:A7 and in A14:F14, likewise you will get "#Value!". Following what I understood, A10 and H14 are an attempt of self-referencing formulas, but off the valid range of the formula. Try with "=A2:A7" (formula in C3) in C8. You gets "#Value!".
Ok if everyone agrees it's expected, let's not waste more time on this and consider it as NOTABUG.
The behaviour is expected. If a function or operator expects a single scalar value but the argument is a cell range and the formula expression is not in array mode, an implicit intersection of the formula cell's position and the cell range argument is taken. If such is not possible because the position and the range are not intersect-able or the range is not a column vector or row vector with a single dimension in the intersection direction, an error is returned. For example the reference A1:A2 is intersect-able anywhere on row 1 or row 2, but not on other rows nor anywhere in column A (because then there is no single cell intersection). See also ODFF 6.3.3 Implied Intersection https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017984_715980110
Btw, the results of 90 and 120 are if the formula is in array mode, i.e. close the input with Shift+Ctrl+Enter instead of just Enter, same results as in Excel.