Page 221 - Excel for Scientists and Engineers: Numerical Methods

198 EXCEL: NUMERICAL METHODS

Do the elimination by columns.
ForC=l TON

Find maximum value in column
TempMax = 0
For R = 1 To N
If Abs(AugMatrix(R, C)) <= TempMax Then GoTo LoopEnd
If PivotLogical(R) = True Then GoTo LoopEnd
PivotRow(C) = R
TempMax = Abs(AugMatrix(R, C))
LoopEnd: Next R

Test the coefficient matrix for singularity.
If TempMax < 1 E-100 Then
GaussJordanl = CVErr(xlErrDiv0)
Exit Function
End If

'Matrix element(P,C) is pivot element.
P = PivotRow(C)
PivotLogical(P) = True
ForJ=IToN
If J <> P Then
factor = AugMatrix(J, C) I AugMatrix(P, C)
For R = C + 1 To N + 1
AugMatrix(J, R) = AugMatrix(J, R) - factor * AugMatrix(P, R)
Next R
End If
Next J
Next C
'Calculate the solution vector and return the specified element.
ForC = 1 TON
P = PivotRow(C)
X(C) = AugMatrix(P, N + 1) I AugMatrix(P, C)
Next C
GaussJordanl = X(va1ue-index)
End Function
Figure 9-5. VBA code for the Gauss-Jordan custom function.
(folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', module 'GaussJordanFunction')
Figures 9-6 and 9-7 illustrate the use of the GaussElim and GaussJordan
functions to solve systems of simultaneous equations, in this case the
spectrophotometric determination of the concentrations of a mixture of n
components by absorbance measurements at iz different wavelengths, as
described in the beginning of this chapter. The absorbance of a six-component
mixture was measured at six wavelengths; in Figure 9-3 the sample absorbances
are in column H and the known molar absorptivities of the six components are in
B5:GlO.