Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 15:55, 16 November 2013

Revision as of 18:19, 17 March 2014

Line 25:

</ref> To illustrate, the lower figure tabulates the simple addition {{nowrap|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⁻⁴⁹ (15 bits altogether). Here ''x'' is approximated by the 4-bit binary 1.111 × 2⁻⁴⁹ (some insight into this approximation can be found using [[geometric progression]]: ''x'' = 1.11 × 2⁻⁴⁹ + 2⁻⁵² × (1 − 2⁻¹¹) ≈ 1.11 × 2⁻⁴⁹ + 2⁻⁵² = 1.111 × 2⁻⁴⁹ ) and the decimal equivalent of this crude 4-bit approximation 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). 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⁻⁵⁰; 15 bits altogether. the binary is replaced very crudely by a single power of 2 (in this example, 2⁻⁴⁹) 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 thanfrom the binary, and is eliminated by truncation to 15 significant figures, making no contribution to {{nowrap|1 + ''x'' − 1}}, leading to ''x'' = 0.<ref name=decimal_input>

To input a number as binary, the number is submitted as a string of powers of 2: 2^(−50)*(2^0 + 2^−1 + ⋯). To input a number as decimal, the decimal number is typed in directly.