Vertex42 The Excel Nexus

An intentional circular reference can be used in the corrector formula to eliminate the need to Fill Right the corrector formula in order to perform the iterations. The corrector formula in cell C6 is changed from the formula shown above to

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.001 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 1013).

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.

A |
B |
C | D |
e |
F | ||

1 |
Simulating First-Order Reaction Using Two-point Predictor-Corrector Method | |||||

2 |
(A single corrector formula employing a circular reference) | |||||

3 |
t Pred Corr | |||||

4 |
0 0.2000 | |||||

5 |
20 |
0.1810 | ||||

6 |
40 |
0.1S381 |
0.16373 | |||

7 |
60 |
0.14821 |
0.14821 | |||

8 |
80 |
0.13417 |
0.13409 | |||

9 |
100 |
0.12137 |
0.12139 |
.. ', |
| |

10 |
120 |
0.10989 |
0.10981 | |||

11 |
140 |
0.09940 |
0.09942 | |||

12 |
160 |
0.09001 |
0.08993 | |||

13 |
180 |
0.08139 |
0.08144 | |||

14 |
200 |
0,07373 |
0.07364 |

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

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

Was this article helpful?

## Post a comment