Just saw this in the Windows Secret blog:
Get official and unofficial fixes for Excel
By Brian Livingston
Despite the hotfix that Microsoft recently released for Excel 2007, as
I described on Oct. 11, some math errors that you should know about
still lurk in both Excel 2007 and Excel 2003.
I'll bring you up to date and explain how you can get better results from
Excel.
Baier and Neuwirth offer Excel math add-ins
In a nutshell, this month's patch for Excel 2007 corrects a bug that
treats numbers close to 65,535 as if they were 100,000. To get the
fix, see the Oct. 9 entry in Microsoft's official Excel blog.
Even with the hotfix, however, both Excel 2007 and Excel 2003 give
slightly wrong ? and, in some cases, extremely wrong ? answers to some
floating-point calculations. I'll give you some examples below. First,
let's discuss an independent solution to the problem.
Those who want more accurate floating-point math than any version of
Excel supports should download a statistics program called R. This is
open-source software that was originally written by Robert Gentleman
and Ross Ihaka ("R & R"), who now work with about 20 researchers
around the world to maintain the code.
The R program, in turn, can be used with Excel if you install various
add-ins by Thomas Baier and Erich Neuwirth called RExcel, rcom, and
R(D)COM. Windows Secrets contributing editor Woody Leonhard
recommended this in his Oct. 4 column on the Excel problem.
In last week's article, I rounded off R(D)COM to R, which resulted in
me mistakenly saying R was authored by Baier and Neuwirth. Ouch! This
floating-point stuff really is hard!
Erich Neuwirth kindly e-mailed me the following explanation:
"Thomas Baier wrote rcom and R(D)COM, both of which allow you to use R
as an embedded library in any Windows program supporting the COM
(Component Object Model, not the serial port) interface. I wrote
RExcel, which embeds R into Excel and allows you to use R functions as
if they were native Excel worksheet functions.
"So, yes, R can be used as a floating point library for Excel, but it
is much, much more. Most computational statistics research nowadays is
done using R."
For more information about R, or to download it free from
R-Project.org, visit the R-Project site.
For more information about the Excel add-ins, see Baier and Neuwirth's
R(D)COM page and the RExcel installation instructions.
--
Jim Holtman
Cincinnati, OH
+1 513 646 9390
What is the problem you are trying to solve?
