Hi everyone, Seeing this big mess, it is my recommendation to follow good mathematical practice.
I would rather vote to implement a new function: CEIL.EXCEL(...) instead of modifying the import filters and the existing functions. Excel import filter should then map the function during import to CEIL.EXCEL. This does not cover the case of export to Excel. However, if we transform this function into something else, is it guaranteed that we always will get the desired result in Excel? Even in future versions of Excel? And, is it also guaranteed that we get the desired results when reopening the Excel file back into Calc? I have concerns that reimport is broken, considering also the various versions of OOo likely to coexist in the future and the breadth of odf documents around, and the possible future versions of Excel, and ... . Therefore, my suggestion: - I would not change CEIL/FLOOR just to have it write the Excel file - if there is an easy formula that evaluates to the correct result in Excel, and does not cause trouble when imported back in Calc, then please use such a formula - if NOT, then generate something close to the original formula, that generates an error; because the original formula is visible, the person might adapt it accordingly, respectively knows what is desired, though this is done explicitly e.g. write = OOo.CEIL(...) (or maybe there are other means to do it) (reimport should convert it back to CEIL()) Sincerely, Leonard -------- Original-Nachricht -------- > Datum: Fri, 14 Aug 2009 21:25:22 +0200 > Von: Regina Henschel <[email protected]> > An: [email protected] > Betreff: Re: [sc-dev] Missing second parameter in CEILING > Hi Eike, hi Daniel, > > Eike Rathke schrieb: > > Hi Daniel, Regina, > > > > On Wednesday, 2009-08-12 10:15:43 +0200, Daniel Rentz wrote: > > > >> Regina Henschel schrieb: > >>> == Import == > >>> > >>> Importing an Excel-file with =CEILING(-3.4;) for example gives the > >>> cellcontent =CEILING(-3.4;;1), because in the current import filter > the > >>> third parameter 1 is added to get Excels "rounding away from zero" > >>> behavior. This will be a valid formula in ODF1.2 and calculated as > >>> CEILING(-3.4;-1;1)= -4. But the formula =CEILING(-3.4;) gives the > >>> result 0 in Excel. Therefore I think, that it would be better, to > >>> import the formula as =CEILING(-3.4;0;1) which gives 0 in OOo too > >>> according to spec ODF1.2. > > > > So, actually CEILING(x,) in Excel always returns 0 for any x. > > Yes, at least in my Excel2007. > > > > >>> The other problematic import case is Excels formula =CEILING(;value). > >>> In this case it becomes =CEILING(;value;1), which is an invalid > formula > >>> in ODF1.2. Here too the result in Excel is 0, and an import as > >>> =CEILING(0;value;1) would give the same result as in Excel. > > > > Why Excel even allows such crap is beyond my scope.. > > I guess, it is a bug in Excel. > > [..] > > > > I first thought I could withdraw the new proposal I made for ODFF, > > Please do not withdraw it. If CEILING will be defined with three > parameters, it is meaningful to allow CEILING(x;;y). It makes it easy to > drag-copy in the case the user wants a rounding to integer. Otherwise he > needs something like CEILING(x;if(x>=0;1;-1);y), because the first and > second parameter must have the same sign. Or the spec is changed in a > way, that the second parameter is always not negative and the sign is > set automatically according to the first parameter. > > > > but there may be documents in the wild that have the missing parameter > > written. These currently calculate 0 for any CEILING(x;;y), but will > > have to calculate according to the spec then, which is to use -1 or +1 > > for the missing parameter. It was an error in OOo to allow the missing > > parameter and interpret it as 0 in first place, just to be Excel > > compatible.. > > Yes, I too think it as a fault in OOo and I see additional trouble with > handling of ods-documents saved with OOo3.0 to OOo3.2. > > Is there a way to distinguish, whether a document was saved in a version > with the 'wrong' handling? I mean for example, if the method with > missing = +-1 would be used in OOo3.3, can a OOo3.3 detect, that a > document was saved in OOo3.2, which uses the old method with missing = 0? > > > > >>> == Export == > >>> > >>> When the formula in OOo is =CEILING(3.4;;Mode) it is exported to > >>> =CEILING(3.4;) which results in 0 in Excel. But in OOo the formula > >>> results in 4. > > > > Currently it does not. It should.. but it does this Excel compatibility > > stuff. > > Yes. I described the problems we get, when we implement CEILING > according to spec. > > > > >>> If the formula is exported as =CEILING(3.4;1) it would > >>> give the correct result in EXCEL. > >>> But for =CEILING(-3.4;;0) in OOo, which results in -3, > > > > Also here, it should, but currently doesn't.. > > > >>> the current > >>> export is =CEILING(-3.4;) which result in 0 in Excel which is wrong, > >>> but the export to =CEILING(-3.4;1) gives an invalid formula and > >>> =CEILING(-3.4;-1) would result in -4 in Excel, which is wrong too. > > > > Basically we can't export any function with Mode=0 (or omitted) to Excel > > where FLOOR(-x) or CEILING(-x) are rounded mathematically, because Excel > > has them wrong. IMHO, the best would be to write an NA() as second > > parameter in these cases, so the result in Excel will be an error > > instead of a different value. > > Concerning import/export with Excel, another -radical, but clean- > solution might be to implement CEILING_ADD, and export the regular > CEILING function to an error value in all cases. It would support an > Excel -> OOo -> Excel work flow, especially if the user sets xls as > default format. > > For OOo -> Excel the user would be responsible to use Excel > compatible functions. The formulas are better readable than using an if > construct (I read your description below already). Especially, when a > user sets xls as default format, he might wonder, that his expression > changed to an if-construct, when he reopens his document. > > But unfortunately this does not solve the change of meaning in > ods-documents. > > > > >>> When the formula in OOo is =CEILING(3.4) it is calculated as > >>> CEILING(3.4;1;0)=4. An export as =CEILING(3.4;1) would give correct > >>> values in Excel. > >>> But =CEILING(-3.4) is calculated as CEILING(-3.4;-1;0)=-3 and > >>> CEILING(-3.4;1) is invalid again and CEILING(-3.4;-1) result in -4 in > >>> Excel, which is wrong. > >>> > >>> I see no way to make a general export, that gives the same value in > >>> Excel as in OOo by simply setting or dropping parameters. So what to > do > >>> in export? > >> Hmm, I see. So the second parameter has to be set according to the > >> current value of the first. No idea how to fix this easily... > > > > No easy fix. We might track the calculation in the interpreter and store > > the replacement parameter at the ocCeil/ocFloor tokens if needed, but > > that still wouldn't resolve the CEILING(-x) and FLOOR(-x) cases other > > than writing NA(). We may better convert our CEILING/FLOOR to some > > IF(...) construct similar to what Gnumeric does when importing an .ods > > file. > > Export of CEILING(x) would result in IF(x>=0;CEILING(x,1);-FLOOR(-x;1)). > And it will become more confusing, when three parameters are used. It > would be a very complex export filter. > > > > > Btw, Gnumeric for CEILING(-3.4) calculates -4 as if CEILING(-3.4,-1) was > > given, when writing to Excel it stores CEILING(-3.4,) and the result in > > Excel is 0. > > The result according to spec would be -3. It seems, Gnumeric needs some > rework too. > > > > > What a mess. And all this only because some weirdo came up with the idea > > that CEILING/FLOOR should do someting different than the mathematical > > ceiling/floor functions.. > > Has it been discussed in ODFF committee to use a third parameter instead > of defining two functions? Or defining one function as standard and > leave the Excel-way function in the 'private' microsoft namespace? > > My current view: > (1) It seems, that a solution for Excel is possible, although not very > nice. > > (2) There are problems, if already existing ods-documents contain > CEILING(x;;mode). Even if such usage is seldom, _silent_ change of > meaning is not acceptable. > > (3) I fear a similar problem as with FDIST, that OOo 2.4 and already > existing OOo3 versions does not detect, that the stored formula has a > new meaning in some seldom, but possible cases. > > Even if we say "No implementation in OOo3.2", the ODF 1.2 spec will be > approved sooner or later and solutions are needed. > > kind regards > Regina > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
