The last two examples illustrate what happens if x is a rather small number. These two middle examples in the figure show that some error is introduced. The decimal input is approximated by a binary and then that decimal is used. In the fourth example, x is a decimal number not equivalent to a simple binary (although it agrees with the binary of the third example to the precision displayed). 16 digits between 1 and 8, but only 15 between 0,5 and 1 and between 8 and 10) is somewhat difficult and solved 'suboptimal'. Similar is done by other spreadsheets, the handling of the different amount of decimal digits which can be exactly stored in the 53 bit mantissa of a 'double' (e.g. Here the 'IEEE 754 double value' resulting of the 15 bit figure is 3.330560653658221E-15, which is rounded by Excel for the 'user interface' to 15 digits 3.33056065365822E-15, and then displayed with 30 decimals digits gets one 'fake zero' added, thus the 'binary' and 'decimal' values in the sample are identical only in display, the values associated with the cells are different (1.1101111111111100000000000000000000000000000000000000 × 2 −49 vs.
In the third example, x is a more complicated binary number, x = 1.110111⋯111 × 2 −49 (15 bits altogether). If this binary version of x is a simple power of 2, the 15-digit decimal approximation to x is stored in the sum, and the top two examples of the figure indicate recovery of x without error. Before calculating the sum 1 + x, Excel first approximates x as a binary number.
All the values of x begin at the 15-th decimal, so Excel must take them into account. To illustrate, the lower figure tabulates the simple addition 1 + x − 1 for several values of x. Excel's storage of numbers in binary format also affects its accuracy. The inaccuracy in Excel calculations is more complicated than errors due to a precision of 15 significant figures. Moreover, the error in Excel's answer is not simply round-off error, it is an effect in floating point calculations called 'cancellation'. Thus, the numbers Excel calculates with are not the numbers that it displays.
However, the difference reported by Excel is three 0s followed by a 15-digit string of thirteen 1s and two extra erroneous digits. 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. In the third line, one is subtracted from the sum using Excel. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the top figure the fraction 1/9000 in Excel is displayed. 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 is the problematic effect of 'cancellation'. Of course mathematical 1 + x − 1 = x, 'floating point math' is sometimes a little different, that is not to be blamed on Excel.
0 kommentar(er)
