Square of a Negative Problem in Excel 2007 too... 3

[swearingen]

However you may feel about it, the issue discussed in the following two threads shows up in Excel 2007 as well. Just wanted to warn anyone that has run into the problem...

thread507-156861
thread404-157296


If you "heard" it on the internet, it's guilty until proven innocent. - DCS

http://www.eng-tips.com/supportus.cfm

 

[electricpete]

I think that behavior is explained in terms of operator precedence (higher precedence operators are evaluated first).

We all know * and / have higher precedence than addition and subraction. And exponentiation has higher still.

As already well discussed the unary negation operator is a different beast than the binary subtraction operator (even though both are identified with the same symbol and that's where the confusion comes in. In excel the unary negation operator has higher precedence than the exponentation.

So whether you have -5^2 or --5^2 or ---5^2, the -( ) operator gets evaluated before the exponentiation.

Sorry for beating a dead horse. An entertaining thread at any rate with good contributions from all (even though we all knew how to cope the first time someone mentioned paranetheses)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

So whether you have -5^2 or --5^2 or ---5^2, the -( ) operator gets evaluated before the exponentiation.

Now you mention it, I did know that; as used in the -- trick to get something evaluated first in a formula.

I just hadn't thought about it in this context.

Anyway, good point Pete.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BigInch]

Open Office also gives 50.

My guess is that it's now mathematically proven that two wrongs can make a right, or that some operator precedence inexperienced programmer wrote Excel and OO copied the mistake.

"The top of the organisation doesn't listen sufficiently to what the bottom is saying." Tony Hayward X-CEO BP
"Being GREEN isn't easy." Kermit

http://www.youtube.com/watch?v=hpiIWMWWVco

http://virtualpipeline.spaces.live.com

 

[davidbeach]

I don't understand how there is any confusion. From the excel help file for order of operations:
Operator Description
: (colon)
(single space)

, (comma)
Reference operators
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison

The unary negative is evaluated before exponentiation. It is also common in programming languages for unary operations to occur prior to binary operations.

Without parens to clearly indicate whether that leading '-' is a negation or part of an implied 0-, it does not seem unreasonable for the '-' to be a unary operator and evaluate it prior to the exponentiation.

Entering 0-5^2+5^2 gives the result of zero, excel correctly evaluating the '-' as a binary subtraction operator.

MathCAD forces clarity by inserting () if you want the square of negative 5, but in MathCAD you can see how it is going to parse the equation as it is being entered. Excel has to rely on the order of operations and they clearly state that the unary '-' will happen before the '^', exactly as expected.

 

[BigInch]

Common is a long way from universal and customary, just because they carefully explain they will treat something "wrong", doesn't make it "right" either, although at least they do explain it. That in itself is somewhat unusual for MS, so I would presume they've been called up on it many, many times before.

"The top of the organisation doesn't listen sufficiently to what the bottom is saying." Tony Hayward X-CEO BP
"Being GREEN isn't easy." Kermit

http://www.youtube.com/watch?v=hpiIWMWWVco

http://virtualpipeline.spaces.live.com

 

[electricpete]

I imagine the mistake comment was tongue in cheek, but I will present some discussion why a programmer would intentionally do it that way:.

First and most important, there are a few other languages where unary – has higher presedence than binary operators.... including mysql, quickbase, java. In all these languages -5^2=25. But it is not universal.

As a general rule (not specific to unary negation), unary operators are assigned higher precedence than binary for simpler parsing and more intuitive understanding. It's easier to show this concept with trailing unary operators (placed after the associated variable) than leading unary operators as in the following examples:

5 + 6!
Do you want it to give you 11! or 6! +5..... my guess is the latter so you want the unary operator to have higher precedence.

Another example would be boolean logic single quote for negation.
A OR B'
My guess is you want to evaluate the unary negation before the binary or.

Now in excel + and – are used for logical AND and OR and NEGATION:
so in excel logical A or B' would be expressed
A + -B
As a logical expression you again want it to evaluate unary – before binary +

Now lets switch it
logical A or B' would be expressed
-B + A
Again you want unary before binary

Now what if you are talking numbers (not logic)
-5 +3
Clearly we want the answer to be –2 and not –8. So if we are comparing the precedence of binary add/subtract with unary negate, we still have a strong preference for binary operator first!

Now one might argue that unary negation operator should be given equal precedence to the binary add/subtract and simply read left to right. Well then I would get an error when I try to evaluate
3 + -5
because it tries to evaluate the + before the quantity on the right has even been determined and it cannot.

The above evaluation error can certainly be overcome using a little more complex logic than simple operator precedence, but I hope this shows why as a general rule from a parsing perspective there is some incentive to treat unary operators as higher precedence.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

