Description of problem:
The attached spreadsheet file (.xls) behaves differently if it is open in
Microsoft Excel 2003 or in OpenOffice.org Calc 2.3.1.
(At least) totals at the bottom of column I are different.
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. Please open the attached .xls file in Excel 2003 and note the bottom totals
in column I.
2.Please open the attached .xls file in OpenOffice.org Calc and note the (now
different) bottom totals in column I.
Recalculated formulas give different mathematical answers.
Recalculated formulas should give the same mathematical answer.
Please don't shoot me ;-) for the corectness of this file; it was not created by
me. The file is provided „as it is”, without any assumption of its corectness,
just to serve as a testcase. It is one of the many files an average user creates
in his/hers day-by-day work in a large office.
Created attachment 283591 [details]
Microsoft Excel file to test.
As far as I can figure it out, if one tries to export this Excel file as .ods
(either via the Sun plugin for Microsoft Office or by directly opening the file
in Calc), numbers get converted to text: an apostrophe is added in front of each
digit in columns E and F. Of course, further calculations cannot be performed.
I don't know if the above is the only issue that gets an incorrect mathematical
In the mean time, I found no way to (automatically) remove the apostophes or to
format the cells in columns E and F as numbers. Can you please provide a way to
do this or point to the relevant documentation ?
Such an issue is a *serious showstopper* for a less-skilled IT person, let's say
I think the correct behaviour of Calc should be to issue a warning when some
formula tries to perform calculations with numerals that are treated as text.
In Excel 2003's help I've identified a quick procedure for converting a range of
cells that are treated as text back to numerals (by multiplying them with 1,
using Paste Special from the Edit menu).
This seems to be a solution, but the problem remains *very* annoying:
Besides the steps given at the link above, it requires selecting the affected
cells and doing a Format -> Default Formatting; if one doesn't do that, the
regular expression replacement won't work.
Indeed (long delay for me to find a copy of excel), excel itself warns about
this with little warnings symbols, but it does work. What's strange to me is
that it works with text formatted cells of more than one character, but not with
text formatted cells of one character. Let's check with upstream, there's more
expertise there wrt. calc issues and it affects all versions, not just the
fedora one. http://www.openoffice.org/issues/show_bug.cgi?id=85242