Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 01:10, 24 August 2021

Revision as of 20:06, 24 August 2021

Line 23:

{{cite book |title=Advanced Excel for scientific data analysis |publisher=Oxford University Press |author=Robert de Levie |author-link=Robert de Levie |year=2004 |isbn=0-19-515275-1 |page=44 |chapter=Algorithmic accuracy |url=https://www.amazon.com/Advanced-Excel-Scientific-Data-Analysis/dp/0195152751/ref=sr_1_1?ie=UTF8&s=books&qid=1270770876&sr=1-1#reader_0195152751}}

</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 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⁻⁴⁹ vs. 1.1101111111111011111111111111111111111111111111111101 × 2⁻⁴⁹). 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 0,5 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⁻⁵⁰; 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 from 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>