Excel calculation error: sometimes ignores unary minus

[Paul Dunmore]

Using Excel 2003 SP3 in XP Professional SP 3.

In cell A1, enter 2. In A2, enter =-A1^2; the result displays as 4 instead
of the correct -4. In A3, enter =0-A1^2; the result is -4, which is correct.
In A4, enter =SQRT(A2); the result is 2 rather than #NUM!, showing that the
error is in the cell value, not its display. In A5, enter =-A1; the result is
-2, showing that the error does not occur with every unary minus.

Similar errors arise in other contexts, such as EXP(-A1^2).

This is clearly a very serious calculation error, because serious people
rely on Excel's arithmetic, but I don't know if MS has fixed it in later
releases. Can anyone with Excel 2007 or the pre-release 2010 (and perhaps
other operating systems) confirm whether or not the error is still present?

 

[Jerry W. Lewis]

By design, Excel performs unary minus before exponentiation, as documented in
Excel's Help ("The order in which Excel performs operations in formulas" is
the topic in Excel 2003). Therefore in Excel =-A1^2 is interpreted as
=(-A1)^2, not as =-(A1^2), which you supposed. This is not the most common
operator precedence, but there are enough different implementations in
different languages that defensive programmers always use parentheses when in
doubt. My guess is that this design decision was done for compatibility with
earlier dominant spreadsheets such as VisiCalc or Lotus 123. Regardless, I
doubt that MS would change it now after more than 2 decades, since that might
cause compatibility issues with existing Excel applications.

Jerry

 

[Paul Dunmore]

Thank you, Jerry. I have been using spreadsheets since Visicalc and have
never been conscious of this definition. But it is documented exactly as
you describe. In algebra, -x^2 unambiguously means -(x^2), and I have checked
every other computational program I have available and they all follow the
algebraic convention. Even VBA does so! But every spreadsheet program defines
it as (-x)^2. Of course, the convention will not be changed now, and unwary
users (and even a few more wary programmers) will continue to get it wrong.
But thanks for sorting me out, at least.