Uggh. I hate to do this, but I don't want to have someone trip over this unmarked landmind and possibly lose a bunch of money/reputation/sanity.
All over the world OpenOffice.org/StarOffice is pitched as a replacement for Microsoft office including Excel.
As such OO calc can read MS Excel files, but it does something very very bad. It performs calculations DIFFERENTLY than Excel (no warnings even).
This is pure evil.
The issue is that Excel will perform math calculations on numbers even if the cell is formatted as "TEXT" or has text in it. The very newest Excel indicates something is amiss by having a small green triangle in the corner of the cell, but this behavior is not likely to ever change so as to maintain backwards compatibility.
The problem with OpenOffice Calc is that in most situations (but not always) it will silently ignore numbers in "text" cells that are part of formulas or calculations and in the end produce wrong answers.
This Excel compatibility problem was reported 5 years ago!
See http://www.openoffice.org/issues/show_bug.cgi?id=5658 for the sad sad commentary, many duplicate bug reports as well as the unfortunate inability by the OO.org developer(s) to consider this a problem of any importance.
For example, some developer comments:
"not a defect."
"Not a bug ! Text is text and not a number so you can't calculate with text."
"IMHO not a bug from Calc but from Excel"
"we are not an Excel clone and will not be !"
I *almost* had a $20,000 mistake (not in my favor) thanks to OpenOffice.org Calc while I was creating an invoice from an Excel XLS file emailed to me by a client.
Leave a comment