Re: [office-formula] CEILING and FLOOR

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

Eike Rathke:
> 
> Huh? I thought we settled that in the thread "CEILING/FLOOR" in January.

Oops! You're right, my apologies.  We _did_ discuss this and come to a decision.  Thanks for putting me straight.  (This points out the "dangers" of mailing lists: Other people can bring up links to your past messages :-) ).

Here's the thing - as I run through tests, I'm getting a little nervous about intentionally defining the 2-parameter CEILING/FLOOR as having such subtly DIFFERENT semantics than Excel and most other applications. Now I grant you, Excel's semantics are awful & nasty in this case, but I worry that having subtly different semantics will cause grief.  In our current approach, translating CEILING/FLOOR from Excel to OpenFormula requires the ADDITION of a parameter, and (where possible) the REMOVAL of a parameter the other way.

So although we DID discuss this, I'm having a last-minute reworry, thinking that we should have Excel semantics for the 2-parameter case (the ONLY case that Excel permits)... and I'd like to know if anyone else has a care either way.  Alternatively, we need to CLEARLY DESCRIBE this issue in a "Note", so that implementors can be made aware of this subtle difference.

> > Excel has a 2-parameter version of CEILING and FLOOR with mind-bogglingly weird semantics, but since people depend on them, I propose that the semantics of the 2-parameter version be the same as Excel's.
> 
> This would actually mean to turn
> 
> | If mode is given and not equal to zero, the amount of N is rounded away
> | from zero to a multiple of significance and then the sign applied. If
> | mode is omitted or zero, rounding is toward positive infinity.
> 
> into
> 
> "If mode is omitted or zero, the amount of N is rounded away from zero
> to a multiple of significance and then the sign applied. If mode is
> given and not equal to zero, rounding is toward positive infinity."
> 
> for CEILING, similary for FLOOR with an opposite rounding direction.
> 
> > The 3-parameter version was added, I believe by the Gnumeric folks.
> 
> It was introduced by OOoCalc, and since it already had an
> implementation we copied that semantics. See also
> Message-id: <E1HAvx7-0004bK-0Y@fenris.runbox.com> respectively
> http://lists.oasis-open.org/archives/office-formula/200701/msg00047.html
> 
> > Excel has no such thing. I'm not sure I understand the difference the "mode" parameter gives (I know what the OPTIONS are, but I'm not sure that what was documented is actually what is intended).
> 
> The mode parameter is documented now as intended by the OOo behavior. If
> present and not zero, the function behaves like in Excel.
> 
> > I think in the ONE-parameter case, we should use STANDARD MATH meaning.
> 
> That's what is specified. We should make it more obvious though. I'll
> add a sentence or two.
> 
> But now your proposal starts getting complicated:
> 1 parameter: standard math
> 2 parameters: do it like Excel
> 3 parameters: standard math again with multiples
> 
> Really?

Yeah, that is what I'm thinking.  Though when you put it that way, I now remember why we did what we did... so feel free to talk me out of it :-).  Now that we've had time to work other issues, I'd like to hear if others care to change it.

The big concern is that the current text doesn't really give any warning about this issue!

An alternative to changing the semantics is to modify the semantic text for FLOOR/CEILING.  The notes are really confusing, and don't get to the meat of the issue.  Namely, that if you're converting between Excel (etc.), you'll need to add/remove a parameter, because the definition given here are the RATIONALE semantics, not the EXCEL semantics.

If we keep this text, I think the sematics should say that "These semantics are consistent with standard mathematical notation, but are different than some legacy formats.  Some applications' 2-parameter FLOOR/CEILING have acted as if they had 3 parameters with nonzero 3rd parameter.  Such applications MAY present this as their user interface, but SHALL convert them to the format as shown when exchanged."  Or something like that.


> > IE: FLOOR(-1.1) is -2.  Note that FLOOR(-1.1) is NOT the same as FLOOR(-1.1; -1), which in Excel would return -1 (!), nor is it the same as FLOOR(-1.1;1) which would return Error.
> 
> We have that all, and IMHO covered it also in the test cases. I can add
> a few to make it more obvious.

Probably should add a few to make it obvious, if only because a single one might be interpreted as a "mistake".

--- David A. Wheeler