Apache OpenOffice (AOO) Bugzilla – Issue 75620
Order of Operations Error
Last modified: 2007-03-22 23:22:50 UTC
The order of mathematical operations is incorrectly implemented in calc. The sequence should be Parenthesis, Exponents, Multiplication & Division, Addition & Subtraction, all taken left to right. By this order, -x^2 and -(x^2) are equivalent expressions. If x = -1, both terms should evaluate to -1. In Calc, the first term evaluates as 1 and the second term evaluates to -1. The fundamental error in this implementation is evident with the equation "-x^2 + 42". By the commutative property of addition and subtraction "42 - x^2" and "-x^2 + 42" should give the same results. In calc, they do not. In order to assure the correct answer, the equation must be written as -(x^2) + 42. These additional parenthesis should not be required. I would note that this incorrect handling of the order of operations is consistent with Microsoft Excel--this is /not/ a product benefit (though it may be appropriate to make this inaccurate behavior a switch for compatibility reasons). The implication of this error is that subtle mathematical errors will propagate through complex worksheets unless the user/programmer takes great care to heavily use parenthesis throughout all of their calculations.
closing invalid, as the given list of operators is missing unary operators, like the minus sign. These have to be the first in the sequence of calculation. So: so -x^2 is equivalent to (-x)^2 as the - is a unary operator. but y-x^2 is equivalent to y-(x^2) as the - is a subtraction operator.
closed
You may have a look at Issue 24271. A single minus sign in front of a number is nothing else than a negation of this number as it can't be an operator. The BODMAS rule just applys to operators not notations. Frank
I respectfully disagree with the responses. Negation is the multiplication by -1. In programming terms, it is a unary operator. Precedence rules in many of the high level languages (Fortran, C, C++, C#, Perl) are common (see ECMA-334:14.2.1) and would provide calculations more consistent with the everyday user while being less likely to result in subtle misunderstandings and incorrect calculations. Using a precedence order different than most common programming languages and different from users expectations and training (high school Algebra) is unnecessary in my opinion and detracts from Calc. Again, I understand the need to be Excel compatible. In the 'best' world, this *feature* could be turned off and a more standard precedence order could be used for calculations.
reopen, as this should rather be closed as duplicate to issue 24271
set duplicate *** This issue has been marked as a duplicate of 24271 ***
closed double
closed again. @mikkl: can you read through Issue 24271 and issue 66735 and see, if some of the proposals (esp. 667359 are usefull? If yes, pleas vote for those issue(s).
@andreschnabel - thank you for pointing out the other two reports. I had searched for cases before posting my issue but was not successful in finding them. I agree that a default insertion of the necessary parenthesis would help clarify the math that is being performed and help to avoid problems debugging spreadsheets while maintaining compatibility with Excel. I will go over and vote for those issues.