John Kane wrote:
As far as I am concerned it is clearly an error and potentially a very
serious one but then I am the one who brought it up. I have reported it
as Issue #: 58903 . It does not seem to be getting a lot of attention.
This may be because Excell and Quatro Pro are just as guilty of this
type of behaviour.
Below is an excerpt from the Calc Help: (this is almost the same as the
text in the OOoAuthors' Getting Started with Calc chapter.
-----------------------------------------------------------------------------------------------------------------------------
You can format numbers as text in OpenOffice.org Calc. Open the context
menu of a cell or range of cells and choose Format Cells - Numbers, then
select "Text" from the Category list. Any numbers subsequently entered
into the formatted range are interpreted as text. The display of these
"numbers" is left-justified, just as with other text.
TIP When numbers are formatted as text, they cannot be used in
calculations or formulas.
operations will not work on it. It will either be ignored or will
produce an error of some kind.
----------------------------------------------------------------------------------------------------
Unfortunately this is not true. Some mathematical operations on a text
or string value will return an incorrect number with no warning. As a
simple example type cat into cell A1 and dog into B1
Let C1 = A1* B1. C1 = 0. Or if A1 is 5 and B1 is fud then if C1 =
A1 + B1 then C1=5.
Somehow I have a problem believing that cat + dog = 0.
You can see my calculations at http://www.mytempdir.com/360489
-----
John Kane
Kingston ON Canada
[EMAIL PROTECTED]
(613)888-2399
Cat + dog = 0 may not make much sense. But 3 + 2 + equivalent + missing
+ 6 = 11 makes perfect sense in some contexts.
Assuming I have undertood you corrrectly, I agree. The difficulty is
knowing that you are doing it. however it appears to me that we need a
missing not a string variable that resets itsel to missing. You made me
think of a simple test
I tried A1='23 (text) , A2=5, A= 2
C1 = A1+A2+A3 = 7
C2=sum(A1:A3) = 7
Count(A1:A3)=2
In one way this is what we want (or intended) however there is no way
except by manually adding the numbers to be sure that this is correct.
And of course if we really intended A1 to be numeric, not text, there is
nothing in either equation to warn of a mistake.
Treating a number as text
and giving it a value of 0 for calculation purposes is risky. A program
can test for content which is strictly numeric, and it's safe to assume
that those text numbers should be included in calculations in most
cases. But what about part numbers, serial numbers and such? Certainly
don't want those included in the count.
This is essentially my worry. Accidentally adding a part number to a
cost estimate may not be good buinsess. The Calc Help says we should not
be able to do it but we can and with no warning.
This "bug" isn't as straightforward as it might appear.
No it is not and I consider it to be dangerous. I don't know that we
can call it a 'bug'; perhaps more of a "design feature" since Quatro Pro
and Excel seem to do similar things.
-----
John Kane
Kingston ON Canada
[EMAIL PROTECTED]
(613)888-2399
Perhaps the correct solution lies in doing exactly what you're doing:
Warning users of the limitations of a tool. Any time a program is
written in C, Python, awk, etc., one usually proves the reliability of
the program by using several test data sets, the results of which are
already known.
Anybody generating a spreadsheet template should also do this. For
example, I ran into a problem with rounding while building a spreadsheet
for some real estate appraisal analyses. Unless you tell the program to
use "precision as shown," dollar amounts in the hundreds of thousands of
dollars will be taken to full precision when calculating depreciation.
The small differences introduced by rounding can amount to several
hundred, or even a few thousand dollars when that small difference (it's
not an error) is compounded over several calculations. So to prove my
spreadsheet, I had to push aside the $4,000 computer and rely on the $60
financial calculator. People round their calculations; computers don't,
unless they're told to do so.
So perhaps a tutorial or help document whose topic addresses the
limitations of spreadsheets in general would be in order. Looks like
you've got a good start.
