Excel: Negation Before Exponentiation

[jer0608]

I recently noticed something potentially unpleasant about the way MS Excel handles mathematical order-of-operations. It seems that Excel places "negation" higher than exponentiation in its order-of-operations scheme. Typically, when constructing a spreadsheet, "negation" is simply an implicit subtraction from zero (or multiplication by minus one, if you prefer) i.e. -3 instead of 0-3 (or -1*3). As you may recall from grade school, addition/multiplication is done AFTER exponentiation. For a quick example of the problem, type the following two formulas into Excel:

-(2*2)^2
-((2*2)^2)

These two formulas are functionally equivalent to 0-(2*2)^2 and should return -16. However, Excel handles the negation sign prior to the exponent, interpreting the top formula as (0-(2*2))^2 = 16. The bottom format is required for this code to execute correctly.

The engineers I work with (I'm a Mech. E) were horrified when I showed this to them, as we do a lot of spreadsheet models and no one was aware of this.

Now, according to Microsoft's Knowledge Base, this was carried over from the initial release and was maintained for version consistency:
Link

Does anyone know why Microsoft did it this way? The only precedent I can find is the use of negation as a logical device (i.e. Not) in software. In that case, it could be prioritized above a binary multiply or add. I suppose if you then viewed exponentiation as a function consisting of multiple multiplications, you might come up with MS' order of ops. However, I can't, for the life of me, come up with a situation where this would be the correct interpretation, and certainly not in a typical spreadsheet.

Sorry for being longwinded, but if anyone can shed some light on this, I am quite curious.

Cliffs:

1) MS Excel does "negation" before exponentiation
2) Why?

 

[KB]

Why? Microsoft never fixes its bugs. It leaves them there for "consistency". I would interested to know if OpenOffice Calc has this problem.

 

[jer0608]

That was my initial interpretation after reading the "Knowledge" Base. Thought I'd give them the benefit of the doubt and see if anyone at AT knew of a legitimate reason they may have done this in the first version.

 

[MrChad]

Originally posted by: KB
Why? Microsoft never fixes its bugs. It leaves them there for "consistency". I would interested to know if OpenOffice Calc has this problem.

There are a lot of Excel spreadsheets out there, and many may rely on this quirk in order to calculate values correctly. If Microsoft changed the behavior, it could have a massive negative impact on the user community.

As an alternative, they've documented the issue and provided legitimate workarounds.

 

[jer0608]

I understand the idea of backwards compatibility and agree that MS would need to think long and hard before tampering with it. The problem is, users will most likely find out about this issue only after they have encountered a blatant demonstration of the problem. Most people picking up a new piece of computational software are going to assume that the product correctly handles the mathematical fundamentals. Microsoft should make their user base clearly aware that Excel flies in the face of convention BEFORE someone needs to debug a fifty page workbook (or worse, doesn't know there's a problem at all).

This is a SERIOUS issue. Not ONE of the twenty plus engineers I sent this to knew of this quirk. These are engineers with years of experience conducting analysis in Excel. I don't think I have to spell out the danger here.

Anyway, I found some further information that at least has some hypotheses on why they did it this way in the first place:

Link