Bug 114929 - Make =-3^2 unambiguous by automatically adding brackets
Summary: Make =-3^2 unambiguous by automatically adding brackets
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 141856 (view as bug list)
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2018-01-09 13:53 UTC by Regina Henschel
Modified: 2021-04-23 20:16 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Regina Henschel 2018-01-09 13:53:46 UTC
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.
Comment 1 Karel Hruska 2018-01-10 08:04:43 UTC
(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
Comment 2 Mike Kaganski 2018-01-12 08:48:44 UTC
(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.
Comment 3 Karel Hruska 2018-01-12 09:02:30 UTC
(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.
Comment 4 Mike Kaganski 2018-01-12 09:13:14 UTC
(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).
Comment 5 Karel Hruska 2018-01-12 09:25:28 UTC
(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"?
Comment 6 Olivier Hallot 2018-12-05 20:37:35 UTC
Sub Main

      MsgBox 2^(1/3)              'Works

      MsgBox -(2^(1/3))           'Works too

      MsgBox -2^(1/3)             'FAIL!

End Sub
Comment 7 b. 2020-09-01 17:23:06 UTC
@ 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)'
Comment 8 Mike Kaganski 2021-04-23 19:02:06 UTC
*** Bug 141856 has been marked as a duplicate of this bug. ***