[Solved] Floor negative decimal fraction

Discuss the spreadsheet application

[Solved] Floor negative decimal fraction

Postby DynV » Sat Jan 20, 2018 7:57 am

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
Last edited by DynV on Mon Jan 22, 2018 5:26 pm, edited 1 time in total.
AOO414m5 on Windows 7 Home Premium SP1. Je suis francophone.
User avatar
Posts: 163
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Floor negative decimal fraction

Postby robleyd » Sat Jan 20, 2018 8:13 am

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?
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
Posts: 1849
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Floor negative decimal fraction

Postby Zizi64 » Sat Jan 20, 2018 9:49 am

Use negative number in the second parameter, and maybe you need use the third parameter of the function too.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Posts: 7004
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Floor negative decimal fraction

Postby Lupp » Sat Jan 20, 2018 1:08 pm

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,
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
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.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 1914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Floor negative decimal fraction

Postby DynV » Mon Jan 22, 2018 5:27 pm

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
AOO414m5 on Windows 7 Home Premium SP1. Je suis francophone.
User avatar
Posts: 163
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests