Page 213 - Excel for Scientists and Engineers: Numerical Methods
P. 213
190 EXCEL: NUMERICAL METHODS
by describing methods for the solution of systems of linear equations, and
concludes by describing a method for handling nonlinear systems of equations.
Cramer's Rule
According to Cramer's rule, a system of simultaneous linear equations has a
unique solution if the determinant D of the coefficients is nonzero. To obtain the
solution, each unknown is expressed as a quotient of two determinants: the
denominator is D and the numerator is obtained from D by replacing the column
in the determinant corresponding to the desired unknown with the column of
constants.
Thus, for example, for the set of equations
2x + y - z = 0
x-y+z=6
x + 2y + z = 3
2 1 -1
D= 1 -1 1
1 2 1
The coefficients and constants lend themselves readily to spreadsheet
solution, as illustrated in Figure 9-1. Using the formula =MDETERM(AZ:C4), the
value of the determinant is found to be -9, indicating that the system is soluble.
Figure 9-1. Spreadsheet data for three equations in three unknowns.
(folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns 1', sheet 'Cramer's Rule')
Figure 9-2. The determinant for obtaining x.
(folder 'Chapter 09 Simultaneous Equations', workbook 'Simult Eqns I', sheet 'Cramer's Rule')
