Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 17:57, 14 November 2013

Revision as of 15:55, 16 November 2013

Line 143:

The bottom line is that in doing this calculation using Excel, as the roots become farther apart in value, the method of calculation will have to switch from direct evaluation of the quadratic formula to some other method so as to limit round-off error. The point to switch methods varies according to the size of coefficients ''a'' and ''b''.

In the figure, Excel is used to find the smallest root of the quadratic equation ''x''² + ''bx'' + ''c'' = 0 for ''c'' = 4 and ''c'' = 4 × 10⁵. The difference between direct evaluation using the quadratic formula and the approximation described above for widely spaced roots is plotted ''vs.'' ''b''. Initially the difference between the methods declines because the widely- spaced root method becomes more accurate at larger ''b''-values. However, beyond some ''b''-value the difference increases because the quadratic formula (good for smaller ''b''-values) becomes worse due to round-off, while the widely spaced root method (good for large ''b''-values) continues to improve. The point to switch methods is indicated by large dots, and is larger for larger ''c'' -values. At large ''b''-values, the upward sloping curve is Excel's round-off error in the quadratic formula, whose erratic behavior causes the curves to squiggle.

A different field where accuracy is an issue is the area of [[Numerical integration|numerical computing of integrals]] and the [[Numerical ordinary differential equations|solution of differential equations]]. Examples are [[Simpson's rule]], the [[Runge–Kutta method]], and the Numerov algorithm for the [[Schrödinger equation]].<ref name=Blom>