Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 15:00, 2 July 2010

Revision as of 15:19, 2 July 2010

Line 35:

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

Although Excel nominally works with [[byte|8-byte]] numbers by default,<ref name=Dalton>

{{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=0470027975 |edition=2nd |publisher=Wiley |year=2007 |url=http://books.google.com/books?id=ABUSU9PWUuIC&pg=PA13}}

</ref> (a modified 1985 version of the [[IEEE 754-2008|IEEE 754 specification]]<ref name=microsoft_spec>

[http://support.microsoft.com/kb/78113/en-us Microsoft '''Overview''']

</ref>), VBA has a variety of data types. The ''Double'' data type is 8 bytes, and the ''Integer'' data type is 2 bytes.<ref name=John_Walkenbach>

{{cite book |title=Excel 2007 power programming with VBA |chapter=Defining data types |pages=194–197 |isbn=0470044012 |author=John Walkenbach |year=2007 |publisher=Wiley |url=http://books.google.com/books?id=HxhXwdUSTe0C&pg=PA194}}

</ref>

For the reasoning behind the conversion to binary representation and back to decimal, and for more detail about accuracy in Excel and VBA consult these links.<ref name=accuracy_links>

Line 63 ⟶ 48:

==Examples where precision is no indicator of accuracy==

{{Expand section|date=April 2010}}

===Statistical functions===

[[File:Excel Std Dev Error.PNG|thumb|450px|Error in Excel 2007 calculation of standard deviation. All four columns have the same deviation of 0.5]]

WhereAccuracy isin accuracyExcel-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 |author= Micah Altman, Jeff Gill, Michael McDonald |year=2004 |publisher=Wiley-IEEE |isbn=0471236330 |url=http://books.google.com/books?id=j_KevqVO3zAC&pg=PA12 |chapter=§2.1.1 Revealing example: Computing the coefficient standard deviation |page=12}}

Line 82 ⟶ 68:

</ref>

===Round-off error===

AUser secondcomputations must be carefully organized to insure round-off error does not become an issue. An example occurs in solving a [[quadratic equation]]:

:<math>ax^2 + b x +c = 0 \ .</math>

The solutions (the roots) of this equation are exactly determined by the [[quadratic formula]]:

Line 138 ⟶ 124:

</ref>

===Accuracy within VBA===

Although Excel nominally works with [[byte|8-byte]] numbers by default,<ref name=Dalton>

{{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=0470027975 |edition=2nd |publisher=Wiley |year=2007 |url=http://books.google.com/books?id=ABUSU9PWUuIC&pg=PA13}}

</ref> (a modified 1985 version of the [[IEEE 754-2008|IEEE 754 specification]]<ref name=microsoft_spec>

[http://support.microsoft.com/kb/78113/en-us Microsoft '''Overview''']

</ref>), [[Visual basic for applications|VBA]] has a variety of data types. The ''Double'' data type is 8 bytes, and the ''Integer'' data type is 2 bytes.<ref name=John_Walkenbach>

{{cite book |title=Excel 2007 power programming with VBA |chapter=Defining data types |pages=194–197 |isbn=0470044012 |author=John Walkenbach |year=2007 |publisher=Wiley |url=http://books.google.com/books?id=HxhXwdUSTe0C&pg=PA194}}

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

==References==

<references/>

==See also==

*[[Microsoft Excel]]