Re: [office-formula] CEILING and FLOOR

[Eike Rathke <erack@sun.com>]

Hi David,

On Thursday, 2007-03-22 15:56:36 -0500, David A. Wheeler wrote:

> We've kept putting off CEILING and FLOOR, and we must stop - we have
> to fish or cut bait.  Here's a proposal.

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

> 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?

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

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.