bill@utastro.UUCP (William H. Jefferys) (10/14/87)
I have run across a bug in the Microsoft Excel square root function.
I am using Microsoft Excel v. 1.04 on a Mac+. When taking square roots
of numbers in the range [0.999975,0.99999991] I find that there is
a significant error. The error is discontinuous at the left end: if the
argument is only 0.999964, the error drops suddenly to zero.
To illustrate this I have made up a table with the following columns:
In the first column, x. In the second column, u=1-x*x; in the third
column, y=sqrt(u), calculated with the Excel square root function. In the
fourth column, x*x+y*y-1 (should be zero). The calculation was done in Full
Precision mode.
In the fifth column I performed a single Newton-Raphson iteration
starting with y, to get a better approximation y' to the square root.
The sixth column is the same as the fourth column, but calculated
using the result of the Newton-Raphson iteration. The error is much
smaller.
x u=1-x*x y=sqrt(u) x*x+y*y-1 y'=(y+u/y)/2 x*x+y'*y'-1
0.0001 0.99999999 0.999999995 0 0.999999995 0
0.0003 0.99999991 0.999999955 1.9984E-15 0.999999955 -1.11E-16
0.0005 0.99999975 0.999999875 1.5543E-14 0.99999987499999 -1.11E-16
0.0008 0.99999936 0.99999968 1.0258E-13 0.99999967999995 2.22E-16
0.001 0.999999 0.9999995 2.5002E-13 0.99999949999987 -1.11E-16
0.002 0.999996 0.999998 4.0001E-12 0.999997999998 0
0.003 0.999991 0.9999955 2.025E-11 0.99999549998987 0
0.004 0.999984 0.999992 6.4002E-11 0.999991999968 0
0.005 0.999975 0.9999875 1.5625E-10 0.99998749992187 0
0.006 0.999964 0.999981999838 0 0.999981999838 0
0.007 0.999951 0.99997549969987 0 0.99997549969987 0
This seems to be a Microsoft bug and not a ROM bug, because the same
calculation done in Lightspeed C (which uses SANE) does not produce this
error. Microsoft apparently has their own private square root routine
in EXCEL, and didn't use SANE. Too bad.
Until this is fixed in (I hope) Version 1.05 (*please*, Microsoft!!!),
I recommend using the following macro function SQRTF instead of SQRT:
function SQRTF
=RESULT(1)
=ARGUMENT("in",1)
=SET.NAME("out",SQRT(in))
=IF(in<>0,SET.NAME("out",0.5*(out+in/out)))
=RETURN(out)
If anyone is interested I can send them the Binhexed spreadsheet. It's
about 4K long.
Bill Jefferys