The GaussJordan Method
The Gauss-Jordan method utilizes the same augmented matrix [A|C] 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:
|
1 |
0 |
0 |
0 |
116 |
|
0 |
1 |
0 |
0 |
72 |
|
0 |
0 |
1 |
0 |
149 |
|
0 |
0 |
0 |
1 |
154 |
The advantage of this method is that the calculation of the vector of results is simplified.
The VBA custom function GaussJordanl, shown in Figure 9-5 incorporates partial pivoting. Two versions are provided on the CD that accompanies this book: the first version, GaussJordanl, has the syntax GaussJordanl(coeff_matrix, const_vector, valuejndex). The value_index argument specifies the element of the results vector to be returned. The second version, GaussJordan2, has the syntax GaussJordan2(coe/f_/nafr/x, const_vector), and returns the vector of results. You must select an appropriately sized range of cells and press CTRL+SHIFT+ENTER (Windows) or COMMAND+RETURN or CTRL+SHIFT+RETURN (Macintosh).
Option Base 1 Option Explicit
'Solving systems of linear equations by the Gauss-Jordan elimination method '+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function GaussJordanl (coeff_matrix, const_vector, valuejndex) ' This version returns a single element of the solution vector, ' specified by valuejndex.
Dim X() As Double, AugMatrix() As Double, PivotRow() As Integer
Dim PivotLogical() As Boolean
Dim I As Integer, J As Integer
Dim R As Integer, C As Integer, P As Integer
Dim N As Integer
Dim TempMax As Double, factor As Double N = coeff_matrix.Rows.Count
ReDim X(N), AugMatrix(N, N + 1), PivotRow(N), PivotLogical(N)
'Create augmented matrix (A|B) with dimensions N x (N+1) For I = 1 To N For J = 1 To N
AugMatrix(l, J) = coeff_matrix(l, J) Next J, I For J = 1 To N
'Initialize pivot elements for each row
'Do the elimination by columns. For C = 1 To N
'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 < 1E-100 Then GaussJordanl = CVErr(xlErrDivO) Exit Function End If
'Matrix element(P,C) is pivot element. P = PivotRow(C) PivotLogical(P) = True For J = 1 To N If J <> P Then factor = AugMatrix(J, C) / 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. For C = 1 To N P = PivotRow(C)
X(C) = AugMatrix(P, N + 1) / AugMatrix(P, C) Next C
GaussJordanl = X(valuejndex) 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 n 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:G10.
|
A |
B C |
D |
E |
FIG |
H I | ||
|
3 |
molar absorptivlties of species 1-6 | ||||||
|
4 |
Wavelength, nm |
&t £2 |
e4 |
es |
£5 |
absorbance | |
|
5 |
400 |
192! 19.1 |
51.8 |
1.59 |
3.2 |
0.11 |
0,3548 |
|
6 |
450 |
17.5 190 |
53.5 |
6.3 |
6.6 |
0.0 |
0,4805 |
|
7 |
500 |
3.5 55,9 |
157 |
29,5 |
12.7 |
0.19 |
0.5185 |
|
8 |
550 |
2.5 7.9 |
15.9 |
223 |
27.9 |
0.79 |
0.5075 |
|
9 |
BOO |
0.83 1.4 |
3.1 |
38 2 |
218 |
1.3 |
0.2598 |
|
10 |
650 |
0.19 0.16 |
Ü.15 |
15.9 |
105 |
0.80 |
0.1167 |
Figure 9-6. Data table for use with the GaussElim or GaussJordan functions, (folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', sheet 'Elimination Fns')
Figure 9-6. Data table for use with the GaussElim or GaussJordan functions, (folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', sheet 'Elimination Fns')
Figure 9-7 shows the results returned by the GaussElim and GaussJordan2 functions. The results vector is the vector of concentrations of the six components in the mixture. The percentage error figures in columns L and N are the errors between the known concentrations and the concentrations returned by the functions.
As the number of simultaneous equations becomes larger, the errors can increase drastically. In this system of equations, the values of the first through fifth variables can be obtained with good precision, since each has a maximum where the other species do not absorb strongly. The concentration of the sixth species is subject to significant error. And if the absorbance measurements are changed randomly by just ±1 in the last figure (Figure 9-8), the errors increase significantly.
|
J |
K |
L |
M |
N I | |
|
3 |
Results | ||||
|
4 |
Cones used |
GaussElim |
% error |
GaussJordan |
% error |
|
5 |
0.001043 |
0,001043 |
0.02 |
0.001043 |
0.02 |
|
6 |
0.001711 |
0.001711 |
0.02 |
0,001711 |
0.02 |
|
7 |
0.002239 |
0.002239 |
0,03 |
0,002239 |
0.03 |
|
8 |
0.001935 |
0.001935 |
0.02 |
0,001935 |
0.02 |
|
9 |
0.000789 |
0,000788 |
0.05 |
0.000788 |
0.05 |
|
10 |
0.002825 |
0.002925 |
9.6 |
0 002925 |
9,6 |
|
J |
K |
L |
M |
N | |
|
3 |
Results | ||||
|
4 |
Cones used |
GaussESim |
% error |
GaussJordan |
% error |
|
5 |
0.001043 |
0 001044 |
0.06 |
0.001044 |
0.06 |
|
6 |
0.001711 |
0.001711 |
0.03 |
0,001711 |
0.03 |
|
7 |
0.002239 |
0.002239 |
0.03 |
0.002239 |
0.03 |
|
8 |
0 001935 |
0.001936 |
0.09 |
0.001936 |
0.09 |
|
9 |
0.0007Ü9 |
0.000792 |
0.25 |
0,000792 |
0.25 |
|
10 |
0.002825 |
0.002364 |
44 |
0.002364 |
44 |
Figure 9-8. Results from the GaussElim or Gausslordan functions when small changes are made in the coefficients (compare Figure 9-7), (folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', sheet 'Elimination Fns')
Figure 9-8. Results from the GaussElim or Gausslordan functions when small changes are made in the coefficients (compare Figure 9-7), (folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns II', sheet 'Elimination Fns')
Post a comment