Apache OpenOffice (AOO) Bugzilla – Issue 24271
Mathematical error in Calc, -4^2
Last modified: 2020-07-02 22:22:00 UTC
There is a mathematical error. If I write the formula "1-4^2" I can see in the cell the number -15, but if I write "-4^2" I see 16 and not -16. In fact in mathemathics -4^2 means -(4)^2, not (-4)^2.
Hi, you're wrong. The function works according to the mathematical rules. -4^2 is -4*-4 or 16 if you want -16 you have to do it like -(4^2). Also Prio 1 is much to high, Prio1 means the whole Application is broken, and this is clearly not the case. Frank
closed inv
Excuse me but I have to insist (I am a maths teacher...): -4^2 must be -16, not 16, this is the mathematical rule (and all other spreadsheets I know give -16).
Not so silly. On my hand calculator Casio Algebra fx2, -2^4 results -16. But on excel, -2^16 resuts 16. But what about other program languages (C, Fortran, R, Scilab,...) ? What is the convention ?
Or more formally speaking, *, / and also ^ have a higher binding priorities then + or -. (The BODMAS rule) > you're wrong. The function works according to the mathematical rules. > -4^2 is -4*-4 or 16 if you want -16 you have to do it like -(4^2). No, exept for special cases where you have a dedicated (-) minus sign that is different from the substraction operator. This is not the case here.
Oooops, I was meaning -4^2...
In normal computer processing (at least since the mid-1970's), the "^" operator is done first (giving "4^2 = 16") and then the functions like x, /, - and +, so we would end up with "-16" as an answer. For this reason, most computer math texts suggest the use of parentheses to clarify something a computer might misinterpret. Thus you would have -(4^2) or even (an extreme example) (-4)^2 to make your intention clear to the computer. Computers also do inside parentheses first and move outward. For OOo to process "-4^2" in a different fashion from a FORTRAN, C++ or BASIC program is a little counter-intuitive for most computer users. However, I would not think of writing such a formula without parentheses as it is visually ambiguous (is the "-" tied to the "4" or to the "4^2"?).
I submit the problem to mathforum.org : >Hi, > >In OpenOffice.org Bug report, there is a question about spreedsheat >program (Excel, OpenOffice.org,...). >In Excel, what -4^2 should result ? 16 or -16 ? > > >[Difficulty] >In excel 97, -4^2 results 16. >In Scilab from INRIA (www.scilab.org), -4^2 reults -16. >In R from R project (www.r-project.org), -4^2 results -16. > >Do you think there is a bug in Excel 97 ? > The answer is : Hi, The normal convention is to interpret -4^2 as -(4^2) = -16. It is indeed true that MS Excel (version 2002) interprets -4^2 as (-4)^2 = 16. However, the on-line help (search for "precedence") specifies that the unary minus has precedence over the ^ operator. It appears therefore that this behaviour, although quite strange and in disagreement with common usage, is intentional. In other words, it's not a bug, it's a feature... See also the specific information on this issue at the bottom of: http://mathforum.org/library/drmath/view/53194.html - Doctor Jacques, The Math Forum <http://mathforum.org/dr.math/>
I tested with Excel 97, R 1.8.1 (www.r-project.org), Scilab 2.72 (www.scilab.org), gnumeric 1.20 (www.gnome.org). Excel 97, enter -4^2 results 16 R 1.8.1, enter -4^2 results -16 Scilab 2.72, enter -4^2 results -16 Gnumeric 1.20, enter -4^2, the input value is transformed into (-4)^2, the result is 16. As you can see, gnumeric treatment is really interesting. Maybe, Openoffice should do the same transformation.
Hmmm, I think the rules which operation has to be processed first do not apply here. Simply because the minus sign is no operator but an inherent part of the unique number '-4'. 16 would be the right result in this case. I think we should preserve excel compliance and stay with the current behaviour. BTW, what is the result of '=-4*-4'? '=-(4*-4)? Best regards, Peter
I understand that OOo must follow Excel to preserve compatibility. But, I'm not really agree to follow all Excel rules. frolloni said he used others spreadsheets, I'd like to know which ones. Personally, I prefer gnumeric tranformation, but -4^2 should be tranformed into -(4^2). And maybe, I'll open a new issue for that.
lcn wrote; >I'm not really agree to follow all Excel rules. I agree with that most of the time. But the impact in this case is much, much bigger than you seem to imagine. Think of documents being im-/exported with a diffent set of rules how to handle calculations. Things like this would really help to preserve the Redmond monopoly because it's a mean pitfall for Migrations. Best regards, Peter
Instead of "Wontfix" why not correct the usage to something more in line with what a mathematician would expect and add an item in Tools > Options under "compatibility" to allow Excel-type formulas?
Hi Robert, I disagree. 1. Making more and more things configurable leads to an growing complexity of the applications. 2. Being in non-compliant mode still creates pitfalls for Im-/Export. You either have to live with it or a developer has to write some routines for the filters to catch this issue. This would mean several days of work in Development and QA. I think it's to much effort and these human resourses can be used for more important things. And we have quite a few of them :-). Peter
Hi, Peter. Yeah. I thought of that after submitting the proposal. From a user's point of view it looks simple, but from a programmer's point of view it's a nightmare. Still, if the re-drafting of the code for 2.0 works smoothly ...? At least we should routinely do what mathematicians expect and then sit back to enjoy favorable comments from mathematicians who discover that we can do it right and Excel cannot (and complaints from non-mathematicians about how we've "messed things up.")
Thank you for the interest. For me it is not a problem of compatibility with Excel (I don't use it, I am a fan of Quattro Pro, and now I am beginning with OpenOffice) it is a mathematical problem. I don't know if in computer science there is a different convention, but in mathematics -4^2 must be -16 (a ^ is stronger then a -), according with Math Forum. In pocket calculators the situation may be different, if there are two different minus signs, but a computer (with its keyboard) has only one minus. Maybe it is usefull for you that I say where I had this problem. I was using the "gaussian function" e^(-x^2) which is universally known in this form and which uses the convention "-4^2=-16". It's has a very big importance in mathematics and stitistics, and I don't think it is useful that OpenOffice trys to change it. Thenk you again.
Added issue for auto transformation for -4^2 like gnumeric. Issue 24405.
Ok, MS claims this to be an excel bug too. http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q132/6/86.asp&NoWebContent=1&NoWebContent=1 I suggest to fix it along with them. Unitl then we should keep the current behaviour.
Hi Daniel, I guess it's yours. Best regards, Peter
Ssummary change : add -4^2 in summary to be little more descriptive
Cannot change this - due to - Compatibility with old documents (formulas would return different results in different Office versions) - Compatibility with main competitors. DR->BH: What do you think about Gnumeric's way to handle this issue (transforming -4^2 to (-4)^2)?
In my own opinion : I think that there few people who type this formula. But if there are, those people must realize there may be an error in their formula. So I think that a radical change is necessary. Nota : If you prefer gnumeric way, As I wrote in issue 24405 : Personnaly, I'd prefer -4^2 to be transformed into -(4^2).
*** Issue 27000 has been marked as a duplicate of this issue. ***
Also got 16 for =-4^2 in both Excel 2003 and Open Office v.1.1.2 However, the default Windows calculator gives -16. System Specs: XP Pro SP2 3.0 GHZ P4 1 GB RAM 60 GB harddrive Open Office v.1.1.2
*** Issue 37129 has been marked as a duplicate of this issue. ***
Indeed window calculator answer is 16 excel 2003 16 too, the reported page from microsoft states it's not a bug but a feature, they state: "Microsoft Excel uses an order of calculation to evaluate operators in formulas. The order of evaluation of operators dictates that a minus sign (-) used as a negation operator (such as -1) is evaluated before all other operators." As a migrating user (60 WS) I will prefer to keep on with MS behaviour instead of breaking the perhaps wrong rule, I prefer to know that I have to put some braces more (as i do actually) when I really want to calculate -(x^2) instead of having to check all the spreadsheets of my company for incompatible math formulas. Best regards Romolo Manfredini
Additional comments from fst Mon Jan 12 03:01:36 -0700 2004 are correct: There isn't any wrong math. handling in OOo, but bay bne we have an EXCEL import problem. Mathemathical handling for first: OOo has an intelligent and correct way to handle those mathematical expressions. Result of input '=-2^4' can't be anything else than (-2)^4! Here the '-' can't be anything else than an algebraic sign, interpretation as a subtraction operator for '-(2^4) is completely useless like a mathematical expression '/3', you always would have to ask "what divided by three" or "what should be reduced by (2^4)". So OOo's interpretation "-2^4 = 16" is completely correct. Now insert a '1' in front of '-2^4' in your spreadsheet cell, so that you get'=1-2^4'. Result changes to '-15', and that also is completely correct, the '-' can't be anything else than a subtraction operator; a mathematical therm with interpretation as algebraic sign "=1(-2)^4" is no valid therm. So there doesn't exist any mathematical problem. 'Additional comments from lcn Tue Jan 13 01:09:17 -0700 2004' told us that EXCEL 2002 has the same result as OOo, so we do not have an open-import problem. If someone will find out, that there is an open import problem for EXCEL97, a new open import problem should be opened. I close this issue INVALID and opened a new Issue 66735 concerning an alternative way of notation.
closed
Just for information. In Google, Have you tried to type -4^2 ? It resuts -(4^2) = -16.
*** Issue 75620 has been marked as a duplicate of this issue. ***
*** Issue 80277 has been marked as a duplicate of this issue. ***
Please close duplicates of this issue as actually *duplicates* of issue 66735. Please note that: -4^2 = -(4^2) = -16 is the mathematical correct expression and every major mathematical (and statistical package, like Mathematica and R) interpret it ONLY this way. [IF you want the number "-4", you need to specify it explicitly with "(-4)"!!!] That said, IF any of this high end packages will ever learn to read .ods files, they would be interpreted *WRONGLY*, so I strongly recommend implementing issue 66735 (even IF the result is mathematically wrong, at least it will be interpreted consistently). Thanks.
*** Issue 86832 has been marked as a duplicate of this issue. ***
(In reply to comment #1) > you're wrong. The function works according to the mathematical rules. > > -4^2 is -4*-4 or 16 When accusing someone of being wrong, it would be great to correct what he said with something which is correct. And in this case, you are TOTALLY wrong! Every single child who went to school learnt that -4^2 = -16 around 12 years old. (In reply to comment #27) > Result of input '=-2^4' can't be anything else than (-2)^4! Here the '-' can't > be anything else than an algebraic sign, interpretation as a subtraction > operator for '-(2^4) is completely useless like a mathematical expression > '/3', > you always would have to ask "what divided by three" or "what should be reduced > by (2^4)". Same comment here. -2 alone is as valid as +2 alone (oh, it's cold today, the temperature is -2°C). I'm very sad to see that someone who is unable to remember basic maths does blame other users. WONTFIX would be more honest and respectful than INVALID!
For what it's worth, I change status to WONTFIX. There are reasons for both variants: a) -4^2 = -16 is mathematically correct b) -4^2 = 16 is how MS Office does it Obviously we can not do both at the same time
This should be fixed, somehow. I understand the argument that in -4^2+7, the leading sign indicates negation rather than subtraction [UNLESS one says that by convention this means 0-4^2+7]. HOWEVER, with -x^2+7, the interpretation is different, and should definitely mean -(x^2)+7. I agree with an earlier poster that maintained that the standard Gaussian Normal statistical expression e^(-x^2/2) should be accepted as is without extra parentheses as e^(-(x^2)/2). In my case, I was generating formulae using Maple, then cutting & pasting them into an Open Calc ods spreadsheet; Maple is a bit idiosyncratic about how it orders the various addends in an expression, so even if my input to Maple was, e.g., "7-x^2", there was a chance that the Maple output would read "-x^2+7" without any forewarning. It took me a long time to diagnose the trouble in my ods spreadsheet and then to locate the several offending cases and de-bug them. SUGGESTION: I suggest that the default Open Calc behavior should be as follows. If a formula begins with a "-" a pop-up window should appear presenting the two main options (A) interpret leading "-t..." as "0-t...", and (B) interpret [consistent with Excel] leading "-t..." as "(-t)...". Further, it would be nice to have check-boxes available to "Always interpret in this chosen way". Perhaps a refinement would be to offer the ability to differentiate between leading "-n..." where n is a numerical value versus leading "-x..." where x is a variable or cell reference. COMPLICATION: The issue is broader than just the "leading term" of a formula, in that it also occurs when an expression is input to some function, e.g., SQRT(-x^2+7) or SIN(-x^2+7), etc. At the very least, such situations should trigger a pop-up warning alerting the user to the ambiguity of interpretation.
nice old discussion, still 'hot' after 16,5 years !! no time to read all, just @lcn (In reply to lcn from comment #4): ... > But on excel, -2^16 resuts 16. ... you should rework your excel, mine (2010 winx64) calculates 65536 which is wrong as it has to be -65536, but yours is 'wronger', 65520 off ... or is 'resuts' another operation than 'results'? sorry, just kidding ... i waste too much time in writing accurate reports which aren't taken serious ... i seriously second @gwojnar from c#36, it should be fixed somehow ... shcouldn't stay 'as is', b.
Okay, i think, I understand the Issue. I think every one thinks that -4 means: negative unary operator four. But it means four with signed bit set. And therefor the forumula -4^2 reads minus four squared two and not negate four squared two. If we are agreen on that this is one step in understanding the different positions. Lets do a formula. We set A1 to 4 and use the Formula "=-A1^2". Now the "-" can not be part of the number. Because the number is a placeholder. But still we will receive a 16. And I think this is doubious behaviour. As soon as the minus does not refer to the number it should negate and not set as a signed bit. Also as a Idea we could become blank sensitive. -4^2 = 16 - 4^2 = -16 Just an Idea to tinker with. Just keep in mind that -x is a short notation for x*(-1), and thus not necessary supported. I leave it for now on won't fix, because I am not exactly sure what the impact of the change would mean. So further analysis is needed. And I think this is not that of an issue.