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]
