## Excel, OpenOffice, LibreOffice, or when developers think rules do not apply to them

Before programming, programmers usually go to school at least till the age of 16 (and then there is also high school and university for some of them). During the secondary school, pupils all learn the order of operations in mathematics. In particular, they all learn that **-4^2 = -16**, because exponents always take precedence over +, -, * and /. There is no exception to this rule, **except** in the world of spreadsheets, including Excel, OpenOffice and LibreOffice. Here, there are some developers (I hope not all of them) who either never went to school, or preferred to sleep during maths classes. Why? Because in their world, **-4^2 = 16**. Arghh! In the world of spreadsheets, only Gnumeric developers seem to have gone to school and remember what their maths teachers told them, and Gnumeric works just fine. Oh, and guess what? Scientific tools like Gnuplot and Mathematica also return -16, as well as Perl and Python and probably mostly everything else which exists in the world.

But when you report this bug to the OpenOffice (see also here) and LibreOffice Bugzilla installations, you are considered as being completely dumb:

OpenOffice bug 24271 comment 1:

"you're wrong. The function works according to the mathematical rules. -4^2 is -4*-4 or 16"

OpenOffice bug 24271 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'"

LibreOffice bug 37271 comment 1:

"Imho LibO "intuitively" correctly recognizes the difference between a "Sign of a number" ant the subtraction operator. I disagree with reporter's interpretation. [...] Please provide information concerning public available mathematical sources supporting your thesis."

These last two comments are from the same developer (one who definitely never went to school). The OpenOffice bug has been marked as INVALID, and the LibreOffice bug has been marked as NOTABUG (a synonym but more polite bug resolution to say INVALID). It’s just *unacceptable* that developers intentionally ignore mathematicals rules which are universally accepted across the whole world, and decide to reimplement their own rules, and then treat users who are totally right as if they said that 1+1=3. A more respectful (but still unsatisfying) bug resolution would have been WONTFIX with the comment that the reporters of the bugs are right, but due to the way Excel works, and for compatibility reasons with Excel, they are forced to do this incorrect statement that -X^2 = (-X)^2. Also, the helper assistant should pop up and warn the user about this misbehavior.

I hope the developers who wrote these comments above are not the ones who write the core code of OpenOffice/LibreOffice! Else I wouldn’t be surprised to discover that 1+1=3 is finally correct too. :)

no comments!

Hi, I did a little research and the best article I could find was at http://mathforum.org/library/drmath/view/53194.html

Whether that would convince a developer who dismisses your bug reports like they have is a different matter.

Interesting article, and I see that C behaves incorrectly too, it looks like, as they make a distinction between the unary operator – and the substraction sign (anyone with C knowledges can confirm?).

Being myself a maths and physics teacher, this kind of mistakes really irritates me, especially when developers pretend to be right and that their implementation is more intuitive. This means that only pupils who don’t listen to their maths teacher will be right when using Excel/OOo/LibO! :)

Yes, the subtraction operator and the unary – are different things in C, with different precedence, unary – has higher precedence than pretty much every other arithmetic operator except the postfix increment/decrement ones.

Actually all the unary operators that go before the expression they operate on have the same precedence. It certainly makes sense for most of the unary operators to have very high priority, pointer dereference and such really should be quite high…

I could be wrong but I think this debate may exist in mathematics too…

here’s a link with the C operator precedence

http://www.difranco.net/cop2220/op-prec.htm

The claim seems to be that the minus sign should bind tighter than the exponent because it’s not a use of the unary minus operator, it’s a negative numeric literal. There is a case for this, at least. But I observe that (at least with LibreOffice – don’t have any of the others to hand to check) "=-A1^2" (in a cell other than A1) has the same misbehavior. You could maybe try again with that example?

Yes, =-A1^2 has the same misbehavior, which is the reason I reported the bug to LibreOffice two days ago (and developers said this was not a bug but so by design).

