[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

*Subject*: **Re: [office-formula] CEILING/FLOOR**

*From*:**Eike Rathke <erack@sun.com>***To*: office-formula@lists.oasis-open.org*Date*: Tue, 30 Jan 2007 18:32:11 +0100

Hi David, On Saturday, 2007-01-27 17:23:16 -0500, David A. Wheeler wrote: > > > This is the reason why OOoCalc introduced a third optional > > > parameter that if given and not zero signals to do the same. > > > > > > The possibilities we have are: > > > 1) Use the usual mathematical definition with two parameters, have > > > a third optional parameter to mimic the Excel behavior. > Andreas: > > #1 (since it has already an implementing application) > > I think we need to provide both standard mathematical behavior, and > Excel behavior. Personally, I'd prefer that there be two different > function names, but the argument "there's already an implementation > that does this" is a pretty good argument. I'm of course fine with this. > So I'm okay with this, but I think we need to add information in the > "Semantics" description of each that the default behavior is standard > mathematical behavior and is DIFFERENT from some applications (Excel). > So a program that reads in an Excel formula with FLOOR/CEILING > function will need to translate it to the 3-parameter version. This > is a subtle thing that an implementation or user could easily get > wrong. Will do. However, in practice the problem is more the other way, exporting to Excel when the third parameter was not used or evaluated to zero. > Also - shouldn't FLOOR/CEILING accept one parameter? If we're going > to use the standard mathematical notation, then a single-parameter > function should round to the integer (towards - or - infinity > respectively). We could do. This would add implied semantics to default the second parameter to -1 if the first argument was negative, and to +1 if it was positive. > > ROUNDDOWN has > > > >| TODO: There are lots of disagreements about the rounding functions and > >| what they should do, esp. for negative numbers. Implementors should not > >| implement any particular answer until this resolved. > > Yes, that's really involving FLOOR/CEILING. But actually, MANY of the > rounding functions are just completely messed up. Could you give some examples, please? > semantics for Excel's rounding functions are truly hideous - I think > they were created by someone who didn't understand mathematics at all. I guess they were already screwed up in the first place by Lotus 1-2-3 or such ... it may also be that in the financial sector CEILING/FLOOR were intended as ROUNDUP/ROUNDDOWN. > Eike: Can you modify the FLOOR/CEILING (and other rounding functions) > as needed to fix it? You can eliminate the TODO in ROUNDDOWN, we're > having that discussion right now :-). Will do. Eike -- Automatic string conversions considered dangerous. They are the GOTO statements of spreadsheets. --Robert Weir on the OpenDocument formula subcommittee's list.

**References**:**CEILING/FLOOR***From:*Eike Rathke <erack@sun.com>

**Re: [office-formula] CEILING/FLOOR***From:*"Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>

**Re: [office-formula] CEILING/FLOOR***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]