S Perry wrote:
... I checked
line by line. They LOOKED the same. I then inserted a new column and
subtracted the second result from the first, expecting "0," row by
row. Instead, the result was the value that appeared in both cells...
most of the time. ...
It's hard to be sure without having your actual sheet, but I believe
this is the smoking gun: the only way this happens is if one of the two
values is numerically zero, and since the columns appear to have the
same value, that means that one is text, which Calc treats as zero when
used in calculations.
Excel silently converts the text string to its numeric value, whereas
Calc is treating 30% of your numbers as zero. Excel tries to cover the
mistake, whereas Calc tries to punish you for it.
I've worked with spreadsheets since day one, over 25 years, and I've
never encountered anything like this before, except when a cell was
formatted as text, rather than a number. ...
Sure, but have you ever tried to work with a spreadsheet imported from a
different application? It's a lot more complicated to get right.
Doing any important calculation on a spreadsheet is risky--more than
most people realize: there are many subtle ways it can go wrong. But
doing important calculations on a sheet passed between two different
applications is downright foolhardy. That was very smart to double-check
the results!
It certainly can be done, but you have to know the "gotchas" (like this)
to watch for, and how to code the sheet so that it works for both. Even
then, it's still possible for someone to enter a text number in one and
get the wrong answer in the other.
Note: The cell format does not determine whether a cell holds a numeric
value or a text value. That is determined by Calc's examination of the
data when it is entered, and /nothing/ after will change its value type.
Try this (in Calc): File > New > Spreadsheet
A1: '123.45
A2: 123.45
Other than the alignment, they look the same. Both have format
"Number/General". Hmm.
Ok, now apply a 'real' numeric format from the list, say "0.00". Oops,
they still look exactly the same, except for the alignment.
Now menu View > Value Highlighting. The numeric value will appear in
blue, the text in black.
Try Value Highlighting on your sheet--I think you'll see a bunch of
black (text) numbers.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
