Content deleted Content added
moved footnotes out of references and into footnote § Tag: references removed |
Adding short description: "Details of data storage in a spreadsheet application" |
||
| (6 intermediate revisions by 6 users not shown) | |||
Line 1:
{{Short description|Details of data storage in a spreadsheet application}}
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 [[Arithmetic precision|precision]]). With some exceptions regarding erroneous values, infinities, and denormalized numbers, Excel calculates in [[double-precision floating-point format]] from the [[IEEE 754-2008|IEEE 754 specification]]<ref name=microsoft_spec>
{{cite web |title=Floating-point arithmetic may give inaccurate results in Excel |id=Revision 8.2
</ref> (besides numbers, Excel uses a few other data types<ref name=Dalton>
Line 28 ⟶ 29:
}}
</ref>
To illustrate, the lower figure tabulates the simple addition {{nobr|{{math| 1 + ''x'' − 1}} }} for several values of {{mvar|x}}. All the values of {{mvar|x}} begin at the 15 th decimal, so Excel must take them into account. Before calculating the sum {{nobr|{{math| 1 + ''x''}} ,}} Excel first approximates {{mvar|x}} as a binary number. If this binary version of {{mvar|x}} is a simple power of 2, the 15 digit decimal approximation to {{mvar|x}} is stored in the sum, and the top two examples of the figure indicate recovery of {{mvar|x}} without error. In the third example, {{mvar|x}} is a more complicated binary number, {{nobr|{{math| ''x'' {{=}} 1.110111⋯111 × 2{{sup|−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<sup>−49</sup> vs. 1.1101111111111011111111111111111111111111111111111101 × 2<sup>−49</sup>). 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 {{sfrac|1|2}} and 1 and between 8 and 10) is somewhat difficult and solved 'suboptimal'. In the fourth example, {{mvar|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 {{mvar|x}} is a rather small number. In the second from last example, {{nobr|{{math|''x'' {{=}} 1.110111⋯111 × 2{{sup|−50}} }} ;}} 15 bits altogether. The binary is replaced very crudely by a single power of 2 (in this example, 2{{sup|−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 {{nobr|{{math|1 + ''x'' − 1}} ,}} leading to {{nobr|{{math|''x'' {{=}} 0}} .}}{{efn|
Line 48 ⟶ 49:
|title=Excel addition strangeness
|website=office-watch.com
|url=
}}
</ref>
Line 70 ⟶ 71:
|id=KB 78113
|website=Microsoft support
|date=6 June 2024
|url=http://support.microsoft.com/kb/78113/en-us
}} — A detailed explanation with examples of the binary/15 sig fig storage consequences.
Line 87 ⟶ 89:
|title=What every computer scientist should know about floating point
|journal=Computing Surveys
|doi=10.1145/103162.103163
|type=edited reprint
|id=E19957-01 / 806-3568
|via=Sun Microsystems
|url=http://docs.sun.com/source/806-3568/ncg_goldberg.html
|url-access=subscription
}} — Focuses upon examples of floating point representations of numbers.
* {{cite web
|title=Visual Basic and arithmetic precision
Line 112 ⟶ 116:
}}
1. The shortcomings in the {{code|{{=}} 1 + x - 1}} tasks are a combination of 'fp-math weaknesses' and 'how Excel handles it', especially Excel's rounding. Excel does some rounding and / or 'snap to zero' for most of its results, in average chopping the last 3 bits of the IEEE double representation. This behavior can be switched
▲1. The shortcomings in the {{code|{{=}} 1 + x - 1}} tasks are a combination of 'fp-math weaknesses' and 'how Excel handles it', especially Excel's rounding. Excel does some rounding and / or 'snap to zero' for most of its results, in average chopping the last 3 bits of the IEEE double representation. This behavior can be switched of by setting the formula in parentheses: {{code|{{=}} ( 1 + 2^-52 - 1 )}}. You will see that even that small value survives. Smaller values will pass away as there are only 53 bits to represent the value, for this case 1.0000000000 0000000000 0000000000 0000000000 0000000000 01, the first representing the {{code|1}}, and the last the {{code|2^-52}}.
2. It is not only clean powers of two surviving, but any combination of values constructed of bits which will be within the 53 bits once the decimal 1 is added. As most decimal values do not have a clean finite representation in binary they will suffer from 'round off' and 'cancellation' in tasks like the above.
Line 174 ⟶ 177:
|title=What every computer scientist should know about floating point
|journal=Computing Surveys
|doi=10.1145/103162.103163
|type=edited reprint
|id=E19957-01 / 806-3568
|via=Sun Microsystems
|url=https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html
|url-access=subscription
}} — more or less 'the holy book' of fp-math
</ref>
who states:
Line 211 ⟶ 216:
|publisher=[[Academic Press, Inc.]]
|edition=8
|
|isbn=978-0-12-384933-5
|lccn=2014010276 <!-- |url=https://books.google.com/books?id=NjnLAwAAQBAJ
|access-date=2016-02-21-->
|page=25
Line 262 ⟶ 266:
|series=Department of Physics
|publisher=[[Lund University]]
|url=https://www.researchgate.net/publication/242226580
}}
</ref>
| |||