The formula =-3^2 can be interpreted as =(-3)^2 as in ODF or OOXML standard or as =0-(3^2) as in common school mathematic and in Writer. This leads to problems for the user. See e.g. bug 37271 and its duplicates. Gnumeric resolves this ambiguity by automatically inserting brackets. So the formula =-3^2 is automatically turned to =(-3)^2 and written to file as such. An alternative approach is to turn =-3^2 to =0-3^2, which makes the minus to a binary operator. I don't know yet, whether MS Office 365 has solved this usability problem. I suggest to do it the same way as Gnumeric. Inserting brackets gives the same result as current.
(In reply to Regina Henschel from comment #0) > The formula =-3^2 can be interpreted as =(-3)^2 as in ODF or OOXML standard > or as =0-(3^2) as in common school mathematic and in Writer. This leads to > problems for the user. See e.g. bug 37271 and its duplicates. > > Gnumeric resolves this ambiguity by automatically inserting brackets. So the > formula =-3^2 is automatically turned to =(-3)^2 and written to file as such. > > An alternative approach is to turn =-3^2 to =0-3^2, which makes the minus to > a binary operator. > > I don't know yet, whether MS Office 365 has solved this usability problem. > > I suggest to do it the same way as Gnumeric. Inserting brackets gives the > same result as current. Hi Regina, thank you for setting this request. I've tested the same on MS Office 2016 and the result is identical - MS says, that -3^2 = 9, no bracket handling is implemented. Google spreadsheet does the same. Karel
(In reply to Regina Henschel from comment #0) > ... as in common school mathematic ... "Common school" mathematics doesn't say anything about priority of minus as a negative number sign. It deals with priorities of operations, and minus in mathematical notation of a number (e.g, -10) is not an operation, but a part of the number, like "1" and "0" are part of 10, and no need to define those "0" and "1" priorities wrt other parts of expression - they substitute an atomic entity. The equivalency of "-10" to "0-10" is computational, it does not translate to equivalency of behavior of expression parts.
(In reply to Mike Kaganski from comment #2) > (In reply to Regina Henschel from comment #0) > > ... as in common school mathematic ... > > "Common school" mathematics doesn't say anything about priority of minus as > a negative number sign. It deals with priorities of operations, and minus in > mathematical notation of a number (e.g, -10) is not an operation, but a part > of the number, like "1" and "0" are part of 10, and no need to define those > "0" and "1" priorities wrt other parts of expression - they substitute an > atomic entity. The equivalency of "-10" to "0-10" is computational, it does > not translate to equivalency of behavior of expression parts. Okay, let's not talk about numbers, but about expressions. Let's cell A1 be number "3" and the content of cell B1 be "=-A1^2". Common school math say, that in the first order the number 3 should be powered by 2, then the minus sign should be added. This is something that LO does not do. Similar example: Common school math says, that relation z = -i_M^2/(3*u) + u + i_DELTA^2/8 is equivalent to z = u + i_DELTA^2/8 - i_M^2/(3*u) If you try to implement the first expression in LO Calc, you MUST add brackets like following z = -(i_M^2)/(3*u) + u + i_DELTA^2/8 otherwise the previously mentioned rule is broken.
(In reply to Karel Hruska from comment #3) I agree with this definition of the problem, when we talk about minus as a negation operation. And in this case, I support variant #1 (autoinserted brackets), which would also expand to the negative numbers mentioned in comment 2 (to avoid user confusion).
(In reply to Mike Kaganski from comment #4) Thank you, then shouldn't be changed the title of the bug to e.g. "Make =-x^n unambiguous by automatically adding brackets"?
Sub Main MsgBox 2^(1/3) 'Works MsgBox -(2^(1/3)) 'Works too MsgBox -2^(1/3) 'FAIL! End Sub
@ Olivier Hallot: hi, your sample is more about 'odd roots' than about parsing and sign operators, and / or more about a term working in sheet but failing in basic / macro, i'd suggest a separate bug report, @all: sad to say and hard to bear: TDF or LO or whoever has in http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#Operators and repeatedly in https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#__RefHeading__1017940_715980110 deliberately made a documented distinction between 'unary' sign operators +/- and binary calculation operators +/-, whereof the former are assigned a higher preference than exponentiating, multiplikation and addition, (in contrast to the fact that some commenters claim that LO would not! arbitrarily set standards), (and imho very! questionable as the same signs get different functionality on very little changes in context) (and imho very! questionable as the evaluation deviates from school math!) but: 1. there will be / have been a plausible reason for this hard and incompatible measure, 2. i can think of little else but compatibility with ex$el, 3. a change becomes very unlikely, 4. imho a automatic insertion of brackets is difficult, when (file open or input) and under what conditions should it be done? How to avoid damage to existing files or intentional settlements, but before do nothing and continue calculating errors and angry users creating new bugs and questions i support the proposal, 5. alternatively i suggest similar to the way ex$el introduced a labeling of potentially wrongly formatted cells - by comparing them with surrounding cells - to adopt this and to add a labeling of possibly wrongly 'formulated' cells, like a small red triangle in the lower right corner of the cell, and a popup-assistant on mouseover: 'be aware that the formula in this cell contains a term that is calculated differently by LO-calc and ordinary school mathematics, calc interprets single minus signs in front of numbers and values as belonging to this number and more tightly bound to it than subsequent arithmetic operators, "-2^2" is thus calculated as 4 instead of -4 (as many users would expect). this quirk was / is necessary to stay compatible to other spreadsheets, for another calculation formulate the term as -(2^2). if there is a value in front of the negative term the "-" sign is used as a calculation operator and it is not necessary to put brackets.' proposed title change: 'ui: calc: formulae: inform users about the special evaluation of 'unary' "-" operators, and avoid errors caused by wrong formulas (in accordance with school mathematics)'
*** Bug 141856 has been marked as a duplicate of this bug. ***
*** Bug 121398 has been marked as a duplicate of this bug. ***
*** Bug 154540 has been marked as a duplicate of this bug. ***
Bumping Importance a little. Another bunch of people (6 or 7 excluding the regulars) are in the cc list of bug 37271.
Created attachment 197260 [details] Video with correct behaviour in gnumeric Attached video gif with correct behaviour in Gnumeric.