Sorry but, I disagree with you. Or, better, you’re right: -4^2 is not equal to (-4)^2. But then, this statement is true in the computer science world, not in the world in general.

If I’m doing a math, physics of chemical exam and I write on a paper -4^2 I expect it to give 16 as a result (just omitting parentheses) and not -16!

While it’s important that -4^2=-16 e.g. in Python and so on, which are used by people who know what they’re doing, programs like Calc or Excel are usually used by everyone, and this everyone expect -4^2 to give 16, and so they behave accordingly.

So: it’s wrong, I agree, but yet correct for the use.

No, -4^2 is the same as -(4^2) also in the world in general. That’s exactly what we teach to pupils in the secondary school. It’s not just a convention in the computer science world.

No offense, but I agree with the developers. Exponent has higher precedence than subtraction, but not over the sign of a number. The "-2" isn’t "0 – 2", it’s a numeric value in it’s own right.

Fortunately you are not one of my students, else you wouldn’t get a good mark at your maths exam! :)

-4^2 = ((-1)*((4)^2))

Or rather, show them this: http://www.google.be/search?q=-4^2

Reading the comments, I had a similar "wtf" thought.

However, after giving it more thought, I think there is a case to be made that PEMDAS/BIDMAS/whatever-your-mnemonic-for-order-of-operations doesn’t come into play here. I think there are two cases which could be reasonably argued. The arguments are:

a) The order of operations as stated in PEMDAS/BIDMAS doesn’t actually dictate what to do here. You’ll note that it only deals with binary operators there–subtraction and negation are not the same thing, even though we use the same sign for both of them. Or,

b) You could argue that not only is the – sign as it appears in, say, -4 not a binary operator, but it isn’t a unary operator, either. It’s a sign. Here we’re discussing the atomicity of -4. Note that even if you take this view, it doesn’t actually help the argument of the developers you’re quoting–applying a atomicity-of-negatives could not yield the answer they defend. [And to be thorough in my comment, I'll note you'd still need to leave room for a true negation operator, so you could do things like -(2*5)].

That just leaves a).

But really, this just smells like a computer science infection, so that it’s possible to take the easy route for parsing ("Unary operators have higher precedence").

Yes, it’s probably a parsing shortcut. But still an incorrect one.

And today I learned why (-1)^n is more appropriate than -1^n.

please try "pow(-4, 2)" in several languages. You will find the result is 16.

Unrelated. We are not talking about (-4)^2, but about -4^2, which should be equivalent to -pow(4,2).

I make a living (at the moment) working with Excel and have over 20 years experience with spreadsheet programs of many flavours.

Excel is a buggy piece of crap – always use brackets, except when it craps out and won’t let you use anymore.

VBA sucks as a language. The formulas do strange things at times and Excel/VBA likes to inexplicably slowdown and/or crash.

And as a bonus – transferring data between MS Excel and MS Access you will often have you numbers interpreted as characters (particularly Access out put into Excel won’t add as Excel thinks they are characters).

Operator order precedence is the least of the worries.

All you can say here is: You are the one being dumb and wrong! YOU!

Why? Because you accuse others of being so, that’s all.

The question is just a matter of operator precedence and mathematics and computer science define the precedence in other ways. In computer science the unary operators are always of highest precedence. In mathematics the unary plus and minus operators are just like the normal plus and minus operators.

So: This is just a question of definition. And people in information technology just defined it *differently*. Not wrong or less correct, by differently.

This doesn’t make any sense and is inaccurate. As I said in my article, scientific tools like gnuplot and mathematica behave correct, as do Perl and Python, two famous programming language. So in computer science, universal mathematical rules still apply. And there should be no exception to this, including Excel. It’s like saying that each country around the world could have their own rules about mathematics…

If you feel that universal mathematical rules apply in computers in the same way they do in abstract mathematical notation, I encourage you to read up on the behavior of floating-point arithmetic.

