Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 11:06, 3 July 2010

Revision as of 11:19, 3 July 2010

Line 1:

As with other spreadsheets, Excel works only to limited accuracy because it retains only a certain number of figures to describe numbers (it has limited [[Arithmetic precision|precision]]). Although Excel cannominally displayworks 30 decimal points, its precision for a specified number is confined to 15with [[significant figuresbyte|8-byte]], andnumbers calculationsby maydefault, havea anmodified accuracy1985 thatversion isof even less due to two issues:the [[roundIEEE 754-off error2008|roundIEEE 754 offspecification]]<ref name=roundoffmicrosoft_spec>

{{cite web |url=http://support.microsoft.com/kb/78113/en-us |title=Floating-point arithmetic may give inaccurate results in Excel |publisher=Microsoft support |work=Revision 8.2 ; article ID: 78113 |date=June 30, 2010 |accessdate=2010-07-02}}

</ref> (Besides numbers, Excel uses a few other data types.<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>) Although Excel can display 30 decimal points, its precision for a specified number is confined to 15 [[significant figures]], and calculations may have an accuracy that is even less due to two issues: [[round-off error|round off]]<ref name=roundoff>

Round-off is the loss of accuracy when numbers that differ by small amounts are subtracted. Because each number has only fifteen significant digits, their difference is inaccurate when there aren't enough significant digits to express the difference.

Line 128 ⟶ 137:

===Accuracy within VBA===

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, and the ''Integer'' data type is 2 bytes.<ref name=DaltonJohn_Walkenbach>

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

{{cite web |url=http://support.microsoft.com/kb/78113/en-us |title=Floating-point arithmetic may give inaccurate results in Excel |publisher=Microsoft support |work=Revision 8.2 ; article ID: 78113 |date=June 30, 2010 |accessdate=2010-07-02}}

</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 2010 Power Programming with VBA |chapter=Defining data types |pages=198 ''ff'' and Table 8-1|isbn=0470475358 |author=John Walkenbach |year=2010 |publisher=Wiley |url=http://books.google.com/books?id=dtSdrjjVXrwC&pg=PA198}}