10.13 Why is =-1^2 positive and =0-1^2 negative? (spreadsheets: Excel)
Description
This article is from the Spreadsheets FAQ, by Russell Schulz casfaq@locutus.ofB.ORG with numerous contributions by others.
10.13 Why is =-1^2 positive and =0-1^2 negative? (spreadsheets: Excel)
nonstandard operator precedence -- the `unary minus' has a high
precedence, as normal, but the `exponentiation operator' has a
higher one, which is NOT normal.
see http://support.microsoft.com/support/kb/articles/q132/6/86.asp
see ftp://ftp.microsoft.com/deskapps/excel/kb/q132/6/
Excel treats -1^2 as (-1)^2, while most languages would as -(1^2).
(you can ALWAYS use parentheses to force either interpretation.)
note that Excel formulas and VBA formulas disagree on the order.
I had thought it might have been there for Lotus 1-2-3
compatibility; however, the Microsoft Excel Knowledge Base mentions
this incompatibility explicitly:
see http://support.microsoft.com/support/kb/articles/q61/9/41.asp
see ftp://ftp.microsoft.com/deskapps/excel/kb/q61/9/
Results of -1^2 (or -1**2)
All known versions of Excel:
1 Excel =FORMULA
Bill Clark tested it and reported:
-1 Excel 5 VBA
-1 DOS Quattro Pro V5
-1 Lotus 123r5w
-1 Qbasic
-1 GNU G77 Fortran (OS/2)
1 dBase 4
1 Base32 (OS/2 Dbase clone)
John Whittington reported:
-1 SAS
-1 Minitab
-1 MS BASIC
1 MS Word for Windows Table Formula
The comp.lang.perl.misc FAQ mentions:
-1 Perl
Anthony Berglas pointed out an odd problem with precedence between
floating-point division (/) and integer division (\) in VBA:
> ? 60 \ 5 * 2
> 6
> ? 60 / 5 * 2
> 24
Continue to:
My Books
