Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 20:06, 24 August 2021

Revision as of 21:25, 16 December 2021

Line 6:

{{cite book |title=Financial Applications Using Excel Add-in Development in C/C++

|author=Steve Dalton |chapter=Table 2.3: Worksheet data types and limits |pages=13–14 |isbn=978-0-470-02797-54 |edition=2nd |publisher=Wiley |year=2007 |chapter-url=https://books.google.com/books?id=ABUSU9PWUuIC&pg=PA13}}

</ref>). Although Excel can display 30 decimal places, its precision for a specified number is confined to 15 [[significant figures]], and calculations may have an accuracy that is even less due to five issues: [[round-off error|round off]],<ref name=roundoff>

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 |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.

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=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><!-- 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.

Line 51:

*[http://docs.sun.com/source/806-3568/ncg_goldberg.html What every computer scientist should know about floating point] Focuses upon examples of floating point representations of numbers.

*[http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1 Visual basic and arithmetic precision]: Oriented toward VBA, which does things a bit differently.

*{{cite book |title=A guide to Microsoft Excel 2007 for scientists and engineers |author=Bernard V. Liengme |chapter=Mathematical limitations of Excel |page=31 ''ff'' |chapter-url=https://books.google.com/books?id=0qDm7uuDmv0C&pg=PA31 |isbn=978-0-12-374623-X8 |year=2008 |publisher=Academic Press }}

</ref>

Line 71:

Accuracy in Excel-provided functions can be an issue. [[Micah Altman]] ''et al.'' provide this example:<ref name=Altman>

{{cite book |title=Numerical issues in statistical computing for the social scientist |author1=Micah Altman |author2=Jeff Gill |author3=Michael McDonald |year=2004 |publisher=Wiley-IEEE |isbn=0-471-23633-0 |chapter-url=https://books.google.com/books?id=j_KevqVO3zAC&pg=PA12 |chapter=§2.1.1 Revealing example: Computing the coefficient standard deviation |page=12}}

</ref> The population standard deviation given by:

Line 83:

However, the first form keeps better numerical accuracy for large values of ''x'', because squares of differences between ''x'' and ''x''_av leads to less round-off than the differences between the much larger numbers Σx² and (Σx)². The built-in Excel function STDEVP(), however, uses the less accurate formulation because it is faster computationally.<ref name=Levie>

{{cite book |title=Advanced Excel for scientific data analysis |author=Robert de Levie |publisher=Oxford University Press |year=2004 |isbn=0-19-515275-1 |url=https://books.google.com/books?id=IAnO-2qVazsC&printsec=frontcove|pages=45–46}}

</ref>

Line 165:

Although Excel nominally works with [[byte|8-byte]] numbers by default, [[Visual Basic for Applications|VBA]] has a variety of data types. The ''Double'' data type is 8 bytes, the ''Integer'' data type is 2 bytes, and the general purpose 16 byte ''Variant'' data type can be converted to a 12 byte ''Decimal'' data type using the VBA conversion function ''CDec''.<ref name=John_Walkenbach>

{{cite book |title=Excel 2010 Power Programming with VBA |chapter=Defining data types |pages=198 ''ff'' and Table 8-1|isbn=978-0-470-47535-85 |author=John Walkenbach |year=2010 |publisher=Wiley |chapter-url=https://books.google.com/books?id=dtSdrjjVXrwC&pg=PA198}}

</ref> Choice of variable types in a VBA calculation involves consideration of storage requirements, accuracy and speed.