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

 

[IDS]

Exactly what evaluations are changed by "Transition formula evaluation" I'm not sure.

Neither are Microsoft it seems.

If you go to Options-Advanced and click on the help icon it goes to the general help screen. If you type "transition" in the search box it tells you there is no help on transition.

If you search for help on Lotus you get a couple of irelevant responses, including some warning about phishing.


Hopeless.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

LOL. MS Excel help has not been very good to me. I usually go to google first. They are so good at copying/stealing others' good ideas... why don't they steal a good working help interface.

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

 

[swearingen]

Just curious about you guys on the other side of the fence:

Say you wanted to find the roots of

-x^2 - 6x + 27 = 0

and you wanted to use the Quadratic Equation to do so.

What would you use for A?


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

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

 

[IRstuff]

I think you are confusing math with parsing. Everything comes with conventions about symbology and order of operations. What we accept on a written or printed sheet of "paper" does not necessarily mean that we will accept or live with the same thing in a serial, blind, real-time parsing situation. If Excel were Mathcad, then I would expect that Excel accepts the first term as having a value less than zero. But it's not, so I have no expectations, and I not convinced that I should, short of something that substantially increases the drudgery of entering equations. Since I find that Mathcad is substantially more useful for doing this type of problem, I see no reason to worry about this.

This is fundamentally no different than the order of operands for atan2 functions, or the now OBE argument about "algebraic" vs RPN inputs. Excel and Mathcad had different atan2 conventions for which is the numerator, and which is the denominator. Is there really a "right" answer. No, because it's by convention only. The fact that Excel's parsing for leading minus signs dates back more than 30 years pretty much convinces me that arguing the point is futile and pedantic.

If you've already made your case to MS and unless you plan on creating a spreadsheet program with only "correct" conventions, then what is the point, other than to while away a bunch of hours arguing with a bunch of faceless entities on the Internet?

TTFN

FAQ731-376

 

[davidbeach]

Of course A=-1. Once again it is a matter of parsing. The quadratic equation has a given form and is parses explicitly. Excel is parse implicitly and has to process the unary operators ahead of the binary operators. Maybe you should just stop using excel.

There is much about Microsoft to complain about, consistent parsing in excel isn't one of them.

 

[swearingen]

IRstuff, the purpose of the first two posts and of this one was to inform folks that may not know, that MS does not use the convention that is used in the mathematics we learned in school. That's all. I wanted to stress the use of parentheses to force your equations to come out as you would expect them to in MathCAD, Mathematica, or on paper.

The problem arose when there was so much push-back on the convention. I have yet to have any "faceless entities" come up with a textbook example of something to refute the convention that I have proffered, although several said that I should check my school books (which I did). You have to admit,though, it's been an interesting ride...

davidbeach, here we go about parsing and unaries again. Well, the roots are -9 and 3 (you don't have to use the quadratic equation if you don't want to). Oooh - but the roots suddenly can't be -9 and 3 because of you plug those back in, you won't get 0, you'll get 162 or 18! Could you please tell me what the actual roots are then?


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

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

 

[stevenal]

Davidbeach,

Perhaps you run a different Mcad version. I run 12.1, and I see no way that it explicitly shows its parsing; it just returns an answer.

 

[davidbeach]

-(-9^2)-6*(-9)+27=0
-(3^2)-6*(3)+27=0

I've no idea where your 162 or 18 come from.

Is it so difficult to understand that how you enter an equation depends on where you are entering it.

Unary negation and binary subtraction use the same character to do two different and distinct things. That's probably a bad choice, but it's too well established to change at this point.

Explain, then why excel should parse
-5^2
differently from
5^-2

In both cases there is a unary negation. In one case it is clearly impossible to perform the exponentiation before the negation, so the negation has to happen first. But you want a different rule for the other one, do the exponentiation and then do the negation? That would be hopelessly more confusing than the present situation.

With implicit parsing it is much better to have complete consistency in how the equation is parsed (even if you find the parsing rules less than optimal) than to situational parsing rules (i before e except after c or...).

In your original problem you are welcome to enter it as
=0-5^2+5^2
Excel will then treat the '-' as a binary subtraction and you can then rearrange to
=0+5^2-5^2
and get the same result. You could even enter the original as
=-(5^2)+5^2
and force excel to parse the equation the way you want. That can then be rearranged to
=5^2-(5^2)
and again you get the same result.

You just can't tell excel to square -5 and expect to get -25, but I still don't understand why you want the square of -5 to be -25 anyway.

 

[swearingen]

The 162 and 18 come from taking --9^2 to be 81 and -3^2 to be 9, as Excel does - note I didn't put parentheses here as I also did not in the orignal problem. You added them later...

The simple answer to your parsing of -5^2 vs 5^-2 is that you have written it incorrectly. Any expression as an exponent should be written with parenthesis: 5^(-2). That expression is to be evaluated first, then the exponentiation takes place. This is another of those bits that was taught in school that you may have also forgotten.

I'm going to go ahead and throw in the towel. I stand by the order of operations that mathematicians are using world wide, as supported by texts and hard-core math programs (of which Excel is surely not). If anyone comes across a math text that refutes it, please let me know...


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

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

 

[IDS]

Just curious about you guys on the other side of the fence:Say you wanted to find the roots of-x^2 - 6x + 27 = 0and you wanted to use the Quadratic Equation to do so.What would you use for A?

What "other side of the fence"? As far as I can see everyone is agreed that it's a good idea to use brackets when exponentiating with negative numbers.

As for the question, A is -1 of course, and my version of Excel gives -1*5^2 = -25.

I don't really care whether Excel is a "hard core math program" or not. I'll continue to use it for everything other than solving large FEA problems, and continue to get my work checked by someone else before going to construction, just like I would if I was using any other calculation tool.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

S,

I think the issue is that it's already been hashed out twice, and obviously, while people claim that they're tired of discussing it, they just can't help themselves, thereby compounding the problem.

So the furor is not that you're saying anything wrong, it's just that it's been said already, and we don't want someone to present yet another opportunity to waste a perfectly good hour rehashing old stuff because we can't help ourselves.

There's a tolerably OK search engine that can find the previous discussions, so there's really no need to *bump* the discussion. If you're really that passionate about the subject, a FAQ entry would be the most logical thing to do, not that those people reads the FAQs, either...

TTFN

FAQ731-376

 

[stevenal]

If -x^2-6X+27 is taken to mean (-x)^2-6X+27 as in Excel, the expression is not yet in a proper form for using the quadratic formula. Since (-x)^2 = x^2, the expression can be simplified to X^2-6X+27, and A=1. At least swearingen chooses his side of the fence consistently.

 

[TDAA]

OK, these guys agree with swearingen:

http://www.themathpage.com/alg/exponents.htm

However, my only argument was based on the words of the thread not matching what he intended to say.

The argument has been "Square of a Negative" vs "Negative of a Square", which is what swearingen should have said for me not to have responded.

 

[IRstuff]

Stevenal,

When in Mathcad, if you look at the unlining that occurs during the entry of -5, you:

> start with a red cross cursor
> enter "-"
> you get a blue vertical line with a placeholder square that is underlined. The underlining tells you what Mathcad considers to be a mathematical entity at that point
> enter "5" into the placeholder
> the vertical bar moves to the right of the 5, but the underline still only encompasses the "5"
> if you hit "^" at that point, the exponent applies only to the 5
> if you hit spacebar once, the underline extends underneath the minus sign. if you now hit "6" the exponentiation occurs, but parentheses are inserted to include the entire "-5"

TTFN

FAQ731-376