Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 08:38, 27 February 2018

Revision as of 15:32, 28 May 2018

Line 1:

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]]). ExcelWith nominallysome worksexceptions withregarding [[byte|8-byte]]erroneous numbersvalues, byinfinities, defaultand denormalized numbers, aExcel modifiedcalculates 1985in version[[double-precision floating-point format]] offrom 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> (Besidesbesides 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=0-470-02797-5 |edition=2nd |publisher=Wiley |year=2007 |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 three 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.