Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 21:54, 23 March 2019

Revision as of 21:18, 25 March 2019

Line 134:

Thus, we find the approximate form:

:<math>r_2 = \frac {c}{a \ r_1} \approx -\frac {c}{b}. </math>

These results are not subject to round-off error, but they are not accurate unless ''b'' ² is large compared to&nbsp;''ac''.

[[File:Excel quadratic error.PNG|thumb|350px| Excel graph of the difference between two evaluations of the smallest root of a quadratic: direct evaluation using the quadratic formula (accurate at smaller ''b'') and an approximation for widely spaced roots (accurate for larger ''b''). The difference reaches a minimum at the large dots, and round-off causes squiggles in the curves beyond this minimum.]]

Line 140:

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&nbsp;''b''.

In the figure, Excel is used to find the smallest root of the quadratic equation ''x''²&nbsp;+&nbsp;''bx''&nbsp;+&nbsp;''c''&nbsp;=&nbsp;0 for ''c''&nbsp;=&nbsp;4 and&nbsp;''c''&nbsp;=&nbsp;4&nbsp;×&nbsp;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''&ensp;-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>