As with other spreadsheets, Microsoft Excel works only to limited accuracy because it retains only a certain number of figures to describe numbers (it has limited precision). With some exceptions regarding erroneous values, infinities, and denormalized numbers, Excel calculates in double-precision floating-point format from the IEEE 754 specification (besides numbers, Excel uses a few other data types). Although Excel allows display of up to 30 decimal places, its precision for any specific number is no more than 15 significant figures, and calculations may have an accuracy that is even less due to five issues: round off, truncation, and binary storage, accumulation of the deviations of the operands in calculations, and worst, "catastrophic cancellation" at subtraction of values with similar magnitude. Standard floating-point error mitigation techniques apply.
Accuracy and binary storage
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 is an effect in floating point calculations called 'cancellation'. 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. To illustrate, the lower figure tabulates the simple addition 1 + x − 1 for several values of x. All the values of x begin at the 15 th decimal, so Excel must take them into account. Before calculating the sum 1 + x , Excel first approximates x as a binary number. 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. In the third example, x is a more complicated binary number, x = 1.110111⋯111 × 2−49 (15 bits altogether). 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. 1.1101111111111011111111111111111111111111111111111101 × 2−49). 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. 16 digits between 1 and 8, but only 15 between 1/2 and 1 and between 8 and 10) is somewhat difficult and solved 'suboptimal'. 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). The decimal input is approximated by a binary and then that decimal is used. These two middle examples in the figure show that some error is introduced. The last two examples illustrate what happens if x is a rather small number. In the second from last example, x = 1.110111⋯111 × 2−50 ; 15 bits altogether. The binary is replaced very crudely by a single power of 2 (in this example, 2−49) and its decimal equivalent is used. In the bottom example, a decimal identical with the binary above to the precision shown, is nonetheless approximated differently from the binary, and is eliminated by truncation to 15 significant figures, making no contribution to 1 + x − 1 , leading to x = 0 . For x′s that are not simple powers of 2, a noticeable error in 1 + x − 1 can occur even when x is quite large. For example, if x = 1/1000 , then 1 + x − 1 = 9.9999999999989 × 10−4 , an error in the 13 th significant figure. In this case, if Excel simply added and subtracted the decimal numbers, avoiding the conversion to binary and back again to decimal, no round-off error would occur and accuracy actually would be better. Excel has the option to "Set precision as displayed". With this option, depending upon circumstance, accuracy may turn out to be better or worse, but you will know exactly what Excel is doing. (Only the selected precision is retained, and one cannot recover extra digits by reversing this option.) Some similar examples can be found at this link. In short, a variety of accuracy behavior is introduced by the combination of representing a number with a limited number of binary digits, along with truncating numbers beyond the fifteenth significant figure. Excel's treatment of numbers beyond 15 significant figures sometimes contributes better accuracy to the final few significant figures of a computation than working directly with only 15 significant figures, and sometimes not. For the reasoning behind the conversion to binary representation and back to decimal, and for more detail about accuracy in Excel and VBA consult these links.
… excerpt ends here. Continue reading the full article.
