Bug 136268 - Math function not evaluated correctly
Summary: Math function not evaluated correctly
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2020-08-29 17:19 UTC by carlkross
Modified: 2024-07-14 15:45 UTC (History)
4 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 carlkross 2020-08-29 17:19:36 UTC
Observed on W-10 and Ubuntu 18.04.

Using exp function as an example.

=exp(2) correct
=exp(-2) correct

=exp(2^2) correct
=exp(-2^2) incorrect
=exp(-1*2^2) correct

Have not done an exhaustive test with other functions.

The minus sign is ignored when exponentiation is present.
Comment 1 b. 2020-08-29 19:05:27 UTC
confirm with: 

Version: 7.1.0.0.alpha0+ (x64)
Build ID: <buildversion>
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: CL

looks as the term is evaluated as 

=exp((-2)^2)

rather than 

=exp(-(2^2))

which is incorrect if you say exponentiation preceedes +/- and 'dotted' calculations, but correct if you understand the minus sign as an integral part of the value -2, 

there have been some discussions about that, i'm not aware if it's consistently handeled / solved for all cases ...
Comment 2 m_a_riosv 2020-08-30 10:11:12 UTC
I think it's a duplicaate of https://bugs.documentfoundation.org/show_bug.cgi?id=121398
Comment 3 b. 2020-08-31 12:05:52 UTC
@m.a.riosv: i don't agree, 

imho this bug is about how to evaluate "-" signs in terms, as a property of the following number or as a calculation statement for the term, e.g. -a^n either as (-a)^n or as -(a^n), 

if you go back you find some duplicates - 114922, 98248, 94580, 94568, 93983, 50596, 38999, 38824, 37271 - from which the last one - imho wrong - was sorted out as notabug, nice discussion with the ever new question mathematical correct vs. 'standard' vs. compatibility to old behaviour vs. compatibility with ex$el, tdf#114929 formulates an enhancement request, 

(mho: on the long run mathematical correctness / compatibility with school math are unavoidable even if any conversion is hard, to get rid of the obstacle of ex$el compatibility without hindering migration or data exchange two different 'calculation-modes' for calc would be necessary, with wondrous consequences for the complexity towards the user)

not marking as duplicate as the 'solution' in 37271 is unsatisfactory, 

tdf#121398 is about how to handle fractions as exponents, which comes up on fractional exponents to negative values, 

it's predecessors are 69293 and 44076 which are partly solved, 121398 still waits for general solution, the tension between correctness and compatibility is the same as above, 

(mho: same as above)

the samples look somewhat similar and the issues may be related somewhere in deeper layers of calc's mathematical concepts and procedures, at least they are in the question 'math vs. compatibility', but on the surface it's two different decisions, interpretation of "-" sign and handling of fractional exponents.
Comment 4 b. 2020-09-01 15:11:53 UTC
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!) 

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. i find the automatic insertion of brackets 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.'
Comment 5 Paulo Carvalho 2022-03-07 23:37:18 UTC
=-3^2 incorrect (it must be equal to -9)
Comment 6 b. 2022-03-08 04:28:43 UTC
(In reply to Paulo Carvalho from comment #5) 
> =-3^2 incorrect (it must be equal to -9) 
 
it's a matter of POV / interpretation / 'standard': 
school math:  '= - ( 3 * 3 )'     -> you are right, 
tdf standard: '= ( -3 ) * ( -3 )' -> Calc is right.
Comment 7 QA Administrators 2024-07-13 03:15:00 UTC Comment hidden (obsolete)
Comment 8 carlkross 2024-07-14 15:45:10 UTC
How the sign is handled seems open to interpretation.

Mathematically, it has always seemed clear what exp(-x^2) meant.

However, Calc seems to interpret it differently.

One has to be very cautious when writing formulas in Calc involving structures such as this.

Thanks to all who commented on the issue.