bill@utastro.UUCP (William H. Jefferys) (07/28/89)
Nearly two years ago, I reported in this forum on a bug in Microsoft EXCEL. I also reported the bug directly to Microsoft, although they never responded to my letter. The version was 1.05. The bug was NOT fixed in 1.5, and I regret to say, it has apparently NOT been fixed in v. 2.2 either. (I thank Paul McJones [mcjones@src.dec.com] for verifying this, as I haven't got v. 2.2 yet). Here is the bug. It shows up on a Mac+ with no coprocessor. It does NOT show up on a Mac II. I don't know about the SE, but I expect it will show up there too (anyone want to try it?) Compute the square root of 0.999975. On a Plus, EXCEL gives the incorrect value 0.9999875. The correct value is 0.9999749992187, a difference of 1.6e-10. This may not sound like much, but for the application I was doing when I discovered this, it was highly significant. Normally, EXCEL gives 16 digit accuracy for the square root. The argument range over which reduced accuracy is obtained is [0.999975, 0.99999991]. ****FLAME ON**** It is inexcusable that Microsoft has had this bug reported to them nearly two years ago, has put out two major upgrades, and has STILL not fixed the problem. Although the amount of the error is small, in scientific calculations it can be quite significant, and could easily produce errors that by going undetected might do considerable damage. Microsoft, are you listening? PLEASE FIX THIS!!! ****FLAME OFF**** Bill Jefferys -- Glend. I can call spirits from the vasty deep. Hot. Why, so can I, or so can any man; But will they come when you do call for them? -- Henry IV Pt. I, III, i, 53
larryh@tekgvs.LABS.TEK.COM (Larry Hutchinson) (08/01/89)
In article <4134@utastro.UUCP> bill@utastro.UUCP (William H. Jefferys) writes: >Nearly two years ago, I reported in this forum on >a bug in Microsoft EXCEL. I also reported the bug . . >Here is the bug. It shows up on a Mac+ with no coprocessor. >It does NOT show up on a Mac II. I don't know about the SE, >but I expect it will show up there too (anyone want to >try it?) > >Compute the square root of 0.999975. On a Plus, EXCEL >gives the incorrect value 0.9999875. The correct value >is 0.9999749992187, a difference of 1.6e-10. This may I am not convinced that this is a bug at all. Directly using either the 68882 OR using SANE (which probably calls the 882) I get: sqrt(0.999975)= 0.999987499921874 as a check on the answer: 0.999987499921874 * 0.999987499921874= 0.9999749999999998 which is not too shabby. Using the value you state, I get 0.9999749992187 * 0.9999749992187= 0.9999499990624391 which is obviously far less accurate. This is on an SE/30. But if this (and your Mac+) is the correct answer then that implys your Mac II gave the wrong answer! Pretty wierd stuff. (My HP15C gives the same results to 10 digits.) Where did you get your value? How did you check it? Larry Hutchinson, Tektronix, Inc. PO Box 500, MS 50-383, Beaverton, OR 97077 UUCP: [uunet|ucbvax|decvax|hplabs]!tektronix!tekgvs!larryh ARPA: larryh%tekgvs.LABS.TEK.COM@RELAY.CS.NET CSNet: larryh@tekgvs.LABS.TEK.COM
jk3t+@andrew.cmu.edu (Jonathan King) (08/01/89)
Interesting. My calculator (a Sharp EL-512 II) gives virtually the same erroneous square root as Excel! That is, the square root of .999975 is given as .999987499. It looks like this problem is not unique to Excel; to make its way into the entirely silicon brain of my calculator this error must be the result of an ancient and honorable (although badly conditioned) numerics routine... jking
cleeland@rex.cs.tulane.edu (Chris Cleeland) (08/01/89)
In article <MYp9wBy00WB-8EBFJb@andrew.cmu.edu> jk3t+@andrew.cmu.edu (Jonathan King) writes: > >Interesting. My calculator (a Sharp EL-512 II) gives virtually the same >erroneous square root as Excel! That is, the square root of .999975 is >given as .999987499. It looks like this problem is not unique to Excel; >to make its way into the entirely silicon brain of my calculator this error >must be the result of an ancient and honorable (although badly conditioned) >numerics routine... > Funny, your Sharp and my HP must hail from a common genetic origin. My HP-28C give SQRT(.999975) as 0.999987499922; upon hitting the "x^2" key, my trusty calc gives the original number -- .999975. The 28C calculates things to 12 digits of accuracy, and only displays 10 (presumably so that all 10 are usable). I hope that my calculator isn't wrong, but then again, maybe I could blame some bad test grades on it ;-> Anybody else? -- Thanks Chris Cleeland, Tulane University ADDRESS: cleeland@rex.tulane.cs.edu Disclaimer: "I'm a student -- I can't afford to buy one!"
ags@mentor.cc.purdue.edu (Dave Seaman) (08/01/89)
First of all, a simple Taylor series expansion of f(x) = sqrt(x) about x=1 should show you that the square root of .999975 is .9999875 with an error on the order of 10^(-10). But since it was claimed that Excel 2.2 on a Mac II did not agree, I decided to check it out. If you display the result using "general" format (the default) in Excel 2.2, then you get .9999875 on a Mac II. However, if you change the numeric format to "0.00000000000000" and then widen the cell to make room, you will see 0.99998749992187. Since I had Mathematica handy, I tried it also and got 0.999987499921874023422240943904, which agrees to as many digits as Excel displayed. -- Dave Seaman ags@seaman.cc.purdue.edu
hiebert@mdivax1.uucp (Graeme Hiebert) (08/02/89)
In article <MYp9wBy00WB-8EBFJb@andrew.cmu.edu> jk3t+@andrew.cmu.edu (Jonathan King) writes: > > Interesting. My calculator (a Sharp EL-512 II) gives virtually the same > erroneous square root as Excel! That is, the square root of .999975 is > given as .999987499. It looks like this problem is not unique to Excel; > to make its way into the entirely silicon brain of my calculator this error > must be the result of an ancient and honorable (although badly conditioned) > numerics routine... > > jking Very interesting indeed. My Sun gives sqrt(0.9999750000000000000000000) = 0.9999874999218740234222409 and 0.9999749992187 * 0.9999749992187 = 0.9999499990624 Now, let's see, if I do the multiplication by hand, I get 0.9999749992187 0.9999749992187 --------------- 69998249945309 799979999374960 999974999218700 19999499984374000 899977499296830000 8999774992968300000 89997749929683000000 399989999687480000000 6999824994530900000000 89997749929683000000000 899977499296830000000000 8999774992968300000000000 89997749929683000000000000 -------------------------- 0.99994999906243906561042969 ^^^^^^^^^^^^^^^ Well look at that. My Sun multiplies the same way I do. Shame on us. -g -- "I didn't sleep well last night. This girl kept knocking on my hotel room door. After awhile, I had to get up and let her out." -Henny Youngman Graeme Hiebert (hiebert@mdivax1.uucp, ...{uunet,ubc-cs}!van-bc!mdivax1!hiebert)
bill@ut-emx.UUCP (Bill Jefferys) (08/03/89)
In article <5680@tekgvs.LABS.TEK.COM> larryh@tekgvs.LABS.TEK.COM (Larry Hutchinson) writes:
#
#I am not convinced that this is a bug at all.
#
#Directly using either the 68882 OR using SANE (which probably calls the 882)
#I get:
#
#sqrt(0.999975)=
# 0.999987499921874
#
#as a check on the answer:
#0.999987499921874 * 0.999987499921874=
# 0.9999749999999998
#which is not too shabby.
#
#Using the value you state, I get
#0.9999749992187 * 0.9999749992187=
# 0.9999499990624391
#which is obviously far less accurate.
#
#
#This is on an SE/30. But if this (and your Mac+) is the correct answer
#then that implys your Mac II gave the wrong answer! Pretty wierd stuff.
#(My HP15C gives the same results to 10 digits.)
#
#Where did you get your value? How did you check it?
Typing error. The Mac II gives 0.99998749992187. I dropped the 8
when copying the number.
An SE/30 of course has the coprocessor & can be expected to give
the correct answer. On the plus, you get 0.9999875 exactly. This
is the wrong answer, and the bug. YOU WILL NOT BE ABLE TO REPRODUCE
THIS BUG ON A MACHINE WITH A COPROCESSOR.
Bill Jefferysbill@ut-emx.UUCP (Bill Jefferys) (08/03/89)
In article <MYp9wBy00WB-8EBFJb@andrew.cmu.edu> jk3t+@andrew.cmu.edu (Jonathan King) writes:
#
#Interesting. My calculator (a Sharp EL-512 II) gives virtually the same
#erroneous square root as Excel! That is, the square root of .999975 is
#given as .999987499. It looks like this problem is not unique to Excel;
#to make its way into the entirely silicon brain of my calculator this error
#must be the result of an ancient and honorable (although badly conditioned)
#numerics routine...
The error only shows up after the 10th significant digit. You gave only 9.
0.99998749992187 == 0.999987499, to the significance shown.
Bill Jefferysbill@ut-emx.UUCP (Bill Jefferys) (08/03/89)
In article <3531@mentor.cc.purdue.edu> ags@mentor.cc.purdue.edu (Dave Seaman) writes:
#First of all, a simple Taylor series expansion of f(x) = sqrt(x) about x=1
#should show you that the square root of .999975 is .9999875 with an error on
#the order of 10^(-10). But since it was claimed that Excel 2.2 on a Mac II
#did not agree, I decided to check it out.
This is NOT what I claimed. I said that the result on the Mac II is
correct (when you widen the field to 16 digits). Another poster
showed that on the SE/30 the answer is correct. APPARENTLY THE BUG
ONLY SHOWS UP ON A MACHINE WITHOUT THE COPROCESSOR. IT IS DEFINITELY
THERE ON A MAC+.
Bill Jefferysags@mentor.cc.purdue.edu (Dave Seaman) (08/03/89)
In article <16497@ut-emx.UUCP> bill@emx.UUCP (Bill Jefferys) writes: >In article <3531@mentor.cc.purdue.edu> ags@mentor.cc.purdue.edu (Dave Seaman) writes: >#But since it was claimed that Excel 2.2 on a Mac II >#did not agree, I decided to check it out. >This is NOT what I claimed. I said that the result on the Mac II is >correct (when you widen the field to 16 digits). Another poster >showed that on the SE/30 the answer is correct. APPARENTLY THE BUG >ONLY SHOWS UP ON A MACHINE WITHOUT THE COPROCESSOR. IT IS DEFINITELY >THERE ON A MAC+. I didn't say who made the claim. It wasn't you. Because of your typing error (which I noticed only after posting previously), everyone was posting answers that were very close to the Mac+ result and considerably different from what you gave as the Mac II result, generally with sarcastic comments about how so many different calculators were all getting the same "wrong" answer. I have verified that the bug exists on a Mac Plus. Changing the format and widening the cell does not make the displayed result any more accurate, as it does on the II. -- Dave Seaman ags@seaman.cc.purdue.edu
bill@ut-emx.UUCP (Bill Jefferys) (08/10/89)
In article <1989Aug1.193850.4999@mdivax1.uucp> hiebert@mdivax1.uucp (Graeme Hiebert) writes: #In article <MYp9wBy00WB-8EBFJb@andrew.cmu.edu> jk3t+@andrew.cmu.edu (Jonathan King) writes: #> #> Interesting. My calculator (a Sharp EL-512 II) gives virtually the same #> erroneous square root as Excel! That is, the square root of .999975 is #> given as .999987499. It looks like this problem is not unique to Excel; #> to make its way into the entirely silicon brain of my calculator this error #> must be the result of an ancient and honorable (although badly conditioned) #> numerics routine... #> #> jking # #Very interesting indeed. My Sun gives # sqrt(0.9999750000000000000000000) = 0.9999874999218740234222409 #and # 0.9999749992187 * 0.9999749992187 = 0.9999499990624 ^[insert 8 here] ^[insert 8 here] #Now, let's see, if I do the multiplication by hand, I get [long, irrelevant calculation omitted] # 0.99994999906243906561042969 # ^^^^^^^^^^^^^^^ # Well look at that. My Sun multiplies the same way I do. Shame on us. Shame on me. I mistyped the number Excel gave, leaving out the "8". There IS bug in Excel, however. I apologize to everyone on the net for my typo, which has caused great confusion. I have received mail from Microsoft, acknowledging the bug, and saying that they have located and fixed it. I hope the the corrected version will be made available for those with older machines soon. Also, I want to thank all those who helped to attract Microsoft's attention to my plea. Bill Jefferys
roberts@sunray.UUCP (Robert Stanley) (08/10/89)
In article <1989Aug1.193850.4999@mdivax1.uucp> hiebert@mdivax1.uucp (Graeme Hiebert) writes: >In article <MYp9wBy00WB-8EBFJb@andrew.cmu.edu> jk3t+@andrew.cmu.edu > (Jonathan King) writes: >> >> Interesting. My calculator (a Sharp EL-512 II) gives virtually the same >> erroneous square root as Excel! That is, the square root of .999975 is >> given as .999987499. It looks like this problem is not unique to Excel; >> to make its way into the entirely silicon brain of my calculator this error >> must be the result of an ancient and honorable (although badly conditioned) >> numerics routine... >> >> jking > >Very interesting indeed. My Sun gives > sqrt(0.9999750000000000000000000) = 0.9999874999218740234222409 >and > 0.9999749992187 * 0.9999749992187 = 0.9999499990624 > > [hand calculation of *same* equation to get same result omitted] > > Well look at that. My Sun multiplies the same way I do. Shame on us. And this demonstrates what? The sqrt function yielded zero point four- nines eight seven four three-nines two one eight seven... All the rest of the posting talks about zero point four-nines seven four three-nines two one eight seven - what happened to the eight between the four-nines and the seven? No wonder the result of the multiplication is nowhere close to the number for which the square root was originally calculated. I'm already having a bad day, as you can tell. Microsoft have already released the information that Excel has a proprietary set of floating-point routines, and does not use SANE. Over the past two years we have had at least three public (in comp.sys.mac) demonstrations that Microsoft's numeric routines are both incorrect and inconsistent in certain limit cases. Of much more interest would be a detailed comparison of these limit cases across a range of floating-point routines, e.g., SANE, 68881, 68882, Microsoft. More importantly, because we tend to take underlying routines of this kind as gospel truths, we (as users) really need accurate documentation from the vendor as to where the truth stops and the fuzzies start creeping in. However, as has been pointed out many times before, by far the majority of users never find themselves outside the limits. What I have problems with is the implicit assumption on the part of the vendors that anyone who *does* step outside the limits will automatically be smart enough (well-trained enough? cynical enough?) to first identify and then correctly attribute the problem. Microsoft: the Apple guidelines to developers clearly state the philosophy behind SANE. You have unilaterally side-stepped those guide- lines. You owe it to all Mac users to tell them that you have done this, and where the differences will show up. If you don't know (implies don't care), then you should probably add SANE support as a user-selectable option, and let the user make the performance/accuracy trade-off at his or her *informed* discretion. Either way, it NEEDS DOCUMENTATION. From the instruction label on the back of my very first Japanese hand-held calculator (circa 1972): "Flashing display does not indicate battery low; battery low is indicated by wrong answer."!!! No, while I still have it, it no longer works at all, and it didn't have a sqrt function anyway. Have you ever considered what it would actually take to 100% verify that a full 32-bit multiply hardware instruction is functioning correctly? Robert_S -- Robert Stanley - Cognos Incorporated: 3755 Riverside Drive, P.O. Box 9707, Compuserve: 76174,3024 Ottawa, Ontario K1G 3Z4, CANADA uucp: uunet!mitel!sce!cognos!roberts Voice: (613)738-1338 x6115 arpa/internet: roberts%cognos.uucp@uunet.uu.net FAX: (613)738-0002
mcdonald@uxe.cso.uiuc.edu (09/02/89)
>Funny, your Sharp and my HP must hail from a common genetic origin. My >HP-28C give SQRT(.999975) as 0.999987499922; upon hitting the "x^2" key, >Anybody else? Sure, I'll bite. This is by hand, using the power series: sqrt(1-.000025) = 1 -.0000125 -7.8125*10-11 -9.765625*10-16 -1.52587890625*10-20 -2.670288...*10-25 = .999997499218740234222409.... which is subject only to errors in my hand addition, not algorithm bugs. P.S. I don't do hand addition very often - CAVEAT EMPTOR!