Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 14:50, 2 July 2010

Revision as of 15:00, 2 July 2010

Line 1:

[[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: the bottom line should be the same as the top line.]]

[[File:Excel errors.PNG|thumb|390px|Of course, 1 + x − 1 = x. The discrepancy indicates the error. All errors but the last are beyond the 15-th decimal.]]

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

</ref> and [[Binary numeral system|binary storage]].

</ref> and [[Binary numeral system|binary storage]]. In the top figure the fraction 1/900 in Excel is displayed. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum has only eleven 1's after the decimal, the true difference when ‘1’ is subtracted is three 0's followed by a string of eleven 1's. However, the difference reported by Excel is three 0's followed by a 15-digit string of ''thirteen'' 1's and two extra erroneous digits. Thus, the numbers Excel calculates with are ''not'' the numbers that it displays. Moreover, the error in Excel's answer is not just round-off error. How was this answer obtained?

==Accuracy and binary storage==

[[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: the bottom line should be the same as the top line.]]

[[File:Excel errors.PNG|thumb|390px|Of course, 1 + x − 1 = x. The discrepancy indicates the error. All errors but the last are beyond the 15-th decimal.]]

</ref> and [[Binary numeral system|binary storage]]. In the top figure the fraction 1/900 in Excel is displayed. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum has only eleven 1's after the decimal, the true difference when ‘1’ is subtracted is three 0's followed by a string of eleven 1's. However, the difference reported by Excel is three 0's followed by a 15-digit string of ''thirteen'' 1's and two extra erroneous digits. Thus, the numbers Excel calculates with are ''not'' the numbers that it displays. Moreover, the error in Excel's answer is not just round-off error. How was this answer obtained?

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>

Line 59 ⟶ 61:

</ref>

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

{{Expand section|date=April 2010}}

[[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]]

Line 139 ⟶ 141:

==References==

<references/>

==See also==

*[[Microsoft Excel]]

[[Category:Microsoft software]]