Description: In offset func: Height is a positive integer value, or a reference to a cell containing such a value, that specifies the total number of rows required in the modified reference. If omitted, a default value of 1 is applied. Width is a positive integer value, or a reference to a cell containing such a value, that specifies the total number of columns required in the modified reference. If omitted, a default value of 1 is applied. in excel width and height can be negative. integrating the possibility of using negative values for height and width would allow calculations to be made using cells above the reference value (example: sum). Actual Results: now is not possibile use negative value Expected Results: now is not possibile use negative value Reproducible: Always User Profile Reset: No Additional Info: My bug is a request for a new feature.
Makes no sense to me. If negative width or height shifts the offset again, then why not give the offset correctly instead? Note also that the ODF OpenFormula (ODFF) standard specification explicitly states positive integer constraints, see https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#OFFSET so it may just be an Excel quirk. Can you attach a sample document that in Excel produces a meaningful result using negative with or height that could not be accomplished using adjusted RowOffset or ColumnOffset values?
Created attachment 190458 [details] Test using negative value
So what's the difference to B1: =SUM(OFFSET(A1;$E$1+1;0;-$E$1;1)) B2: =SUM(OFFSET(A2;$E$1+1;0;-$E$1;1)) ... B8: =SUM(OFFSET(A8;$E$1+1;0;-$E$1;1)) B9: =SUM(OFFSET(A9;$E$1+1;0;-$E$1;1)) ... ? Which IMHO makes it even clearer that an actual range is shifted. (except that Excel apparently displays #REF! error in B1:B6 instead of Err:502). In real one probably would enter 7 in E1 and instead use B1: =SUM(OFFSET(A1;-($E$1-1);0;$E$1;1)) ...
The issue was brought up before on Ask.LO, where Eike already commented on it[1]. He explained there that MS Excel allows negative values even though both the MS documentation[2] and the OOXML specification[1] say it is not allowed. Closing as "not our bug" (see even MS Office users reporting it[4]), but it would be good to have it mentioned in our documentation.[5] [1]: https://ask.libreoffice.org/t/calc-offset-function-works-may-give-different-result-than-excel-equivalent/24859/5?u=stragu [2]: https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 [3]: ECMA-376-1:2016 section 18.17.7.245, see https://ecma-international.org/publications-and-standards/standards/ecma-376/ [4]: https://answers.microsoft.com/en-us/msoffice/forum/all/offset-bug-negative-height-argument/dc24aca4-b77c-e011-9b4b-68b599b31bf5 [5]: https://wiki.documentfoundation.org/Documentation/Calc_Functions/OFFSET
The issue was brought up before on Ask.LO, where Eike already commented on it[1]. He explained there that MS Excel allows negative values even though both the MS documentation[2] and the OOXML specification[3] say it is not allowed. Closing as "not our bug" (see even MS Office users reporting it[4]), but it would be good to have it mentioned in our documentation.[5] [1]: https://ask.libreoffice.org/t/calc-offset-function-works-may-give-different-result-than-excel-equivalent/24859/5?u=stragu [2]: https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 [3]: ECMA-376-1:2016 section 18.17.7.245, see https://ecma-international.org/publications-and-standards/standards/ecma-376/ [4]: https://answers.microsoft.com/en-us/msoffice/forum/all/offset-bug-negative-height-argument/dc24aca4-b77c-e011-9b4b-68b599b31bf5 [5]: https://wiki.documentfoundation.org/Documentation/Calc_Functions/OFFSET