Page 260 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 10 ORDINARY DIFFERENTIAL EQUATIONS. PART I 237

iterations. The corrector formula in cell C6 is changed from the formula shown
above to
=$65+DX*(-k*$BS-k*CG)/2

which creates a circular reference, since cell C6 refers to itself. A circular
reference is usually an error; Excel displays the "Cannot resolve circular
references" error message and puts a zero in the cell. In this case, however, the
circular reference is intentional. We can make Excel recalculate the value in
each cell, using the result of the previous iteration. To "turn on" iteration, choose
Tools + Options -, Calculation and check the Iteration box. Unless you change
the default settings for iteration, Microsoft Excel stops calculating after 100
iterations or after the circular reference value changes by less than 0.00 1 between
iterations, whichever comes first. Enter 1E-9 in the Maximum Change box.
When you press OK the iterative circular reference calculation will begin. You
can Fill Down the formula into the remaining cells in column C. The
calculations in columns D-F are no longer needed and can be deleted. The
spreadsheet is shown in Figure 10-14.
The value displayed in cell C6 is identical to the value that would be
obtained by extending the corrector formula to, in this case, the tenth iteration
(these calculations can be seen in columns G-L in the spreadsheet of Figure 10-
13).
The errors obtained by using the modified Euler method are significantly less
than with the simple Euler method, but greater than with the fourth-order Runge-
Kutta method.

Figure 10-14. A simple predictor-corrector method utilizing a circular reference.
(folder 'Chapter 10 Examples', workbook 'ODE Examples', worksheet 'Predictor-Corrector Method (2)')