Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 03:16, 22 June 2021

Revision as of 01:10, 24 August 2021

Line 33:

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

[http://news.office-watch.com/t/n.aspx?a=612&z=9 Excel addition strangeness]