Note that Google Spreadsheet also does the same thing.

Might even be the same guy. We had a microprocessor design with a particular output inverted from its design spec. The design team left the company and designed to the same military spec, but in a different technology, and lo, the same inverted output appeared in their design.

TTFN

FAQ731-376

 

[electricpete]

Ooops. One type error corrected in bold:

Now what if you are talking numbers (not logic)
-5 +3
Clearly we want the answer to be –2 and not –8. So if we are comparing the precedence of binary add/subtract with unary negate, we still have a strong preference for unary operator first!

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[TDAA]

Swearingen,

The reason I question whether it is the same is that we do not tend to put () around all our negative numbers.

5-10=-5 is typical, and that is how you wrote it from your first math class in grade school.
5-10=(-5)is not so typical.

Therefore, right or wrong based on the arguments, when I see -5^2 I think 25. This also would make more sense to the general public that would want to square a negative number.

You also seemed to do the same when you were going through your calculations:

For the -5:

-(-5)^2 + 5 = -20

In that instance, you did exactly what you say Excel does wrong, you made -5 into (-5)^2, and not -(5)^2. Like I said, that is because we take the number and treat it as a whole. You have done this because "x" is the entity "-5", and you knowingly are taking the opposite of that total quantity once squared.

Given that, I find that it strange that you would want to code in a constant in in such a way. If you want to subtract 25, then do so. Why go through so many steps? In your defense, and where I do not like what they do, is when you have =-A2^2. I would want that treated the way you indicate, but that fits in line with the equation using -x^2.

The point is there though for order of operations to be watched, and that is something I make sure of when I write code. Everyone should do the same, and take heed of your observation. They should also know if they are intending to square a negative number, or want the negative value of a squared number. Writing it in symbol form does not clarify either direction. Writing it in word form does.

 

[swearingen]

Again, unary or no, semantics or no, I have yet to hear any argument against the following:

-x^2 + x^2 = x^2 - x^2 = 0

Now, when you actually put a positive, real number in for x, suddenly it's false. That's a load of crap. It's not "usual," it's not "conjecture," it's not "opinion." It is, as I said in my first post, a standard in mathematics worldwide. Please, please go back to your school texts (or look it up in your children's texts) and you'll see. Understand that I mean no offense to the programmers out there, but you're not mathematicians. You can write it any way you like because they're your programs, but that doesn't mean it follows the standard rules of mathematics.

If I sat here and argued that -x^2 + x^2 = 2x^2 you'd all run me off this site. No educated person, from their first algebra class to however many letters they've added after their name, would say that this is true.

What is the problem? Why can't this be grasped? Why is there an argument? As I said earlier in this thread - I am FLABBERGASTED that there is even an debate, let alone a long debate.


If you "heard" it on the internet, it's guilty until proven innocent. - DCS

http://www.eng-tips.com/supportus.cfm

 

[davidbeach]

I'm rather flabbergasted that you find negative x and minus the quantity x^2 to be the same thing subject to the commutative property. Minus x squared plus x squared equals 2 times the quantity x squared. Not sure why anybody would be run off the site for that statement.

 

[swearingen]

So let me get this straight - you contend that:

-x^2 + x^2 = 2x^2

Is that correct? Am I losing it here?

Well, here's what I contend:

If a = b, then if I multiply both sides by a,
a^2 = ab, then if I subtract b^2 from both sides,
a^2 - b^2 = ab - b^2, then if I factor out an (a-b),
(a-b)(a+b) = b(a-b), then if I divide both sides by (a-b),
a + b = b, but since a = b (from the first line),
b + b = b, and therefore,
2b = b, and dividing by b, we get,
2 = 1

Hey, since we're throwing algebra rules out of the window, why can't 2 = 1?



If you "heard" it on the internet, it's guilty until proven innocent. - DCS

http://www.eng-tips.com/supportus.cfm

 

[stevenal]

In Mathcad -5^2=-25, which is the way I learned order of operation when no brackets are used; squaring before subtraction (zero in front of the minus sign is implied).

Microsoft as usual wrote their own rule and gets 25.

 

[davidbeach]

MathCAD show explicitly how it is going to parse the equation and happened to force a unary negation to be enclosed in (). That's fine, you can see what it is doing. Excel chose to parse it as read since it can't show an explicit parsing. And, yes, I do contend that if you square minus x you get the same result as when you square x.

Minus the quantity x squared is rather different from minus x squared. If x=5 the the first is -25 and the second is 25.

I thought that writing it out would help, but I guess not. Notice that I wrote minus x squared, not minus the quantity x squared. They are quite different. So, yes, minus x squared plus x squared equals two times the quantity x squared. On the other hand, minus the quantity x squared plus x squared equals zero.

