[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [office-formula] CEILING and FLOOR
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.