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

 

[zdas04]

Both of those threads are amazing to me. I can't believe how many ENGINEERS claim something is true because they want it to be. The example that people kept using is:

-5^2+5^2=50 in Excel (and should be zero) and the commutative law says that 5^2-5^2 should give the same answer, but it doesn't (it gives zero in Excel).

One way to "explain" it is that there is no such thing as a subtraction operator (it is +(-1)*) as in:

(-1)*5^2+5^2=0=5^2+(-1)*5^2 and the commutative law is satisfied

That way the it is clear that ((-1)*5)^2 which is positive and (-1)*5^2 is negative.

Most of the time I would do =A1^2 which always makes -5 into +25. I can't remember the last time I typed a number into an equation (even constants get stuck in cells and then named so something like "specific gravity" shows up as "SG" in the equation instead of AA35 or 0.72)

This is a good thing to know, but I'm still looking for a credit card calculator that knows that 1+5*7 =36 not 42.

David

 

[swearingen]

As you can tell from my responses, I was flabbergasted that there was even 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

 

[IDS]

Maybe there was a debate because it is not particularly helpful to say that Excel is "wrong".

The meaning of the symbols is ambiguous unless you use brackets, so the message to always use brackets is good advice, and I far as I saw, no-one disputed it.

By the way, it still works the same in Excel 2010, and I imagine it will continue to do so into the future.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[TDAA]

-5="Negative 5"

"Negative 5"^2=25

 

[zdas04]

Did you read the referenced threads? Must be 25 guys made a bald faced statement like you just made TDAA, and all of their assertions did not make them right.

David

 

[jhardy1]

swearingen said:

As you can tell from my responses, I was flabbergasted that there was even debate, let alone a LONG debate...

And yet you post again here, 4 years later?!

 

[zdas04]

The 2006 stuff was all about Excel 2003. This thread is just informing that 2007 has the same issue. Seems appropriate to me.

David

 

[IRstuff]

which explains 1 of the 9 posts in this thread

TTFN

FAQ731-376

 

[TDAA]

Yes, I did read them, did you read the title of the thread? Read what the title of the thread says: "Square of a Negative"

Last time I checked, one negative multiplied by the same negative number (negative number squared) is a positive number.

Why the discussions lasted so long was not because what I wrote was wrong, but the titles of the thread are misleading. What the arguments are is about order of operations, and not about squaring a negative number, which is what the title indicates!

sweringen proves my point on June 15, 06 (bottom link):

For the -5:

-(-5)^2 + 5 = -20
You must square what's in the parentheses, (-5)^2 = 25.
-(25) + 5 = -20
-25 + 5 = -20
-20 = -20

Let's rewrite that:
For the "Negative 5":

-("Negative 5")^2 + 5 = -20
[bold]You must square what's in the parentheses, ("Negative 5")^2 = 25.[/bold]
-(25) + 5 = -20
-25 + 5 = -20
-20 = -20

OH SNAP, he said the same thing I did!!!! The only difference is he multiplied the

So, the issue is not that Excel does not know how to square a negative number, but that the title of this post was written incorrectly. It falls more in line with an "order of operations when multiplying a negative 1 times a squared number.

FYI, in Excel 2000, 5^2--5^2=0

 

[IDS]

I'm not sure what people are getting so irate about. It doesn't hurt to post a reminder of the dangers of squaring negative numbers, even if some of us don't agree with all the wording.

My preferred way to look at the logic of the way Excel handles it is that it treats a single "-" as being a subtraction operator if it is between two numbers, and a negative number indicator if not, or if the "-" is inside (). If there are two "-" signs, or a +-, it treats the first one as the operator and the second one as the negative indicator. So:

=25+5^2 = 50
=25+(-5)^2 = 50
=25+-5^2 = 50
But
=25-5^2 = 0
=25-(5)^2 = 0

All logical and consistent, but open to misinterpretation if you don't know the rules.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[TDAA]

II agree with you IDS. It is good to know what you are equating in Excel. I check anything with much complexity to see that I have typed in the logic that I thought I did.

The only thing that irritated me was zdas04 indicating that my "bald faced statement" was not right. I disagree with that statement, and still do not see how squaring a negative number is not a positive result. The wording is much different from the symbols, and it does mean something totally different.

 

[zdas04]

Sorry if that bothered you, but your entire post was:

-5="Negative 5"
"Negative 5"^2=25

The point of the discussion is not the math, it is a warning that Excel may give you an answer that deviates from your expectations. It is a pretty good assumption that everyone on eng-tips.com knows that (-5)*(-5)=+25. You can also safely bet that all of us know that writing unabigous equations is better than writing ambigious ones. The problem is that I expect that