-4^2 is ambiguous notation. Computer programs and mathematicians interpret it differently.

In maths, it’s really a flattening of dash, four with a little two at the 4’s upper left corner, and represents ‘minus four, squared’, which is indeed 16.

That gets flattened down to ‘-4^2′ when we try to write it on one line. If we had a convention of (for example) of writing negative numbers in red, instead of pre-pending a dash, there’d be no ambiguity over atomicity of ‘-4′: (red 4)^2 would be 16.

In programs, we interpret ‘-x’ not as ‘minus x’ but as ‘x with the negation operator applied to it. The BODMAS precedence rule applies (Brackets, Orders, Division, Multiplication, Addition, Subtraction), and -4^2 get evaluated as -(4^2) -> -16.

Two different sets of conventions, which interpret the same string "-4^2" in two different ways. Arguments can be made for both, and I’ve no idea which should be correct in a spreadsheet.

The solution is, as always when logic or arithmetic statements may be ambiguous, to add appropriate parenthesis.

I guess you meant the opposite? In maths, -4^2 = -16.

‘upper RIGHT corner’ of course.

This appears to be a gray area and not as simple as you believe. I have a degree in Math and Computer Science and I was always been taught that -4^2 = 16. (Some may say that this is a failure of the American education system :))

Not that it is the most reliable source, but the wikipedia article on order of operations (http://en.wikipedia.org/wiki/Order_of_operations#Gaps_in_the_standard) notes this as a gap in the standard, and refers to your interpretation as a convention (albeit the more widely recognized one)

When I was in latin-math high school some forty years ago, the opposite of x² – x + 1 was -x² + x – 1, and woe betide the pupil who thought otherwise, he’d get red marks on his math exam. Replacing the letters by digits changed nothing. This said, now and again I’ve seen programs disobeying the precedence rules I was taught in school, and I’d rather use unnecessary parentheses than fall victim of a misparsing by (e.g.) an interpreter which would always compute in left-to-right sequence rather than obey established precedence rules.

You’re absolutely right.

Math rules says that the power operator takes precedence.

Some people see an ambiguity in this here, which isn’t true. There is no space in math for ambiguity of such things, that’s why you need to put brackets around this construct if you want it to be treated as negative number.

I always thought that -4 is a number, and not an expression involving an operator and a number. If you want to square the number, you simply write a superscript 2 (as with any other number). Having made it through technical university and never ever coming upon the notion that -4² = -16 makes me think that it’s probably culturally biased (Czech Republic speaking here).

On the other hand, -a² seems to naturally parse as -(a²), and also 16 – 4² = 0, but 16 – -4² = 0 and not 32.

-4^2 = 0 – 4^2 = 0-16 = -16

This is the way how it should be evaluated.

Any negative number is the same as subtracting the modulus of that number from zero.

eg: -5 = 0 – |5| = 0 – 5

Just one word: Excel

If Excel does it, then OpenOffice will do it.

Numbers (from iWork) also. Because they want to be compatible with the de-facto standard.

Gnumeric on the other hand always tries to be mathematically correct.

I suspect that most students, mathematicians, scientists, and engineers in this world would evaluate -4^2 as 16 in hand calculations.

I suspect that knowledgable programmers, who may or may not be mathematically talented, would apply the appropriate syntax for the software solving the problems. So if the software was built to interpret -4^2 as -16 then the erudite programmer would apply the strict application of BODMAS or whatever rules of precedence the software writer required.

Excel, OO, Libre Office etc has been set up to interpret it one way while Perl, Python ("famous programming languages") have been set up ( by a human) to read -4^2 differently.

If I wish to communicate with other humans or use excel etc I will use -4^2 =16. If I want my python, perl programs to run correctly I will use (-4)^ = 16.

If I were to teach programming student maths it would be wise for them to get into the habit of expressing negative numbers to be contained in brackets.

For the rest of us mere mortals -4^2=16 has an unambiguous meaning.

Maybe excel does it wrong because it has to parse -4 as a single token, and not an expression consisting of an operator and a number? Otherwise, you’d have to write =-4 to write negative numbers ;-) Anyway, it’s probably for historical reasons "because lotus 1-2-3 did it" and can’t be fixed after several decades of backward compatibility (no matter how frustrating this may be).