If I were to write it out long hand and wanted the negative of the square I'd write -(x^2). Writing -x^2 is what I'd write if I meant (-x)^2. Absent other explicit indication of parsing it is intuitive that all unary operators be evaluated prior to any binary operators. If you want the square of minus 5 there is no way you can do the square first and get the right answer, you have to square the quantity minus 5. In other words:
5
CHS
x^2
5
x^2
+
has always returned 50 and always will. If you want the other result you need:
5
x^2
CHS
5
x^2
+
which gives a result of zero.

 

[electricpete]

In Mathcad -5^2=-25, which is the way I learned order of operation when no brackets are used; squaring before subtraction (zero in front of the minus sign is implied).

Microsoft as usual wrote their own rule and gets 25.

Zero in front of the minus sign is implied?!?
You are saying that unary -X is same as 0 - X

In that case what if you have
5*-3
or with cell addresses
A12*-A16

Then following the well-known rules (multiplication/division before add/subtract) then the first should be 5*0-3 = -3 and the 2nd should be A12*-A16 = -A16.

I can imagine now you are jumping out of your seat to tell me you didn't mean 0-X... you meant (0-X). Ok that's better. Then we have
-5^2 = (0-5)^2 = 25.

Seems like the 0-X logic intended to attack MS's approach ended up supporting MS's approach ;-)

I honestly don't think there is any one absolutely correct answer, but but I can understand why people like swearingen have preferences.

As I said in the other thread, there are enough legitimate reasons to bash Microsoft. This isn't one of the imo.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

I guess you could also say -5^2 = (0-5^2) and we'd be back where we started LOL.

Please don't take it too seriously.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Last comment (for awhile anyway)

I agree with swearingen that if we lived in the algebraic world before computers, there is no doubt -5^2 = -25

But of course we don't live in that world. We have computers. I am typing on one right now.

Let's go back to the task of writing a parser. First step is to separate into blocks that have parentheses. The tough part is to evaluate stuff inside the center parentheses that has no more parentheses but possibly multiple operators and operands. We have to figure out what to do first with this group of operators and operands.

The simple approach is to define operator precedence. Start with highest-precedent operators, evaluate them and substitue in the results. Then move to next highest precedence operator. Continue to lowest and you're done.

So now let's say you want to do this and you want to proclaim as per algebraic convention that exponentiation has a higher precedence than unary negation (opposite of the excel way).

Use this rule to evaluate 3^-2
Right off the bat you're hosed. You have to feed –2 into the exponentiation but you haven't evaluated –2 yet. We have to do the unary negation before the exponentiation to evaluate this expression.

So the logical approach to eliminate this problem is to make the unary operators such as negation a higher precedence and the problem is avoided. It allows use of a simpler parsing algorithms.

The programmer simplifies his own life by making unary operators highest precedence. Maybe in your view his made your life more complicated. He has deviated from the pre-computer-age algebraic convention. But there is plenty of precedent to do it in the programming world. Whether it is "wrong" is in the eye of the beholder.

I imagine if microsoft knew in the beginning what they knew now, they'd reverse their approach and use the algebraic convention suggested by swearingen. But that's out of the question now... you don't want spreadsheets that have been in service for years to give different answers when the version of excel changes.

BTW 2=1 after dividing by (a-b).....it's a good one for those that haven't seen it.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

This is NOT a MS thing, since this effect dates back to Visicalc.

Visicalc did not use nor allow an "=" sign, and the plus and minus signs are unary operators that signify a numerical quantity following. Excel follows that lead, even in the case of
-A1²-A1². If A1 is 5, the resultant is not -50, it's 0.

Interestingly, Visicalc gives 400 as the resultant for
-A1²-A1² when A1=5, so Excel does adhere to PEMDAS more closely than Visicalc.


The bottomline here is that you should avoid starting any calculation in Excel, and probably any spreadsheet, with a leading minus sign.

TTFN

FAQ731-376

 

[rowingengineer]

I would have thought the point was check everything assume nothing.

An expert is a man who has made all the mistakes which can be made in a very narrow field

 

[IDS]

Just as a point of interest for the historians, Lotus 123 (rel 9.5) evaluates -5^2 or =-5^2 as -25. (-5)^2 = 25 of course.

Excel (2010) evaluates -5^2 as +25, even if you select "Transition formula evaluation" and/or "Transition formula entry" in Options - Advanced. Exactly what evaluations are changed by "Transition formula evaluation" I'm not sure.

Gnumeric does it the Mathematica way, i.e. it evaluates -5^2 as 25, but it inserts () around the -5, to make it clear what it is doing.

On reflection, I think it would be a good idea for Microsoft to copy Gnumeric in this regard (and several others).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/