Bugged by Excel’s calculation errors

Q: I found an anomaly with a rather simple Excel computation; specifically, Excel calculates 111,111,111 times 111,111,111 to equal 12,345,678,987,654,300, which is incorrect (the correct answer should end in “21,” not “00”). If Excel’s arithmetic fails, then what else is false, and why does this happen?

(Author’s note: You must increase the column width size to view the full number mentioned by the reader; otherwise Excel displays the number in exponential format (1.23457E+16), which does not allow you to see the calculation problem. If Excel continues to display the number in exponential format after you increase the column width, then right-click on the cell, select Format Cells, and in the pop-up box, select Number under the Number tab.)

A: You don’t need arithmetic to expose this problem; you can’t even type 12345678987654321 into Excel. When you try, Excel displays 12345678987654300. This is not an anomaly. Excel is designed to handle only 15 total digits in a given number (including digits after the decimal place when applicable). Therefore, it is impossible to accurately type any number into Excel with more than 15 digits (ending in numbers other than zero). If you do, Excel rounds the digits after the 15th place down to zero.

This problem stems from the fact that computers store numbers as binary numbers and display them as numeric numbers. The inherent back-and-forth conversion causes problems with certain numbers. Excel follows the industry standard IEEE 754 protocol for storing and calculating floating-point numbers in computers, a standard that was officially adopted in 1985 and was updated in 2008. Support for numbers with more than 15 digits requires more computer storage and greater processing resources. Accordingly, the number of digits supported in Excel is limited to 15, in line with the industry standard in pursuit of optimum programming and processing efficiency. (Note: To ensure your understanding of this problem, it is not correct to say that Excel can’t handle numbers greater than 15 digits. Rather, we should say that Excel does not handle these larger numbers accurately, as the extra digits in excess of 15 are rounded down to zero.)

While this limitation may plague astronomers and geneticists who deal with such astronomical figures, CPAs are rarely affected by this limitation (except on rare occasions, such as when CPAs are dealing with hyperinflated foreign currencies). Still, CPAs should be aware that Excel is vulnerable to producing such errors. For example:

– In Excel 2007, multiplying 77.1 times 850 yielded 100,000 instead of the accurate answer 65,535. As explained by Microsoft at tinyurl.com/3q3rf2n, Excel calculated the result correctly (e.g., if you charted the erroneous 100,000 number, it displayed correctly on the chart as 65,535), but the result displayed on the worksheet incorrectly due to an improper conversion of the binary representation of that number into its numerical string. This particular bug has since been corrected.

To illustrate the problem further, consider that the formula 1/3 calculates to 0.3333333~ (with an infinite number of repeating 3’s as decimal places). Because Excel can hold only 15 decimal places accurately, saving and retrieving this number results in a value that is very near to, but not exactly, 0.3333333~. Similarly, certain odd numbers create repeating binary decimals, and when those repeating digits are cut off after 15 places, the binary number does not convert back accurately to the intended numeric value. As an example, in all editions of Excel, the formula 22.26 − 21.29 should yield 0.97, but instead yields 0.970000000000002. Try it, and remember to increase your column width and decimal places so you can see the calculation problem.

Such errors are typically considered insignificant or immaterial because they rarely manifest into meaningful calculation errors; nonetheless, here are two measures you can take to eliminate potential floating decimal point errors:

1. The ROUND function. Use Excel’s ROUND function to round your calculated values to the desired decimal place, thereby eliminating any possibility of 15th-digit anomalies. For example, the formula =ROUND(-21.29 + 22.26,2) accurately yields 0.97.
2. Precision. You can turn on Excel’s Precision as Displayed option to force all formulas to truncate and round calculated values based on the visible digits. To turn this option on in Excel 2013, 2010, and 2007, select File (or the Office Orb), Options (or Excel Options), Advanced, and in the When calculating this workbook section, check the Set precision as displayed box, and then click OK. In Excel 2003, 2002, and 2000, from the Tools menu, select Options, and on the Calculation tab, under Workbook options, check the Precision as displayed box, and then click OK.