Issue 26755 - Spreadsheet should not implement all of excels order of precedence for worksheet operators. For example, in Excel "=-X^2 + B" is not equal to "=b - X^2".
Summary: Spreadsheet should not implement all of excels order of precedence for worksh...
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 1.0.0
Hardware: All All
: P3 Trivial with 6 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: ms_interoperability, rfe_eval_ok, usability
: 66735 92515 98823 111477 124192 (view as issue list)
Depends on:
Blocks:
 
Reported: 2004-03-20 04:40 UTC by romerok
Modified: 2014-02-05 23:00 UTC (History)
9 users (show)

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

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description romerok 2004-03-20 04:40:52 UTC 
Copying the order-of-precendence operators for EXCEL is probably unnecessary 
(especially for the clearly wrong unary-minus > exponentiation).  There are no 
good mathematical reasons for choosing this order of precedence for the 
spreadsheets worksheet functions.  It leads to problems where two identical 
algebraic equations have different results depending on the order in which 
formula elements are added.  For example: =-X^2 + B  is not equal to =b - X^2.  
In the first case a "unary" minus is used, in the second a normal minus is. 
This is a severe problem in Excel that leads to many many worksheet errors. I 
think the spreadsheet should offer the ability of using a normal order of 
precedence for operators rather than the clearly wrong, and mistake encouraging 
excel idea.  I have found no circumstance where calculations are not 
immediately fixed by replace unary minus with 0-, however since the 
spreadsheets auto-correct does not work in formulas (excel's does) I can not 
automatically fix the problem using Auto-correct as I do in excel.
Comment 1 frank 2004-03-20 23:25:34 UTC 
Hi,

this is not a defect but could be seen as an request for a new feature. So I
re-flaged it as such. 

Frank
Comment 2 mcardill 2004-09-24 02:30:52 UTC 
I definitly see this as a bug and confirm it. Here's what I did:

1: Input "=-3^2+4" into a spreadsheet cell, result is 13
2: Input "=4-3^2" into another cell, result is -5
3: Input "=-(3^2)+4" into a third cell, result is -5

The problem here seems to be that the program attaches the negative sign to the
3 in step one before doing the square, which it should not, unless manipulated
by parentheses like this: "(-3)^2".
Comment 3 erwin.tenhumberg 2004-10-28 10:05:12 UTC 
enhanced summary, set keywords and reassigned issue according to RFE process
Comment 4 ace_dent 2008-05-16 00:08:17 UTC 
OpenOffice.org Issue Tracker - Feedback Request.

The Issue you raised is currently assigned to 'Requirements' pending review, but
has not been updated within the last 3 years. Please consider re-testing with
one of the latest versions of OOo, as the problem(s) may have already been
addressed. Either use the recent stable
version: http://download.openoffice.org/index.html
or consider trying the new OOo 3 BETA (still in testing):
http://download.openoffice.org/3.0beta/
 
Please report back the outcome so this Issue may be Closed or Progressed as
necessary - otherwise it may be Resolved as Invalid in the future. You may also
wish to search for (and note) any duplicates of this Issue that may have
advanced further by checking the Issue Tracker :
http://www.openoffice.org/issues/query.cgi
 
Many thanks,
Andrew
 
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~
http://marketing.openoffice.org/3.0/announcementbeta.html
Comment 5 rcabane 2008-05-27 22:40:35 UTC 
This issue is commented here. It's clearly an "MS-quirck", since Lotus 1-2-3
didn't have it. 
http://support.microsoft.com/kb/132686/en-us/
Comment 6 rcabane 2008-05-27 22:42:28 UTC 
Another remark : properly dealed with by gnumeric : when you type -A1^2 within
gnumeric, the cell receives indeed -(A1^2) with parentheses added. It's the
right solution.
Comment 7 rcabane 2008-05-27 22:45:36 UTC 
And, of course : the defect remains within OOo 2.4.
Please correct this, it's a pity ! 
(we use OOo in our schools...)
Comment 8 rcabane 2008-05-28 22:42:00 UTC 
More info about this subject:
http://www.macnauchtan.com/pub/precedence.html
Comment 10 rcabane 2008-05-29 13:45:11 UTC 
Please read here:
http://bugzilla.gnome.org/show_bug.cgi?id=115941
Comment 11 ooo 2008-06-02 14:26:25 UTC 
*** Issue 66735 has been marked as a duplicate of this issue. ***
Comment 12 ooo 2008-06-02 14:32:28 UTC 
Confirmed. Input of =-x^y probably should be changed to read =(-x)^y
Comment 13 vinc17 2008-06-02 15:58:02 UTC 
Not all OpenOffice users are also Excel users (or users of other spreadsheets).
Many of them have used calculators for which the rule is the conventional (and
more common across software) mathematical one: -x^y is -(x^y). So, when the user
types (or copy-pastes) such a formula, OpenOffice should rather interpret it as
-(x^y); there could possibly be a configuration option to support both
interpretations. To avoid any ambiguity, OpenOffice should output explicit
parentheses in both cases.

Concerning file formats, just use the specifications when reading or writing
spreadsheet files. When writing files, adding explicit parentheses could also be
safer, in particular wrt 3rd-party applications that could easily interpret
formulas in the wrong way.
Comment 14 lcn 2008-06-24 02:23:53 UTC 
In Google, Have yous tried to type -4^2 ?
It resuts -(4^2) = -16.
Comment 15 frank 2008-08-06 14:29:42 UTC 
*** Issue 92515 has been marked as a duplicate of this issue. ***
Comment 16 discoleo 2008-08-06 17:53:04 UTC 
To add further weight to the mathematical correct interpretation:
 - all scientifically advanced programs DO interpret -4^2 = -16
 - including mathematical and statistical software, e.g:
   R, Octave, Mathematica (did not test it personally, but I am confident
   that the person whom I asked, did actually test it), ...

Adding parenthesis (as does gnumeric) is necessary for some other reason, too:
 - files may get imported into different software that:
   -- DO interpret it mathematically correct
   -- BUT do NOT implement all ODF-engine (and every quirk)
      [e.g. a simple ODF-parser/importer]
 - ODF and implementations might change in the future and handle
   such expressions correctly
   -- this will break all backwards-compatibility and
   -- make old spreadsheets rather unusable

To be safe for future changes, removing all mathematical ambiguity when saving
these expressions is the better approach.

Another weirdness of the current implementation is described in issue 92515:
   -(5+3)^2 is interpreted as  + 64  instead of -64
  [tested in OOo-dev m28]
If it wasn't tragic, it would be rather grotesque. In the latter case it can't
be even argued that the number (-8) is meant, because we have "-(an
expression)". Also, this breaks completely the mathematical commutativity, like:
x - b^n != - b^n + x

