IP: Math bug in Microsoft Excel

[David Farber <farber () central cis upenn edu>]

Subject: Math bug in Microsoft Excel
Date: Thu, 14 Sep 1995 23:35:38 LOCAL
Organization: Arizona Daily Star - AZSTARNET
Lines: 60
Message-ID: <lindsay.3.001A8387 () azstarnet com>
NNTP-Posting-Host: sprite20.azstarnet.com
Summary: A certain 15 digit number is interpreted in
Microsoft Excel with a very large error.


                              Stupid Excel Tricks
 -----------------------------------------------------------
-------------------------
Go to any cell in Excel.
Type in 1.40737488355328 DO NOT PRESS ENTER
Examine that input carefully.
Click your heels together three times while saying the magic word:
     "Microsoft".
Now press enter. Examine the result.


Type in =1.40737488355328
Press enter.
Examine the result.


 This happens on Excel 5 on a Mac or an IBM, also Excel 7 for
Windows 95.  A different wrong value is observed on a Power PC.


This was discovered by Stuart Worley of Hughes Aircraft Company, Tucson
AZ when he input the following formula in a large spreadsheet.(9/11/95):
+INT((2^47)/(10^INT(LOG(2^47))))
The result "0" instead of "1" stood out of the middle of the spreadsheet. This
prompted him to investigate further.


As far as we know, INT is the only function that misbehaves with this formula
output. This misbehavior may not be evident on a Power PC.  There, the magic
number is converted to 1.28 , not 0.64.  INT will round this down to 1,
just as if it were the correct value.  This problem has
just been reported to Microsoft.


The following is copied directly from Excel:
1.40737488355328 is the magic number.


Type the magic number (above) then enter:
0.64000000000000000000000000


Type the equals key, then the magic number and enter:
1.40737488355328000000000000


The magic number / 2 =
0.70368744177664000000000000


+1 + 0.40737488355328=
1.40737488355328000000000000


For Excel Power users:


When you "paste special" the "value" function of above cell:
0.64000000000000000000000000


The "value" of the text version of the magic number:
1.40737488355328000000000000


You may say "but this is a 15 digit number.  People hardly ever use
15 digit numbers."  But remember, the problem that originally caught
 this bug had numbers no bigger than 2 digits!


I think I will ask my boss for a pay cut by the factor 1.40737488355328.


Can someone write an Excel macro that checks a large number of numbers
looking for other magic numbers?  The question is:  how do you know the
macro will not lie to you?