Page 1 of 1

[Solved] Floor negative decimal fraction

Posted: Sat Jan 20, 2018 7:57 am
by DynV
I'm trying to do a a floor function on -0.25 but with the formula =FLOOR(-0.25;1) I get Err:502. Am I misunderstanding the floor function (as in with other software too) or am I doing the formula wrong?

Thank you kindly

Re: Floor negative decimal fraction

Posted: Sat Jan 20, 2018 8:13 am
by robleyd
Wikipedia wrote:In mathematics and computer science, the floor function is the function that takes as input a real number x and gives as output the greatest integer that is less than or equal to x.
1 is not less than -0.25 - what are you expecting the output of your formula to be?

Re: Floor negative decimal fraction

Posted: Sat Jan 20, 2018 9:49 am
by Zizi64
Use negative number in the second parameter, and maybe you need use the third parameter of the function too.

Re: Floor negative decimal fraction

Posted: Sat Jan 20, 2018 1:08 pm
by Lupp
I would like to show you the specification of the FLOOR function as declared mandatory by "OpenDocument-v1.2-part2" ("OpenFormula") of OpenDocumentFormat OASIS documents:
OpenFormula (line wrapping as in the orginal) wrote: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.
Semantics: Rounds a number down to a multiple of the second number. If significance is omitted
or an empty parameter (two consecutive ;; semicolons) it is assumed to be -1 if N is negative and
+1 if N is non-negative, making the function act like the normal mathematical floor function if
mode is not given or zero. If mode is given and not equal to zero, the absolute value of N is
multiple of the absolute value of significance and then the sign
applied . Otherwise, it rounds toward negative infinity, and the result is the largest multiple of
significance that is less than or equal to N. If any of the two parameters N or significance is zero,
the result is zero.
Note: Many application user interfaces have a FLOOR function with only two parameters, and
somewhat different semantics than given here (e.g., they operate as if there was a non-zero mode
value). These FLOOR functions are inconsistent with the standard mathematical definition of
FLOOR.
See also CEILING 6.17.1, INT 6.17.2
If you feel sure to have understood this completely and only in this case you may "use" that function - and expect errors.
I do neither feel sure about this myself, nor about the question if developers having worked on the implementation had understood, nor about the question if the authors of the specification really knew what thy did. And I cannot definitely decide it by better means than feeling.

If I need formulas to achieve results of the kind, I compose them based on a few arithmetic operators and the functions INT(), SIGN(), ABS(), sometimes also MOD(), probably completed by IF(). Insofar I feel sure about the definitions - and about the implementations, too.

In short: You may consider the function FLOOR() to be a B level gag owing to some kind of compatibility phantasy.

Re: [Solved] Floor negative decimal fraction

Posted: Mon Jan 22, 2018 5:27 pm
by DynV
Using Lupp advice, I used INT() and the formula behaves as expected, ad a proper function FLOOR() would have been used instead.

Thank you again Lupp