Issue 24271 - Mathematical error in Calc, -4^2
Summary: Mathematical error in Calc, -4^2
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1.1
Hardware: All All
: P4 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact: issues@sc
Keywords: oooqa
: 27000 75620 80277 86832 (view as issue list)
Depends on:
Reported: 2004-01-10 16:32 UTC by frolloni
Modified: 2020-07-02 22:22 UTC (History)
10 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description frolloni 2004-01-10 16:32:42 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.
Comment 1 frank 2004-01-12 10:01:36 UTC

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.

Comment 2 frank 2004-01-12 10:02:06 UTC
closed inv
Comment 3 frolloni 2004-01-12 20:19:10 UTC
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).
Comment 4 lcn 2004-01-12 20:32:34 UTC
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 ?
Comment 5 quetschke 2004-01-12 20:37:16 UTC
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.

Comment 6 lcn 2004-01-12 20:41:38 UTC
Oooops, I was meaning -4^2...
Comment 7 rblackeagle 2004-01-12 23:49:18 UTC
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"?).
Comment 8 lcn 2004-01-13 08:09:17 UTC
I submit the problem to :

>In Bug report, there is a question about spreedsheat 
>program (Excel,,...).
>In Excel, what -4^2 should result ? 16 or -16 ?
>In excel 97, -4^2 results 16.
>In Scilab from INRIA (, -4^2 reults -16.
>In R from R project (, -4^2 results -16.
>Do you think there is a bug in Excel 97 ?

The answer is :


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:

- Doctor Jacques, The Math Forum
Comment 9 lcn 2004-01-13 08:19:45 UTC
I tested with Excel 97, R 1.8.1 (, Scilab 2.72 
(, gnumeric 1.20 (

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.

Comment 10 peter.junge 2004-01-13 08:29:12 UTC
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
Comment 11 lcn 2004-01-13 08:46:30 UTC
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.
Comment 12 peter.junge 2004-01-13 09:19:57 UTC
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
Comment 13 rblackeagle 2004-01-13 18:30:50 UTC
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?
Comment 14 peter.junge 2004-01-14 10:33:55 UTC
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 :-).

Comment 15 rblackeagle 2004-01-14 17:05:09 UTC
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.")
Comment 16 frolloni 2004-01-14 18:29:05 UTC
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.
Comment 17 lcn 2004-01-14 19:14:15 UTC
Added issue for auto transformation for -4^2 like gnumeric.
Issue 24405.
Comment 18 peter.junge 2004-01-14 19:20:59 UTC
MS claims this to be an excel bug too.
I suggest to fix it along with them. Unitl then we should keep the current
Comment 19 peter.junge 2004-01-14 19:23:14 UTC
Hi Daniel,
I guess it's yours.
Best regards, Peter
Comment 20 lcn 2004-01-20 18:16:47 UTC
Ssummary change : add -4^2 in summary to be little more descriptive
Comment 21 daniel.rentz 2004-02-02 13:54:46 UTC
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)?
Comment 22 lcn 2004-02-02 20:19:26 UTC
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).
Comment 23 frank 2004-03-29 09:07:49 UTC
*** Issue 27000 has been marked as a duplicate of this issue. ***
Comment 24 groundczero 2004-09-22 23:22:14 UTC
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
60 GB harddrive
Open Office v.1.1.2
Comment 25 frank 2004-11-15 09:05:50 UTC
*** Issue 37129 has been marked as a duplicate of this issue. ***
Comment 26 romolo 2005-12-12 01:34:46 UTC
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 

"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
Comment 27 Rainer Bielefeld 2006-06-26 07:31:27 UTC
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.
Comment 28 Rainer Bielefeld 2006-07-16 18:16:00 UTC
Comment 29 lcn 2007-03-03 22:50:08 UTC
Just for information.
In Google, Have you tried to type -4^2 ?
It resuts -(4^2) = -16.
Comment 30 andreschnabel 2007-03-22 12:29:36 UTC
*** Issue 75620 has been marked as a duplicate of this issue. ***
Comment 31 frank 2007-08-02 08:40:19 UTC
*** Issue 80277 has been marked as a duplicate of this issue. ***
Comment 32 discoleo 2007-08-02 18:51:00 UTC
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).

Comment 33 amy2008 2008-09-08 07:22:03 UTC
*** Issue 86832 has been marked as a duplicate of this issue. ***
Comment 34 Frédéric Buclin 2011-05-17 18:03:12 UTC
(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!
Comment 35 Andre 2013-05-27 12:26:18 UTC
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
Comment 36 gwojnar 2019-08-14 21:56:00 UTC
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.
Comment 37 b. 2020-07-02 19:02:07 UTC
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', 

Comment 38 Peter 2020-07-02 22:22:00 UTC
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.