The order of operations in mathematics clearly states that exponents take precedence over +, -, *, :, but this order is clearly violated when typing =-A1^2 in a cell as it's interpreted as (-A1)^2 instead of -(A1^2). This can trigger major troubles.
Imho LibO "intuitively" correctly recognizes the difference between a "Sign of a number" ant the subtraction operator. I disagree with reporter's interpretation. Discussion might become interesting, please see discussion in related OOo issues: 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" <http://openoffice.org/bugzilla/show_bug.cgi?id=26755> Mathematical error in Calc, -4^2 <http://openoffice.org/bugzilla/show_bug.cgi?id=24271> @Frédéric Buclin: Please provide information concerning public available mathematical sources supporting your thesis.
(In reply to comment #1) > Please provide information concerning public available mathematical sources > supporting your thesis. Oh, I don't need to provide anything. I teach maths and physics, and that's one of the first things we teach to students. 3 correct examples below: Scientific tools like "gnuplot" and Mathematica: gnuplot> print -4**2 -16 (same output when executed within Mathematica) Perl: perl -E 'say -4**2' -16 Python: python -c 'print -4**2' -16 It's wrong to make a distinction between a "sign of a number" and the subtraction operator. LibreOffice developers are not free to decide how maths should work.
MS Excel works like OOo/LibO. I think that a change in LibO will break Excel compatibility.
How "maths work" is surely a much deeper thing than the notational conventions used to express mathematics. Conventions are just that. Excel and LibreOffice apparently happen to use a different lexical and/or syntactic convention here than most other programming languages and conventional notation. Sure, it is a pity. But changing it would be a regression. Just get over it. Would you also say that HP's scientific calculators with their postfix notation (does HP still make those?) also break "how maths work"? What about the APL programming language, with its lack of precedence hierarchy, did that also break "how maths works"?
There are mathematical rules which everyone has to obey. -X^2 means -(X^2). Period. Everything/everyone not following these rules is wrong. If that's how your examples behave, then yes, there are wrong.
@Frédéric Buclin Changing this convention now means breaking compatibility with previous OOo/LibO documents and with others major spreadsheet software. Do you really want this? I don't think so...
I wrote such a detailed comment, but Tor said it "to the point". Full ACK, nothing to add.
Being myself also a programmer, I perfectly understand the compatibility problem, and know how hard such problems are to fix. Where I totally disagree is about comments like comment 1 in this bug or http://openoffice.org/bugzilla/show_bug.cgi?id=24271#c1 or http://openoffice.org/bugzilla/show_bug.cgi?id=24271#c27 where the commenters just cannot remember the basic mathematical rules and prefer to blame the reporters to be dumb when they are right. Resolving such bugs as INVALID just demonstrate that the developer in question should stop programming. Resolving the bug as WONTFIX + a comment which explains "sorry, but we are forced to keep the wrong behavior for compatibility with this broken Excel" is much more respectful. To avoid or at least reduce such trouble, I think it would be great to have the help assistant to pop up when it detects -A1^2 in a cell and warn the user that it will be (incorrectly) interpreted as (-A1)^2.
"NOTABUG" is also valid resolution of this because: a) The order of precedence as commonly used in math has never been formally standardized. It is just a convention. b) LibreOffice never stated to conform to the convention. Actually it cant, because the convention was never formalized. If it would, it would need to formalize the convention itself first, which is out of scope for the project. Assuming that LibreOffice would give a unformalized convention precedence over user expectations (which are formed by spreadsheet applications and an not by scripting languages) is an axiom. And it is wrong. The reasoning behind this can easily be deducted from the mission of the project -- which is the only relevant base here. see: http://www.libreoffice.org/features/
LibreOffice should follow ODF1.2 standard. And in this standard =-3^2 has to be calculated with the minus as sign and not as operator and therefore results in 9. But in schools it is taught, that -3^2 results in -9. I suggest to handle it as Gnumeric, which automatically inserts brackets, when someone enters =-3^2. So entering =-3^2, it is immediately changed to =(-3)^2. So the formula does neither violate the taught precedence nor ODF1.2 standard.
*** Bug 38824 has been marked as a duplicate of this bug. ***
*** Bug 93983 has been marked as a duplicate of this bug. ***
Not being an Excel user I hadn't realised that Excel also deviates from mathematical convention, but I agree that compatibility should be maintained as far as possible. My question is, would a gnumeric-like solution of background-bracketing -x^y to (-x)^y would at least give users a clue that the result may not be what they intended, if they understand the maths behind what they are perhaps trying to achieve. Would such an approach break anything? I have difficulty in accepting that an inaccurate result in a spreadsheet from a formula observing conventions of operator precedence should not be considered potentially a major problem. Also, in maths, for something so fundamental, surely this "convention" is a standard in all but name? Other approaches (postfix etc) might be considered somewhat non-mainstream and I think are likely to be used by those who are mindful of such issues and know what to check for and against (eg. scientists, mathematicians) rather than, say, a doctor or pharmacist calculating a dosage and expecting the spreadsheet to work like maths does...
*** Bug 94568 has been marked as a duplicate of this bug. ***
So as Microsoft developers has made a mistake 20 years ago, LibreOffice also copied this mistake without even checking. And now, to maintain retro-compatibility, we are going to have this wrong behaviour until the end of the time?. Today, I lost 2h until I discovered why the hell my function with around 30 parameters was wrong to discover that Libre-office was wrong. I asked a Mathematician community to be sure before and I end-up against these comments. Possible solution: Change the behaviour to the correct, and add parentheses while opening old files from before the update so they still work.
*** Bug 94580 has been marked as a duplicate of this bug. ***
*** Bug 98248 has been marked as a duplicate of this bug. ***
*** Bug 114922 has been marked as a duplicate of this bug. ***
"Status: RESOLVED NOTABUG (edit)" This is a joke? I've spent two hours of my working time finding out, why expression z = -i_M^2/(3*u) + u + i_DELTA^2/8 does not work, while finding, that required syntax is z = -(i_M^2)/(3*u) + u + i_DELTA^2/8 which is NOT mathematically correct, and it is NOT considered a bug? I thought, that LibreOffice is better than MS Office, but apparently it is not...
Tor set this as Notabug (I don't know whether he is still active) and Björn supported it. But due to a number of duplicates, I personally don't agree with "do nothing" approach. This should be reconsidered, maybe on ESC.
(In reply to Timur from comment #20) > Tor set this as Notabug (I don't know whether he is still active) and Björn > supported it. > But due to a number of duplicates, I personally don't agree with "do > nothing" approach. > This should be reconsidered, maybe on ESC. I agree that this is something that should be discussed in a wider group, especially with mathematicians. Thanks for support.
It seems to be a common problem so should have the feature enhancement to add the ( ) in automatically (I guess) at least filed. Implementing that, of course, requires some coding action - which would need to be done by someone. This bug has got too cluttered for this. I'd suggest that whomever is interested in this - file a new enhancement bug that concisely describes the confusion caused here (which is clearly real), and how to work around it by auto-inserting brackets - Long bug reports are a real problem for developers to read and get interested. Then we can close this bug as a duplicate of that one. Thanks =)
(In reply to Michael Meeks from comment #22) > I'd suggest that whomever > is interested in this - file a new enhancement bug that concisely describes > the confusion caused here (which is clearly real), and how to work around it > by auto-inserting brackets The enhancement request for inserting brackets is in bug 114929. A request to improve the help texts is in bug 114930.
*** Bug 159684 has been marked as a duplicate of this bug. ***