Excel's precision problem

[chenj]

Now I found Excel' precision is not very hign !
May be there is some problem in some Excel funcion's
implemention.ex:
in Excel :gammaln(4)=1.79175946908210000
but it is 1.791759469228055 in MATLAB and Mathematica !!
Same problem in function erf(),and so on!

Thank you !!!

 

[Paul]

Now I found Excel' precision is not very hign !
May be there is some problem in some Excel funcion's
implemention.ex:
in Excel :gammaln(4)=1.79175946908210000
but it is 1.791759469228055 in MATLAB and Mathematica !!
Same problem in function erf(),and so on!

Thank you !!!

Yes, this may well be true for some of the scientific functions. Excel is
basically a business tool. Adding the scientific functions was probably
something of an afterthought, and precision not the uppermost priority. If
you need more than 10 significant figures (or thereabouts) of precision,
it's probably best to use some other program.

 

[Jerry W. Lewis]

There are several things here worthy of comment

The problem with gammaln is the algorithm, not the math, Excel
calculates the mathematically equivalent =LN(FACT(3)) to be
1.79175946922805 which is still not exact, but is more accurate than
what you report from MATLAB and Mathematica. The correct value is
1.791759469228055001 to 19 figures.

You report 18 figures from MATLAB and Mathematica, but IEEE double
precision only resolves 15 decimal figures reliably, hence Excel's
refusal to display more. I believe that MATLAB and Mathematica also use
IEEE double precision unless you specifically ask for extended precision.

If I want n digit accuracy in a package that supports extended
precision, I generally request n+k digits and round the result, because
even specialized extended precision math packages tend to be optimistic
about their accuracy. My "correct" value was calculated to 50 figures
and then rounded to 19 in Maple 7.

Jerry

 

[Jerry W. Lewis]

Basic math functions like +-*/, ln, exp, and trig functions are
programmed in the math coprocessor, and and are no more or less accurate
than any other IEEE double precision package (almost all general purpose
software). Erf, gammaln, probability and statistics functions are not
from the coprocessor, and use algorithms that seriously limit their
precision. Excel 2003 greatly improved its statistics functions but did
not go far enough in improvements to probability functions.

Jerry

 

[Harlan Grove]

Basic math functions like +-*/, ln, exp, and trig functions are
programmed in the math coprocessor, . . .

Oh, ye of undue faith!

These 'functions' are programmed in hardware, but you're assuming Excel makes
use of these without, er, filtering the operands and/or results. Given the fact
that Excel's MOD function fubars when quotients exceed 2^27, well within IEEE
and Intel FPU's FPREM1 op specs, I'm not convinced Excel doesn't provide some
sort of absolutely pointless and (at least for MOD) deleterious intermediate
steps between the FPU and the worksheet results for all the simple arithemetic
operations. The best way to approach a product like Excel is always to assume
that its programmers never passed up an oppotunity to complicate and screw up
even the simplest operations.