Re: [office-formula] CEILING/FLOOR

[Eike Rathke <erack@sun.com>]

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.