Re: [office-formula] CEILING/FLOOR

["David A. Wheeler" <dwheeler@dwheeler.com>]

Eike said:
> > As can be found in the TODOs, CEILING and FLOOR can have these strange
> > Excel semantics that round the absolute amount if number and
> > significance are negative (away from zero for CEILING, toward zero for
> > FLOOR). 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.
> > 2) Use the usual mathematical definition with two parameters, ignoring
> >    Excel. Optionally define another function that behaves like in Excel.
> > 3) Swap #2 and do like Excel, optionally define another function with
> >    mathematical behavior.
> > 4) Only copy Excel behavior.
> > Opinions? I'm undecided between #1 and #2.

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.

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.

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).

Eike also noted:
> 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.  The semantics for Excel's rounding functions are truly hideous - I think they were created by someone who didn't understand mathematics at all.

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 :-).

--- David A. Wheeler