Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 16:09, 29 April 2011

Revision as of 11:11, 4 May 2011

Line 17:

[[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: the bottom line should be the same as the top line.]]

[[File:Excel errors.PNG|thumb|390px|Of course, 1 + x − 1 = x. The discrepancy indicates the error. All errors but the last are beyond the 15-th decimal.]]

In the top figure the fraction 1/9009000 in Excel is displayed. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum has only eleven 1's after the decimal, the true difference when ‘1’ is subtracted is three 0's followed by a string of eleven 1's. However, the difference reported by Excel is three 0's followed by a 15-digit string of ''thirteen'' 1's and two extra erroneous digits. Thus, the numbers Excel calculates with are ''not'' the numbers that it displays. Moreover, the error in Excel's answer is not just round-off error. How was this answer obtained?

The inaccuracy in Excel calculations is more complicated than errors due to a precision of 15 significant figures. Excel's storage of numbers in binary format also affects its accuracy.<ref name=deLevie>