Page 214 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 9 SYSTEMS OF SIMULTANEOUS EOUATIONS 191

The x values that comprise the solution of the set of equations can be
calculated in the following manner: xk is given by a quotient in which the
denominator is D and the numerator is obtained from D by replacing the gh
column of coefficients by the constants c,, cz. .... The unknowns are obtained
readily by copying the coefficients and constants to appropriate columns in
another location in the sheet. For example, to obtain x, the determinant is shown
in Figure 9-2, and x = 2 is obtained from the formula
=MDETERM(A8:CI O)/MDETERM(A2:C4)
y = -1 and z = 3 are obtained from appropriate forms of the same formula.
Cramer's method is very inefficient and should be used only for systems of
only a few equations.

Solving Simultaneous Equations
by Matrix Inversion
Simultaneous equations can be represented in matrix notation by
Ax=C (9- 1 )
where A is the matrix of coefficients, B the matrix of unknowns, and C the
matrix of constants. Multiplying both sides of equation 9-1 by A-' yields
X = A-'C (9-2)
In other words, the solution matrix is obtained by multiplying the matrix of
constants by the inverse matrix of the coefficients. To return the solution values
shown in Figure 9-3, the array formula
{=MMULT(MINVERSE(A2:C4),D2:D4)}

was entered in cells E2:E4.

Figure 9-3. Solving a set of simultaneous equations by means of matrix methods.
(folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns 1', sheet 'Matrix Inversion')

Solving Simultaneous Equations
by Gaussian Elimination
A system of linear equations such as
x+2y= 15
3x+Sy=57