Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 04:52, 15 April 2021

Revision as of 12:53, 15 April 2021

Line 15:

==Accuracy and binary storage==

[[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: mathematical the bottom line should be the same as the top line, in 'fp-math' the step '1 + 1/9000' leads to a roundigrounding up as the first bit of the 14 bit tail '10111000110010' of the mantissa falling off the table when adding 1 is a '1', this uprounding is not undone when subtracting the 1 again, since there is no information about the origin of the values in this step. thus the 're-subtracting' of 1 leaves a mantissa ending in '100000000000000' instead of '010111000110010', representing a value of '1.1111111111117289E-4' rounded by ex$el to 15 significant digits: '1.11111111111173E-4'.]]

[[File:Excel errors.PNG|thumb|390px|Of course mathematical 1 + x − 1 = x, 'floating point math' is sometimes a little different, that's not to be blamed on ex$el. The discrepancy indicates the error. All errors are beyond the 15-th significant digit of the intermediate 1+x value, all errors are in high value digits of the final result, that's the problematic effect of 'cancellation'.]]

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 1s after the decimal, the true difference when ‘1’ is subtracted is three 0s followed by a string of eleven 1s. However, the difference reported by Excel is three 0s followed by a 15-digit string of ''thirteen'' 1s 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, it's a effect in floating point calculations called 'cancellation'.