Hope this will foster adopting sound mathematical rules in interpreting such
expressions.
Comment 17 ooo 2009-02-04 10:18:46 UTC 
*** Issue 98823 has been marked as a duplicate of this issue. ***
Comment 18 zoominee 2009-04-24 07:49:25 UTC 
I suggest adding the appropriate conversions into the Microsoft formats input/
output converter and implementing the mathematically correct precedence rule in 
OpenOffice.Org starting from the next major version.
Comment 19 Regina Henschel 2010-05-08 13:37:02 UTC 
*** Issue 111477 has been marked as a duplicate of this issue. ***
Comment 20 Frédéric Buclin 2011-05-17 17:37:07 UTC 
It's unacceptable that developers (either working on Excel or OpenOffice or LibreOffice) decide how -X^2 should be understood. There are mathematical rules which do exist, that we teach to our students, and that everybody in the world has to obey. This decision is not yours. Deliberately violating these rules can lead to dramatic consequences in various areas.

-X^2 means -(X^2). Period. Scientific tools like gnuplot and Mathematica correctly return -4**2 = -16. Same for Perl and Python. Same for everything else which works correctly.
Comment 21 Regina Henschel 2014-02-05 23:00:15 UTC 
*** Issue 124192 has been marked as a duplicate of this issue. ***