Content deleted Content added
m fixed reference |
KolbertBot (talk | contribs) m Bot: HTTP→HTTPS |
||
Line 21:
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.<ref name=deLevie>
{{cite book |title=Advanced Excel for scientific data analysis |publisher=Oxford University Press |author=Robert de Levie |year=2004 |isbn=0-19-515275-1 |page=44 |chapter=Algorithmic accuracy |url=
</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<sup>−49</sup> (15 bits altogether). Here ''x'' is approximated by the 4-bit binary 1.111 × 2<sup>−49</sup> (some insight into this approximation can be found using [[geometric progression]]: ''x'' = 1.11 × 2<sup>−49</sup> + 2<sup>−52</sup> × (1 − 2<sup>−11</sup>) ≈ 1.11 × 2<sup>−49</sup> + 2<sup>−52</sup> = 1.111 × 2<sup>−49</sup> ) 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.
Line 41:
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 [[Truncation error|truncating]] numbers beyond the fifteenth significant figure.<ref name=deLevie3>
{{cite book |title=cited work |author=Robert de Levie |year=2004 |isbn=0-19-515275-1 |pages=45–46 |url=
</ref><!-- These figures are simply screen shots of the listed arithmetic using Excel 2007 --> 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.
| |||