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. >> 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.. >> This is true for the Excel formats xls, xlsx and xlsb. The import >> filter for xml does not alter the Excel formulas and so the formulas in >> the cells are invalid according to ODF1.2. But it could be solved the >> same way with =CEILING(0;value;1) and =CEILING(value;0;1) respectively. >> >> Change the import filter? > > Understood and agreed. This can be added in the import filters. > I think it should be then: > - If 1st param empty, replace with 0 > - If 2nd param empty, replace with 0 > - Add 3rd default param Sounds good. I first thought I could withdraw the new proposal I made for ODFF, 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.. >> == 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. >> 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. >> 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. 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. 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.. 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.
pgp6TAlAkDAF9.pgp
Description: PGP signature
