Recipe 9.4. Solving Linear Systems
You'd like to solve a linear system of equations using Excel.
You can take one of several approaches to solving linear systems in Excel. You could, for example, use VBA to program any of the standard numerical algorithms for solving systems of equations. While this approach would undoubtedly work, it's the most
If you can frame the system of equations in the form of a matrix equation, then you can use direct matrix inversion to find a solution. If you can't or don't want to do so, or if you're dealing with nonlinear equations, you can use one of Solver's several approaches.
In Recipe 7.10, I showed you how to use Excel's built-in matrix functions to multiply and invert matrices. You can use these same functions to solve a linear system of equations of the form:
The approach to solving this problem is a straightforward application of the built-in matrix functions, where you can find the unknown
For example, let's say you're given [ A ] and [ b ] matrices as shown in Figure 9-12.
Figure 9-12. Solving a matrix equation
The first step in the solution is to invert the [
] matrix. To do so, enter the matrix formula
. Remember, this is a matrix formula entered in a range of cells; you have to press Ctrl-Shift-Enter to enter the formula. The results of this matrix inversion are shown in Figure 9-12 in
Using Solver with constraints
Instead of using matrix functions, which can get a little unwieldy for large matrices, you can use Solver to solve a linear system (or a nonlinear one, as discussed in Recipe 9.5). In this example, I'll show you how to solve the same linear system discussed earlier by using Solver with constraints. Figure 9-13 shows how this problem is set up.
Figure 9-13. Solving a linear system with Solver by using constraints
The first thing I do in this approach is set up a table containing the given a -coefficients and b -coefficients along with initial guesses for the corresponding x s. Referring to Figure 9-13, I entered the a -coefficients in cells D9 through F11, the b -coefficients in cells I9 through I11, and the x s in cells H9 through H11.
Next, I set up a column of cells (J9 through J11) containing formulas of the form
. These formulas compute
for each equation, given the
-coefficients and the initial guesses for the
s. Ideally, the results in this column should equal the given
-values shown in the adjacent column. Since initially the
s are only guesses, the
Figure 9-14. Solver with constraints
We will set the By Changing Cells fields to the range of cells containing the x -values, cells H9 through H11 in this case. Now it's time to add some constraints.
Basically, we want to let Solver vary the x s subject to the constraints that the computed b -values in cells J9 through J11 are equal to the given b -values in cells I9 through I11. So we have to add a constraint for each b -value.
Figure 9-14 shows the constraints I set up in Solver. To add a constraint, press the Add button to open the Add Constraint window shown in Figure 9-15.
Figure 9-15. Add Constraint window
In the Cell Reference field, select one of the cells containing a computed b -value. Then in the Constraint field, select the cell containing the corresponding given b -value. In the drop-down listbox in the center of the window, select the equals sign ( = ). Press the Add button to add the constraint. Pressing Add does not close the Add Constraint window, so you can go ahead and add the other two constraints as well. Press Cancel when you're done to close the window. Upon doing so, you should see the constraints as shown in Figure 9-14.
After everything is set up, you can press Solve to actually solve the problem. Solver should quickly find a solution as in Figure 9-14, where the column of x -values (H9 through H11) represents the solution to the given system of equations. Notice that these x -values are equal to those found earlier using matrix inversion.
Using Solver to minimize residuals
An alternative approach to using constraints in Solver is to frame the problem in the form of minimizing residuals. We can easily do this by adding a column to the setup shown in Figure 9-14. The new setup is shown in Figure 9-16.
Figure 9-16. Solving a linear system with Solver by minimizing residuals
Column K contains the square of residuals between the given b -values (column I) and the computed b -values (column J). The formulas in column K are of the form =(I20-J20)^2 . Cell K23 contains the sum of these squared residuals, using the formula =SUM(K20:K22) .
This is now essentially a least-squares problem, where we want to minimize the sum of squared residuals. We can readily use Solver to minimize this sum by changing the x -values contained in column H.
This is a straightforward application of Solver using techniques already discussed in this chapter. Figure 9-17 shows the Solver model for this example.
Figure 9-17. Solver without constraints
Set the target cell to the cell containing the sum of squared residuals, cell K23. In this case you want to minimize the value in cell K23. Select the cells containing the x -values to vary in the By Changing Cells field. Notice that there are no constraints this time.
Upon pressing Solve, you should see the results already shown in Figure 9-16. Here again, the resulting x -values are the same as those found using the two other approaches discussed earlier.
Which approach is better depends on your preferences and the