I’ve since been conducting an informal experiment between my friends, of the 10 or so answers it was about 50/50, with people having no higher math education often giving the mathematically correct answer (even explaining it with brackets), while other people (including some CS graduates) tending to favor the other answer. All mathematicians agreed on -16.

The most important lesson for me is that when writing parsers, you should never parse negative numbers as a single token (literal value), always as an expression using a unary minus. Even though such a grammar seems completely logical and consistent for almost all expressions (including multiplication! thanks to associativity), once you try to support exponentiation, you’ll invite many a mathematicians wrath: the minus sign will have been gobbled up by the tokenizer into a "number", making mathematically pleasing exponentiation a somewhat complicated business.

In Libre in a single block if you execute

0-4^2 = -16

-4^2 = 16

and =0+A1^2 (where A1 is -4) =16

it’s a world where cells are separate, it’s as if each block has parenthesis around it, which makes some kind of sense to me,

I think requiring the masses to type (-4)^2 would be cumbersome

Programmers would use appropriate parens because of the vague standards. Most of us use more then one language anyway, and things differ from language to language as they do from culture to culture. Because we want accurate results, we would structure it accordingly. The fact that you are debating how the software works so that you don’t have to , shows the differences in the mentality already. Use the parens, problem solved and move on.

Exactly. This is a clear case of RTFM.

If you mean (-4)^2, write that in there, don’t write -(4^2) and expect it to put out the wrong answer (in this case, 16).

A – at the beginning of a cell is interpreted, as it should be, as 0-x. In this case, 4^2 takes precedence over the -, so it’s 0 – 4^2, which is (last I checked) -16.

As far as I’m concerned – and I’m not a developer, just someone who uses the software every day – this is NOT a bug. It is exactly how the software is supposed to work.

I suggest you try again, because what you write is exactly the opposite of how Excel, OOo and LibO work. :) In these softwares, the exponent doesn’t take precedence, which is exactly what I’m complaining about. And from what you write, you agree with me! ;)

Scratch that – in this case, they’re interpreting the first – as marking the number as negative, i.e., (-4)^2, which is actually pretty clever – but that only happens if you type it in the beginning of a cell.

If you type =0-4^16, you’ll get -16; if that’s what you mean to get, then type that! So, I think the lesson here is to type what you mean, and don’t assume the computer is thinking the way you do. Parentheses are always a good habit.

Maybe -4^2 is the same as -4*-4, isn’t it? And as the product of to signs say that should turn out as 16 instead of -16. I think that’s the most obvious view of it.

Maybe that’s what the Lotus (et al.) developers thought, but it’s wrong. -x^2 is – (x*x) and -4^2 is -(4*4), no math teacher will tell you anything else, and in fact, high-school math teachers (I used to be one) will train you, by means of repeated exercises, not to make the confusion.

So it’s actually not the obvious thing, but has to be trained by repeated excercises in high school? ;-)

It is part of what has to be trained. Maybe not the hardest part, and those who don’t get it real fast are maybe not the brightest pupils…

(Moi, il y a longtemps que j’ai arrêté de me battre sur ce sujet avec des gens qui ne veulent pas entendre. Je garde mes forces pour mes élèves. Mais merci Tony de défendre cette cause. :) )

De rien Frédéric. Mes élèves à moi, à l’époque où j’en avais, n’écoutaient pas toujours beaucoup plus qu’ici, mais ici au moins, même pendant le trimestre d’automne, on ne me lancera pas de marrons à la tête. :-)

