[Solved] Floor negative decimal fraction

Discuss the spreadsheet application
Post Reply
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

[Solved] Floor negative decimal fraction

Post 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
Last edited by DynV on Mon Jan 22, 2018 5:26 pm, edited 1 time in total.
Je suis francophone.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Floor negative decimal fraction

Post 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?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Floor negative decimal fraction

Post by Zizi64 »

Use negative number in the second parameter, and maybe you need use the third parameter of the function too.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Floor negative decimal fraction

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: [Solved] Floor negative decimal fraction

Post 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
Je suis francophone.
Post Reply