Page 228 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 9 SYSTEMS OF SIMULTANEOUS EQUATIONS 205

references in the formulas in cells B14 and C14 from references to row 13 to
references to row 14. The formulas in cells 814, C14 and D14 are now,
respectively,
=($E$8-$C$8*CI 4-$D$8*D14)/$8$8
=($E$9-$B$9*BI 4-$D$9*D14)/$C$9

=($E$I 0-$B$1 O*B14-$C$I O*C14)/$D$IO
This produces the Tannot 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
1E-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.

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

A Custom Function Procedure
for the Gauss-Seidel Method
The Gauss-Jacobi and the Gauss-Seidel methods can easily be implemented
as a custom function. Since the Gauss-Seidel method is more efficient, only the
Gauss-Seidel custom function is presented here. The VBA code is shown in
Figure 9- 13.
If any of the diagonal elements of the coefficients matrix are zero, a divide-
by-zero error will be produced. Thus it is necessary either to ensure that the
coefficients matrix does not contain any zero diagonal terms before beginning the
solution, or to incorporate code to swap rows if a zero diagonal element is
encountered. The GaussSeidel2 procedure (not shown) includes swapping if a
diagonal element = 0.