Problem description: FLOOR function return "error 502" if passing a negative number and a positive significance: this has no sense, since even -12 is a multiple of 2. Moreover, if one has data consisting in both positive and negative data, using FLOOR passing the same significance as argument for both positive and negative numbers increases the flexibility of the function, while keeping the meaning of the arguments as clear as possible. Furthermore, such a behavior is consistent with the behavior of Excel. It should be noted, that, if it's decided to keep the current behavior, "help" (https://help.libreoffice.org/Calc/Mathematical_Functions) files should be corrected to warn about the real meaning of the argument "significance". From the help: "Rounds a number down to the nearest multiple of Significance." Steps to reproduce: 1. Type "=FLOOR(-12.5,-1)" in a cell in Calc Current behavior: Cell displays "error 502". Expected behavior: Cell value should be -13 or -12 if mode=1 is passed as argument. The same behavior is consistent with Excel(R). Operating System: Windows 8 Version: 3.6.4.3 release
Created attachment 73486 [details] Sample file Hi Filippo, thanks for reporting. I can not reproduce the issue. See the attached file and the screenshot.
Created attachment 73487 [details] Screenshot
I've made an error writing the formula, it's: "=FLOOR(-12.5,1)" negative number and positive significance.
Note that according to the ODF 1.2 (OpenFormula) specification the current behaviour of =FLOOR(-12.5,1) is correct. "Multiple" in this context means natural-number-multiple. The real problem is that while the ODF specification allows the significance to be omitted, in which case it defaults to -1 if the first argument is negative and +1 if the first argument is positive, LibreOffice dos not implement this. That 1-argument version would then match the behaviour of the mathematical ceiling function.
Created attachment 84582 [details] Sample ODS file showing the bug Attached corrected sample file showing the bug. I note that in reading the ODF specification, nowhere does it say that "multiple" is restricted to positive integer multiples. Could someone explain how it could be interpreted in this way? I agree with the original submitter in that allowing both positive and negative numbers increases the flexibility without any loss, and also improves compatibility with Excel.
(In reply to comment #5) > I note that in reading the > ODF specification, nowhere does it say that "multiple" is restricted to > positive integer multiples. Could someone explain how it could be > interpreted in this way? OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 - Part 2: Recalculated Formula (OpenFormula) Format, part 6.17.3 FLOOR: > Summary: Round a number N down to the nearest multiple of the second parameter, significance. > Syntax: FLOOR( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) > Returns: Number > Constraints: Both N and significance shall be numeric and have the same sign. The last cited clause, Constraints, gives the answer. So, NOTABUG. Unfortunately.