Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 16:09, 22 December 2020

Revision as of 19:58, 3 March 2021

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/9000 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's1s after the decimal, the true difference when ‘1’ is subtracted is three 0's0s followed by a string of eleven 1's1s. However, the difference reported by Excel is three 0's0s followed by a 15-digit string of ''thirteen'' 1's1s 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 simply round-off error.

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>