That expression is ambiguous and is never written like that. It’s either — (4²) when you’re negating or (—4)² when you’re squaring.

As has already been said many times, it is not ambiguous because of the precedence rules for operators: the expression

3*5² – 2*6³

means

(3*(5²)) – (2*(6³))

and nothing else, because multiplication has precedence over subtraction, and exponentiation has precedence over both. Ask your math teacher (or your children’s) if you’re not convinced. (Don’t just lookup a spreadsheet’s manual, since Frédéric’s whole rant is about the fact that spreadsheets disobey the accepted math precedence rules.)

Let’s assume -4^2 "should" mean (-4)^2.

Then -x^2 should mean (-x)^2 = x^2.

And -x^3 should be (-x)^3 = -(x^3).

So if I have -x^2 = -16, I need to simplify into x^2 = -16, whereas if I have -x^3 = -16 I need to simplify into x^3 = 16. Not great, but why not.

Now if I write x^n, with n an unknown integer… what must I do? Well now I need to split the case where n=2p and n=2p+1. This begins to be cumbersome.

And if n is not an integer? –Oh my gaad! How can such things happen in a free society??

So…

If math teachers tell you that -4^2 = -16, it’s for a reason.

While we are at it, the "mere mortals" (with or without a degree in computer science) may also be happy with the idea that x^2 = 16 means x=4. These certainly expect Excel to tell them so. But it’s false, as "obvious" to these "mere mortals" as it may be –luckily Excel has no equation solver, as far as I know.

Should a piece of software reproduce people’s most frequent mistakes, because this makes the software behave according to "common sense"? This does not seem reasonable.

Wow, I’ve been so completely captivated by this thread… I did some sleuthing and it turns out that there are others who have wrestled with this… Some examples: http://mathforum.org/library/drmath/view/69058.html and http://www.perlmonks.org/?node_id=226785

In my mind I think of -4 ^ 2 = -1 * 4 ^ 2 (and that order of precedence is not disputed).

Excel is at its heart a macro environment, which today for the end user is bound together through VBA syntax. It obeys the order of operations of the VBA language, as it should.

Regardless of the proper order of operations, OOo probably figures that it’s far more important to mirror Excel’s system than any mathematical system.

I’ve learned when I was 11 that

-x^2= -(x^2)

-4^2=-(4^2)

that remains true for:

complex numbers

for hand calculators

for ALL mathematical programs (matlab, maple, mathematica…)

especially for the last point I think It’s not a cultural matter.

I found no exception until today, trying to figure out how LibreOffice could have such a bug, and…

I discover now that this behaviour is copied from excel, the first and only program that uses this odd notation.

I don’t think that how programming languages (like Python or C) define (oppositely) them self, should be considered as a valid argument to dispute about an inter-cultural and unambiguous matter as Maths and mathematical expressions.

I see that in C this is not a problem because it doesn’t have the ^ operator, and who uses C must "RTFM" this does not apply to spreadsheets.

Please, please, spudd86, iacchi, Irsyad and anyone else that thinks that this is ambiguous in maths, leave the keyboard for a second and go and see what your maths textbook says.

Suggestion:

spreadshhets could and should "autocorrect" formulas introducing parentheses to clarify what they mean, when they use a mathematical expression with an altered "computer-ish" signification.

It is much more important that new spreadsheet programs give the same results on old spreadsheets as the original spreadsheet program did, than any adherence to mathematical rectitude. By the same argument, it would be far more reasonable to spell English or French with no silent letters, but backward compatibility prevents the idea from catching on. All other rationales are irrelevant.

I would argue that the most important thing is giving the user what they expect. That’s the key to good program design.

Negative four squared is negative four multiplied by negative four. Which equals sixteen, does it not?

99.9% of users would expect that behaviour. A negative multiplied by another negative number is a double negative, therefore a positive – at least that’s what I’ve always been taught.

And you ain’t seen nothing yet ;)