-5^2+5^2 should equal 5^2-5^2

and in Excel it doesn't (in MathCAD it does). That tells me that I should never start an equation with a negative sign unless I'm really careful about brackets. I find that to be useful information and appreciate Swerengen pointing it out. Everyone uses ambigious arithmetic from time to time, and it is good to know the the assumptions that the software is using.

David Simpson, PE
MuleShoe Engineering

http://www.muleshoe-eng.com

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips Fora.

"It is always a poor idea to ask your Bridge Club for medical advice or a collection of geek engineers for legal advice"

 

[TDAA]

I understand the point of the post, and I agree with it.

The point of my comment was that so many people are jumping up and down about how to properly enter the formula, but (IMO) so many have misinterpreted the word problem from the start (pretty much misusing the term negative).

This may be off base for what some argued, but I think it illustrates the point: For some it may come down to calling "-5" "minus 5". Personally I think this is incorrect, as it implies there is an operation going on. In our most basic of math (was it first grade?) we learned that "-5" is a "negative number" - it is a defined entity. Later, we learned to square that entity and get 25, not -25.

Swearingen's post basically proved the point that excel does properly square "-5" and the issue is more just that it is (IMO) poor form to start the equation with an operator. Excel allows you to do that with the "+" or"-" because it can be used to indicate a positive/negative number, not because it allows you to perform an operation before having the numbers to use in the equation.

It is not like you would enter "=/4*3" and expect to get "0.75". Does Mathcad solve that one? Excel does not like it.

The real question is how you (and Excel/Mathcad)interpret "-5^2" in his original posts . Is it"-5"^2 (squaring an entity), or is there an implied multiplication going on "-1*5^2" (taking the negative value of a squared entity)? That is where the long debate started. I stand behind:
-5="Negative 5"
"Negative 5"^2=25
I do this because because he defined it as "Square of a Negative", and I assert that the rest of you did not read the problem correctly.

 

[zdas04]

OK

David

 

[IRstuff]

Actually, in Mathcad, you can achieve either result, because in Mathcad, a leading minus is treated as an operator on the number that follows, so that you can apply the exponent operator on just the number, or the number and the associated operator. If you do the latter, Mathcad clarifies the situation by automatically adding parentheses. So, in Mathcad, if you forget to start the parentheses, you can recover quite easily, and without much fuss.

This is particularly true in Mathcad because the edit cursor always underlines what is being operated on next. In the former case, the exponent operator only applies to the number. In the latter case, you have to purposely extend the edit cursor by hitting the space bar to ensure that the minus sign is included under the exponent.

As for "This is a good thing to know, but I'm still looking for a credit card calculator that knows that 1+5*7 = 36 not 42" that's why RPN was invented, and that's why I only use RPN calculators, when I have a choice. ;-) But Dave's lament points out the precise problem in which Excel is not alone. The Windows calculator will produce 42 or 36, depending on whether it's in "standard" mode or in "scientific" mode. So here is a single program that changes its behavior in this simple problem, simply by changing to what is only a more detailed keyboard and display. I'd try the exact Excel problem, but "standard" mode does not include square, only square root. VERY odd...


TTFN

FAQ731-376

 

[zdas04]

I bought a batch of credit card calculators (I think it was 100) when I first started my business from one of those advertising gimmick places. I opened the box, typed in the 1+5*7 equation and got 42--I closed the box and mailed it back because I'm not going to have my company name on something that stupid. I can use RPN, but I have to think about what I'm doing. I like my TI 30X IIS, it gives me 36 every time (it also says that -5^2+5^2=5^2-5^2)

David

 

[IRstuff]

Well, I was certainly surprised that the Windows Calculator would give out different answers, depending on whether you were a scientist, or not.

TTFN

FAQ731-376

 

[swearingen]

I contend that "negative 5" does not equal -5.
I contend that "negative 5" equals (-5). It is implying that you're wanting to square the whole thing.

Those semantics aside, I admit that my title for this post is glaringly incorrect, however, and I apologize.

This is as clear as I can put it, though:

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

You would never question the latter, why would you question the former?


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

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

 

[IDS]

Just having a play with this and discovered something I didn't know before:

=-5^2 = 25
=--5^2 = 25
=---5^2 = 25
and so on

It seems that Excel treats any signs immediately after the initial = as being attached to the number, so will always yeild a positive result if squared. It works as if there are virtual brackets immediatly after the = and between the number and the ^, so:
=--5^2 = =(--5)^2 = 25

It works the same way with numbers and cell addresses:
=-A1^2 = 25 if A1 contains 5 or - 5

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/