Arithmetic Is Hard–To Get Right
Yesterday, a nasty little bug was identified in Excel 2007. Try multiplying 850 * 77.1 and Excel returns 100,000. Well the answer should be 65,535! Any calculator should get this right, so why not Excel.
AppScout had the following explanation:
It all boils down to the fact that you can’t represent an infinite group of non-integer numbers using a finite number of bits. In fact, Excel can store “only” about 9 quintillion distinct values. The numbers going into your calculations may be infinitesimally different from the number displayed, and for two calculations that nominally have the same answer the result may be infinitesimally different. Excel generally manages just fine in dealing with these tiny differences, but in exactly 12 instances out of the 9 quintillion possibilities it goes completely bonkers.
Wolfram research has an interesting blog entry explaining the potential causes for arithmetic errors and he discusses the impacts.
These days reliability is an increasingly important component of numerical computation. Machines have become so fast that people are doing huge numbers of numerical computations all the time. And now what’s critical is to get them right all the time–because if there’s a fringe case that’s wrong, it’s now going to be noticed.
Enjoy and double check those Excel sheets!