Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 14:30, 2 September 2010

Revision as of 09:33, 18 October 2010

Line 31:

</ref>

For ''x''′s that are not simple powers of 2, a noticeable error in {{nowrap|1 + ''x'' − 1}} can occur even when ''x'' is quite large. For example, if ''x'' = 1/1000, then {{nowrap|1 + ''x'' − 1}} = 9.99999999999'''''89''''' × 10⁻⁴, an error in the 13'th significant figure. In this case, if Excel simply added and subtracted the decimal numbers, avoiding the conversion to binary and back again to decimal, no round-off error would occur and accuracy actually would be better. Excel has the option to "Set precision as displayed".<ref name= discuss>This option is found on the "Excel options/Advanced" tab. See [http://support.microsoft.com/kb/214118 How to correct rounding errors: Method 2]

</ref> With this option, depending upon circumstance, accuracy may turn out to be better or worse, but you will know exactly what Excel is doing. (It should be noted, however, that only the selected precision is retained, and one cannot recover extra digits by reversing this option.) Some similar examples can be found at this link.<ref name =arithmetic>

Line 39:

</ref>

In short, a variety of accuracy behavior is introduced by the combination of representing a number with a limited number of binary digits, along with [[Truncation error|truncating]] numbers beyond the 15'thfifteenth significant figure.<ref name=deLevie3>

{{cite book |title=cited work |author=Robert de Levie |year=2004 |isbn=0195152751 |pages=45–46 |url=http://www.amazon.com/Advanced-Excel-Scientific-Data-Analysis/dp/0195152751/ref=sr_1_1?ie=UTF8&s=books&qid=1270770876&sr=1-1#reader_0195152751}}