Hi Regina, On Friday, 2009-08-14 21:25:22 +0200, Regina Henschel wrote:
>>>> 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 don't think so. Excel allows empty parameters at most places, where the default usually (exceptions prove the rule) is interpreted as 0. Just that having an expression CEILING(,value) that calculates always as 0 is pretty senseless, and already the UI should not allow such input. To my opinion, empty parameters (as opposed to optional parameters) defaulting to some value should be refused unless the specific replacement value depends on other arguments, which is the case for only very few functions. >> 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. Which would be useful only for the wicked Excel "round away from 0" behavior with y!=0, else for the mathematical calculation one could always write CEILING(x). > 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. That would be incompatible with Excel and probably all other applications that support this two parameters form. >> 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? It could be detected, but it's not necessarily reliable, as other flavors of OOo may write a different generator tag. It would be cleaner to document the difference between versions. >>>> == Export == >> 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. In fact it would be _only_ useful in that case. As soon as ODF is to be written, having CEILING_ADD wouldn't help at all and it should be converted to ODFF CEILING. In case of roundtrip from/to Excel also the current approach works fine. It's just with an extra editing where the user uses the function unaware of the differences between OOo and Excel we may run into problems when saving to Excel. That probably would not be solved with an additional CEILING_ADD function. > 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. I wouldn't care about that. If it produces a correct result it would be better than any good looking but error prone approach. > But unfortunately this does not solve the change of meaning in > ods-documents. We shouldn't overrate that. An expression that always results in 0 probably isn't very widespread.. A bug of earlier versions. >>>> 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. In case we'd go for this filter, the !=0 case was the Excel mode, a rewrite would only be necessary when either the mode parameter was missing or evaluated to 0. I think we could say that we make it depend on these cases: missing => rewrite double 0 => rewrite double !=0 => do not rewrite, strip parameter any other expression => write NA() >> 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? It was discussed, and the conclusion was to define it the way it is because there was already an application (OOo) that implemented it. > Or defining one function as standard and > leave the Excel-way function in the 'private' microsoft namespace? I think we never discussed that. It also wouldn't had solved the problem. > 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. We can insert a 0 if mode=1 when encountering ODFF written with OOo<3.3; but as said, we may miss documents that followed the same logic but were written with a different generator tag. > (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. Here I'd say we fixed a bug, and earlier versions got it wrong. Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the [email protected] account, which I use for mailing lists only and don't read from outside Sun. Use [email protected] Thanks.
pgpHgCJxKCuki.pgp
Description: PGP signature
