Apache OpenOffice (AOO) Bugzilla – Issue 58903
Illegal arithmetic operations on text cells undetected
Last modified: 2006-02-16 09:45:53 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.
@jrkrideau: pls. attach your spreadsheet!
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.
Created attachment 32131 [details] Calc spreadsheet illustraing illegal arithmetic operations
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.
@jrkrideau 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?
@rainerbielefeld 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 illustration.http://finance.wharton.upenn.edu/~benninga/pfe.html http://www.decisioneering.com/engineering/ 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.
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=0 B1/D1 = #Value.
confirmed. 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. Hwoarang
Hi, Issue 5658 handles all the things related to text values and calculating. Therefore I close this Issue as double. Frank *** This issue has been marked as a duplicate of 5658 ***
closed double