Issue 58903 - Illegal arithmetic operations on text cells undetected
Summary: Illegal arithmetic operations on text cells undetected
Status: CLOSED DUPLICATE of issue 5658
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0
Hardware: All Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Keywords: oooqa
Depends on: 5658
  Show dependency tree
Reported: 2005-12-05 16:11 UTC by jrkrideau
Modified: 2006-02-16 09:45 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Calc spreadsheet illustraing illegal arithmetic operations (11.73 KB, application/vnd.sun.xml.calc)
2005-12-06 14:35 UTC, jrkrideau
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jrkrideau 2005-12-05 16:11:20 UTC
It is possible to carry out arithmetic operations on text cells.  If one has a
text value of '9999 in a cell formatted as a text cell an arithmetic operation
does not return an error but rather it returns a number. The number returned is
dependent upon the operation  Note that Excel does this in some circumstances
but the values returned are not the same as Calc's.

Below are some examples of operations on text 'numbers' on a real number both
for Calc and Excel. In my opinion the results from both of these are
unacceptable and  very dangerous in some situations.  

Calc Version 2.0.0 (Final) 
   Check of arithmetic operations on text cells. 
   A1 = 5   A2 = foo  C1=999   <- C1 is a Text formated cell    
   Operation      Result          
   Add A1 + B1      5          
   Sum (A1:B1)      5          
   Multiply A1*B1   0 
   Sum (A1:C1)      5 
   Add A1 + C1      5 
   Sum(A1:C1)       5          
   Multiply A1+C1   0          
   All of these operations should have returned an error of #Value.  

   The equivalent results from Excel (2003) are 
   A1 = 5   B1 = foo    C1 = 999      <- C1 is a Text formated cell 
   Operation      Result    
   Add A1 + B1    #VALUE!    
   Sum (A1:B1)     5    
   Multiply A1*B1  #VALUE!    
   Add A1 + C1     #VALUE!    
   Sum (A1:C1)     5    
   Multiply A1*C1  4995 

   All of these operations should have returned an error of  #Value.
Comment 1 Rainer Bielefeld 2005-12-06 06:52:21 UTC
pls. attach your spreadsheet!
Comment 2 Rainer Bielefeld 2005-12-06 07:27:24 UTC
OOo is not EXCEL, and only that EXCEL handles things in another way is no reason
to change OOo behaviour.

This issue seems to be related or DUP to issue 5658. Or course, an EXCEL
spreadsheet should still work after import to OOo, but I do not think that it
would be a good idea to to change approved OOo behaviour.

So it is useful to think about import rules or even better a special "EXCEL
compatibility mode", that will to allow to open .xls content EXCEL-like. This
second solution is more favourable because it will allow to reexport data so
that it can be used with EXCEL without problems.
Comment 3 jrkrideau 2005-12-06 14:35:01 UTC
Created attachment 32131 [details]
Calc spreadsheet illustraing illegal arithmetic operations
Comment 4 jrkrideau 2005-12-06 16:29:52 UTC
My thanks to rainerbielefeld  for such a fast reply to my issue and for
identifying Issue 5658 as a related issue. I am not familiar with the issue
tracker and clearly did a poor search.  He is correct that this  issue is
closely  related to Issue 5658 however Issue 5658 is only a symptom of a larger
problem that this issue (58903) raises. 

One should not be able to add or multiply a numeric variable (cell) to a
character string variable and get a numeric value.  This is what Calc does.

 Let A1=5 & A2 = foo,  then A1+A2 =5  

Similarly  let A1= '01234 & A2=5, then A1*A2 = 0

It is not a matter that Excel works differently than Calc.  The problem is that
Calc permits  illegal arithmetic operations. 

Of course, so does  Excel. I pointed out the Excel example to show that this
problem does not appear to be simply a Calc problem but a problem in at least
one other spreadsheet program.  
Comment 5 Rainer Bielefeld 2005-12-06 18:09:44 UTC
That all is a question of philosophy. Of course some of those operations are
"illegal", but on the other hand many people say that it's simply
"user-friendly" not to be too strict ... . It seems that EXCEL also accepts a
SUM() with a string in the summation area [Sum(A5:B5) in your examples].

The question ist: does OOo behaviour really cause more real problems than
incompatibility to EXCEL? 
Comment 6 jrkrideau 2005-12-06 20:44:33 UTC
Re: “That all is a question of philosophy.â€

It is not a question of philosophy unless we are referring to something like
Principia Mathematica.   Calc (and Excel) perform illegal mathematical
operations in a way that may be completely undetectable to the user.

This issue first came to my attention in the OOo Calc forum where someone was
having a problem with data that his company was receiving in Excel from their
suppliers (Issue 5658 clearly). However it soon became apparent that there was
more of a problem,  both with Calc and with Excel. 

My advice to the poster, now,would be to go back to the suppliers and verify the
information that they are receiving. Given that the Excel spread sheets are
badly set up they cannot trust the suppliers' information.

To me, the first requirement for user-friendliness is that that the spread sheet
does not lie to me. Here, I can carry out an illegal operation and, instead of
an error message, I get what may be a plausible result.

The fact that Excel makes the same type of errors is not exactly an excuse for
Calc doing the same.  Excel errors and Calc errors are just different enough
that they are likely to compound the problem since if I am aware of the Calc
errors and watch out for them in Excel then the Excel errors may slip by me or
vis versa.

The question, also, is not “does OOo behaviour really cause more real problems
than incompatibility to Excel.† The incompatibility already exists as Issue
5658 shows.  Both spreadsheets do illegal operations, they just do them differently.

The real question is, “Do we want to have a  a spreadsheet that we know is
faulty, where the faults  may be  very serious and potentially life threatening.  

Spreadsheets are used in many areas including scientific research , financial
modelling and engineering.  A quick google for Excel and these keywords brought
up a number of instances. Here are two for

A user/builder of large and complex spreadsheets who are most at risk since they
will have the knowledge to design spreadsheets with string and numeric data
properly formatted as a means to reduce the chance of error.  

They may accidentally do something like use a postal code of the form 1234 as a
numeric value in a model by using  the wrong column of data or perhaps the wrong
data-range name in a formula. These errors can have very seriou with serious
real world consequences.

Worse in a way, they will be less likely to suspect an error since they “knowâ€
that you  cannot multiply a postal code by a number since they have carefully
formatted it as a character string. And the Calc Help clearly states this.

I don't like the thought that  an Airbus aircraft engineer or perhaps my
government's  finance department may be making decisions on bad information. 
Nor do I want my pharmacist or doctor calculating a drug dosage for me based on
my street number rather than my weight.

Comment 7 jrkrideau 2005-12-07 22:03:03 UTC
It was pointed out to me that the behaviour of the summary functions (sum in my
example)are actually working correctly. My apologies for including them.  

The arithmetical operations remain illegal however. 
I have also noted that if we have 
A1 = 5; B1 = Foo; C1 = '999; and D1 = Harry then
B1-A1 = -5, 
B1 + D1 = 0, 
B1/D1 = #Value.
Comment 8 hwoarang 2006-02-15 12:49:49 UTC

After reading issue 5658 (with all FS's comments and others issues marked as
duplicated), for me this one is a clearly issue 5658 duplicated. 

But I will wait for FS decision.

Anyway, reproducible/confirmed.

Comment 9 frank 2006-02-16 09:45:24 UTC

Issue 5658 handles all the things related to text values and calculating.
Therefore I close this Issue as double.


*** This issue has been marked as a duplicate of 5658 ***
Comment 10 frank 2006-02-16 09:45:53 UTC
closed double