| 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.
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
Subscribe and get our monthly bonuses - free!
Your hard drives store photos, books, music and film libraries, letters, financial documents and so on. This ebook is aimed at helping you understand your hard drives, expand their capacities and length of life, and recover what you can from them when they fail. We're offering you a FREE Excerpt! Get this excerpt and other 4 bonuses if you subscribe FREE now!
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 the Excel add-ins, see Baier and Neuwirth’s R(D)COM page and the RExcel installation instructions.
Some Excel 2007 bugs also affect Excel 2003
I mentioned last week that Excel 2007′s problem with 65,535 was reported to also affect Excel 2003, but that Microsoft wasn’t being clear about this. Reader Charlie Woodall explains that this particular bug is present in Excel 2003 only if Microsoft’s “Save as Excel 2007″ converter has been installed:
- “The Excel bug [involving 65,535 in Excel 2007] does not affect the older versions of Excel. However, for those who have applied the Office 2007 Compatibility Pack to older versions of Office, there are two files that are affected by the bug. The two affected files, excelcnv.exe and oartconv.dll, must be replaced by build 12.0.6042.5000. These files are part of the Office 2007 Compatibility pack. The bottom line is that the Excel bug affects the file format converter and not the older Excel versions.”
• The dec2hex( ) function gives wrong answers (see comments 5178648 and 5205563);
• Calculations involving a result of 0.1 are slightly off (5192535);
• The trunc( ) function changes the value of calculations that are already truncated (5372326).
Woodall confirms these problems and clarifies what Microsoft’s October 2007 hotfix does and does not cure:
- “You are correct that the bugs addressed in the 4 links affect Excel 2003. They also affect Excel 2007. The hotfix that was issued the other day did not fix these bugs in either Excel 2003 or Excel 2007. The hotfix seems to have fixed the [65,535] calculation bug that affected only Excel 2007 (and the file format converter). In effect, the hotfix makes Excel 2007 calculations agree with Excel 2003 calculations [involving 65,535]. …
“Microsoft should definitely fix the problems with the dec2hex( ) and dec2oct( ) functions. However, the error 4.1 – 4 = 0.0999999999999996 [which should result in 0.1] is due to the inaccuracy in floating point calculations and is probably not considered a bug by Microsoft.”
For his part, RExcel developer Neuwirth has his own dirt on the Redmond spreadsheet app:
- “My main concern with Excel is its inconsistent handling of rounding: int(1000–2^–41) and quotient(1000–2^–41,1) never should produce different values, and that is what Excel does. Int(1000–2^–41,1) also never should be negative, but Excel gives a negative result. These are more than floating point issues, this is a logical problem.”
| UPDATE 2007-10-19: Erich Neuwirth e-mailed us to say that his comment, “Int(1000–2^–41,1) also never should be negative…”, should have said, “Mod(1000–2^–41,1) also never should be negative…”.|
What to do: I recommend that you install the hotfix to Excel 2007 and to Excel 2003′s “Save as Excel 2007″ converter, but you shouldn’t expect this to solve every Excel calculation error. Just because a number comes out of a computer — or a program comes out of Redmond — it ain’t necessarily reliable.
Readers Neuwirth and Woodall will each receive a gift certificate for a book, CD, or DVD of their choice for sending comments we printed. Send us your tips via the Windows Secrets contact page.
The Known Issues column brings you readers’ comments on our recent articles. Brian Livingston is editorial director of WindowsSecrets.com.