Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 15:27, 4 June 2016

Revision as of 02:44, 5 October 2016

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=0-470-02797-5 |edition=2nd |publisher=Wiley |year=2007 |url=httphttps://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 three issues: [[round-off error|round off]],<ref name=roundoff>

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'' |url=httphttps://books.google.com/books?id=0qDm7uuDmv0C&pg=PA31 |isbn=0-12-374623-X |year=2008 |publisher=Academic Press}}

</ref>

Line 63:

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 |url=httphttps://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 75:

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=httphttps://books.google.com/books?id=IAnO-2qVazsC&printsec=frontcove|pages=45–46}}

</ref>

Line 105:

It is possible to determine the round-off error by using the [[Taylor series]] formula for the square root:

<ref name="Zwillinger_2014">{{cite book |author-first1=Izrail Solomonovich |author-last1=Gradshteyn |author-link1=Izrail Solomonovich Gradshteyn |author-first2=Iosif Moiseevich |author-last2=Ryzhik |author-link2=Iosif Moiseevich Ryzhik |author-first3=Yuri Veniaminovich |author-last3=Geronimus |author-link3=Yuri Veniaminovich Geronimus |author-first4=Michail Yulyevich |author-last4=Tseytlin |author-link4=Michail Yulyevich Tseytlin |author-first5=Alan |author-last5=Jeffrey |editor-first1=Daniel |editor-last1=Zwillinger |editor-first2=Victor Hugo |editor-last2=Moll |translator=Scripta Technica, Inc. |title=Table of Integrals, Series, and Products |publisher=[[Academic Press, Inc.]] |date=2015 |orig-year=October 2014 |edition=8 |language=English |isbn=0-12-384933-0 |id=ISBN 978-0-12-384933-5 |lccn=2014010276 <!-- |url=httphttps://books.google.com/books?id=NjnLAwAAQBAJ |access-date=2016-02-21-->|title-link=Gradshteyn and Ryzhik |chapter=1.112. Power series |page=25}}</ref>

:<math>\sqrt{b^2-4ac} = b \ \sqrt{1-\frac{4ac}{b^2}} \approx b \left( 1 -\frac{2ac}{b^2} + \frac{2 a^2 c^2 }{b^4} + \cdots \right ). </math>

Line 148:

</ref> Using Visual Basic for Applications, any of these methods can be implemented in Excel. Numerical methods use a grid where functions are evaluated. The functions may be interpolated between grid points or extrapolated to locate adjacent grid points. These formulas involve comparisons of adjacent values. If the grid is spaced very finely, round-off error will occur, and the less the precision used, the worse the round-off error. If spaced widely, accuracy will suffer. If the numerical procedure is thought of as a [[Negative feedback amplifier|feedback system]], this calculation noise may be viewed as a signal that is applied to the system, which will lead to instability unless the system is carefully designed.<ref name=Hamming>

{{cite book |author=[[Richard Hamming|R. W. Hamming]] |title=Numerical Methods for Scientists and Engineers |year= 1986 |isbn=0-486-65241-6 |url=httphttps://books.google.com/books?id=Y3YSCmWBVwoC&printsec=frontcover |publisher=Courier Dover Publications |edition=2nd}} This book discusses round-off, truncation and stability extensively. For example, see Chapter 21: [httphttps://books.google.com/books?id=Y3YSCmWBVwoC&pg=PA357 Indefinite integrals – feedback], page 357.

</ref>

Line 155:

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=0-470-47535-8 |author=John Walkenbach |year=2010 |publisher=Wiley |url=httphttps://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.