Regina Henschel schrieb:
Hi Daniel,
I do not answer directly, because it seems that I described my concern
not good enough. Next try:
== 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.
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.
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
== 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. 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, 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.
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...
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
