Error in BesselJ function from Analysis TookPak?

[Richard A. Fowell]

The BesselJ function seems to calculate to less than full accuracy -
as little as 8 digits of accuracy vs. the expected 15 digits. I see
this both in Excel on my Mac and on a PC at work. This function should
produce 15 digit accuracy, as Matlab and Mathematica do.

For example, BesselJ(1.8,0) returns this value on my Macintosh Ibook
G4 ( 933 Mhz,
Mac OS version 10.3.9 running Microsoft Excel 2004 for Mac Version
11.3.7(070724):

BesselJ(1.8,0) = 0.33998 64129 60524 000 (Excel) vs.
BesselJ(1.8,0) = 0.33998 64110 42558 350 (MIT tables)

The latter value is from a handbook of tabulated Bessel functions of
18 digit
accuracy produced by MIT, and both Matlab and Mathematica agree with
the MIT
value to 15 digits of precision (which is all I would expect from
standard
IEEE floating point arithmetic - for more I would use an extended
precision library
such as the Xnumbers plugin for Excel:

http://digilander.libero.it/foxes/index.htm

Is this discrepancy universal with the Analysis ToolPak addin version
of BesselJ?
Or do some versions do better?
Are there other handy resources for an accurate BesselJ for Excel or
on the Web?

Richard A. Fowell

 

[JE McGimpsey]

The BesselJ function seems to calculate to less than full accuracy -
as little as 8 digits of accuracy vs. the expected 15 digits. I see
this both in Excel on my Mac and on a PC at work. This function should
produce 15 digit accuracy, as Matlab and Mathematica do.

First, I should say I'm no expert on Bessel functions...

I know that the ATP's implementation of the Gamma function is less than
ideal, accurate to perhaps 9 digits (if you google, you can find VBA
routines by David Braden that provide significantly better accuracy).
Since the BesselJ function uses the Gamma function, I don't see how it
could be *more* accurate.

Prior to WinXL07, the ATP add-in, while included with XL, was written by
a third party and licensed by MS, so I would expect that all MacXL and
pre-XL07 WInXL versions would produce the same results. I don't know
whether the routines were re-written for XL07, but they're now
integrated into the application.

FWIW, it's important to separate accuracy from precision. The IEEE
double precision floating point arithmetic that XL uses provides
approximately 15 decimal digits of precision. Accuracy, on the other
hand, depends on the exact implementation of numerical methods. For
instance, iterative functions can usually be expected to be less
accurate than 15 decimal digits because of the accumulation of rounding
errors.