Page 219 - Excel for Scientists and Engineers: Numerical Methods

196 EXCEL: NUMERICAL METHODS

ResultVector(K) = AugMatrix(K, N + 1)
term = 0
For C = N To K + 1 Step -1
term = term + AugMatrix(K, C) * ResultVector(C)
Next C
ResultVector(K) = AugMatrix(K, N + 1) - term
Next K
If Range(Application.Caller.Address).Rows.Count > 1 Then
GaussElim = Application.Transpose(ResultVector)
Else
GaussElim = ResultVector
End If
End Function
Figure 9-4. VBA code for the Gaussian Elimination custom function.
(folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', module 'GaussianElimFunction')

The calculation proceeds essentially as described in the example. First, the
elements of the working matrix AugMatrix are populated by reading in the values
from the coef-matrix and consf-vector arguments. Then, in a loop, each row is
normalized by dividing by the appropriate diagonal element, and Gaussian
elimination is performed on the following rows. When all rows have been done,
the results are calculated, beginning with the last row of the upper diagonal
matrix.
The custom function GaussElim contains some features not discussed in the
worked-out example. As you can see from the example, the diagonal elements of
the coefficients matrix are the pivots and are used to normalize the matrix. If the
process of elimination results in a zero diagonal element, subsequent
normalization using that pivot value will result in a divide-by-zero error. Thus it
is necessary to check that the pivot value is not zero before normalizing. If the
pivot is zero, one can swap this row with one below it before normalizing and
proceeding with the elimination step. However, if we have reached the last row
of the matrix, we swap the last and first rows, but in this case we must swap rows
in the original matrix and start over from the beginning.

The Gauss-Jordan Method
The Gauss-Jordan method utilizes the same augmented matrix [AIC] as was
used in the Gaussian elimination method. In the Gaussian elimination method,
only matrix elements below the pivot row were eliminated; in the Gauss-Jordan
method, elements both above and below the pivot row are eliminated, resulting in
a unit coefficient matrix: