Page 217 - Excel for Scientists and Engineers: Numerical Methods

194 EXCEL: NUMERICAL METHODS

1 0.2 0.2 0.2
0 1 1 -0.4286 77.::;]
0 0 3 -3.2857 -30.429
I 0 7 -5.1429 65.286
0
I 0 1 -0.4286 77.857
Row 3 is normalized and the x3 terms are eliminated from column 3 of row 4:
0.2
1 0.2 0.2
137
1
0
0
1 -1.0952 -10.143
0
1 0 1 -0.4286 -10.143
Row 4 is normalized: 0 0 2.5238 136.29
0.2
1 0.2 0.2
o
77,KI
1
0
1 -1.0952
0
1
54
0
0
As you can see, the coefficients matrix is now an upper triangular matrix,
with the diagonal elements equal to one. The results are obtained by successive
substitution, beginning with the last row. The last row corresponds to x4 = 154,
the third row corresponds to x3 - 0.272727~4 = 107, from which x3 = 149, and so
on. The results, XI, x2, x3 and x4 are 106, 52, 49, 54, respectively. You can see
the steps in Gaussian elimination calculation by using the demo program
provided on the CD (folder 'Chapter 09 Simultaneous Equations', workbook
'Simult Lin Eqns', sheet 'Gaussian Elimination Demo').
The Gaussian elimination method can also be performed by using the VBA
custom function GaussElim. The VBA code is shown in Figure 9-4.
The syntax of the function is GaussElim(coeff-rnatrix,const-vector). The
function returns the results vector; since the function is an array function, you
must select an appropriately sized range of cells and press CTRL+SHIFT+ENTER
(Windows) or COMMAND+RETURN or CTRL+SHIFT+RETURN (Macintosh).