I’m not sure using python is a good example. It doesn’t behave as one would expect either. A classic example is

python -c ‘print 5/2′

2

2? WTF?

To normal human beings this is 2.5. To python, you *obviously* only want an integer result back from this simple arithmetic. How many programming errors do you think that has caused over the years?

It’s a pointless argument, a program should do what you expect and want, even if it’s not ‘technically’ correct. After all, numbers themselves are arbitrary ways to describe mathematics – Arabic numerals are a human creation, not a mathematical one. Convention over rules and configuration, as they say.

Mathematicians might know the difference between a subtraction operator and a negative sign in front of a number, but I don’t, and I’ve done degree level maths. What hope does a normal maths averse person have?!

Apparently, even maths teachers don’t always agree on this one:

http://mathforum.org/library/drmath/view/55713.html

http://mathforum.org/library/drmath/view/55709.html

Different answers in 2002 and 1997… maths is developing :)

You seem to lack the most fundamentals of interpersonal relations to understand that "LibreOffice developers are not free to decide how maths should work." (your bug comment) in English it means "You do not know maths, I do and you’re dumb". I agree with you on the mathematical part.

As an LO contributor, I don’t like to be called ignorant for my decisions, I’m going to skip your bugtickets. Do you behave like that with your colleagues?

This post still attacks the devs, but they brought serious reasons (ODT specification, interoperability, retrocompatibility), you may wish to update the post? Thank you.

The links by Chris contradict you, would you like to update the post? Another reference is Baroncini Manfredi, "Lineamenti di Matematica", page 285, the note at the bottom. Will you admit that you’ve learned some math from this story? What I guess is, you’ll now stand on your opinion and discredit those links. Yeah, if you’re the teacher, and your truth is disputed, what will students think of you? Better call the other ignorant.

Feel free to ignore my bug tickets, I don’t really care. I don’t know why you feel personally attacked. Just because you are a LO contributor?? And no, I don’t wish nor plan to update my post. No reason to.

Wow, I am appalled by the amount of willful ignorance on display here.

I am a mathematics graduate and computer hacker extraordinaire. At my day job, I spend most of my time in Excel; it’s a horrific mess when it comes to mathematics.

My opinion is as follows:

-4^2 is an ambiguous expression, with defaulted precedence. My feeling is that the defaulted precedence in a spreadsheet should be -(4^2), since this agrees with the mathematical world precedence–when using a spreadsheet, I think people intend to get an answer that is mathematically correct.

While I understand that (-4)^2 may be the typical parsing in the computer science world, the fundamental problem is that this is usually different from what a mathematically literate person means when they type -4^2 into a spreadsheet.

I could iterate through all the cases, but here’s the conclusion:

Under the current system, novice users will tend to get an answer that is inconsistent with their intention (mathematical correctness). Advanced users will get the answer they intend regardless, but it will require frustrating workarounds for mathematical experts.

This is in contrast to the proposed alternative:

Under the alternative system, novice users will tend to get an answer consistent with their intention (mathematical correctness). Advanced users will get the answer they intend regardless, but it will require frustrating workarounds for computer science experts.

IMO, the alternative system is better, for two reasons:

– It produces better results for novice users

– Computer science experts do not represent the mainstream userbase

unary_operator.priority > binary_operator.priority

0 – 4^2 = -16, "-" is binary, "^" is binary

-4^2 = 16, "-" is unary

1 * -4^2 = 16, "-" is unary

0 + -4^2 = 16, "-" is unary

What?

What the hell is ‘1 * -4’? That does not exist!! Where are the ( ) to make that VALID?

What math teacher did you get? What is wrong with your school?

Unary? I only heard that word when i searched for wtf was Excel doing.

That came out too rude and i apologize.

It’s frustration from my part on this issue and not your fault.

Certainly it is a bug in Excel and C. Math is older and the more general science than informatics, so informatics should have followed math rules from the beginning. Everything else causes troubles, which we have now.