Floating Point Challenge
April 1999 Challenge: This month, I return to a more subjective question. In the figure at left, I entered 2045.2 in cell A1. I entered 2036.1 in cell A2. In cell A3, I entered =A1-A2. Rather than the correct result of 9.1, Excel returns 9.10000000000014.
OK, we all know this is because computers are binary creatures, and there isn't a great way to deal with tenths in the binary world. No, it is not a big deal, but to quote our correspondent Fred, "While the tenth or fourteenth decimal place won't bother my checking balance, it might mean I miss Mars with my Space Shuttle."
So, for those of you who *do* have to hit Mars with your space shuttle, how do you deal with this inaccuracy problem in Excel? Do you go through the hassle of rounding every result off to the nearest millionth? Share your methods.
Thanks to Brad Smith and Russell Goldsworthy who suggested to do the work in whole numbers. In this example, multiply both figures by 10, do the subtraction, then multiply everything by 0.1.