The Gauss Seidel Method Implemented on a Worksheet Using Circular References

The worksheet in the preceding section can be easily modified to use intentional circular references, as follows. After entering the starting values in row 13 and the formulas in row 14 as before (Figure 9-11), change the cell references in the formulas in cells B14 and C14 from references to row 13 to references to row 14. The formulas in cells B14, C14 and D14 are now, respectively,

=($E$8-$C$8*C14-$D$8*D14)/$B$8 =($E$9-$B$9*B14-$D$9*D14)/$C$9 =($E$10-$B$10*B14-$C$10*C14)/$D$10

This produces the "Cannot resolve circular references" error message. Then choose Tools-^Options..., choose the Calculation tab, check the Iteration box and change the Maximum Change parameter to a suitable small value, such as IE-10 or even zero. When you press OK, the final values of the variables are returned, as shown in Figure 9-12. Cell A14 contains the formula =A14+1, and shows that, in this example, one hundred cycles of iteration (the default value in Tools-»-Options->Calculation) were performed.

A

B

c

D

E

12

iteration

XI

x2

x3

% error (X1)

13

■ : ■ 1 ■

;

14

J------.„,„,„■„, ^

33.300000

11.250000

O.OOE+OQ

Figure 9-12. The Gauss-Seidel method using intentional circular references, (folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', sheet 'Gauss-Seidel 2')

Figure 9-12. The Gauss-Seidel method using intentional circular references, (folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', sheet 'Gauss-Seidel 2')

+1 -1

Post a comment