Home > Excel Tutorials > Using Excel to Solve Simultaneous Linear Equations
Using Excel to Solve Simultaneous Linear Equations
In this article we present two methods of solving simultaneous linear equations using Excel. The first method uses Excel Solver (which is an add-in optimizer). The second method makes use of Excel's built in matrix functions.
We demonstrate these two methods by solving the following equations for u, v, w, x and y:
u + v + w + x + y = 5.5
u + 2v + w - 0.5x + 2y = 22.5
2v + 2w - x - y = 30
2u - w + 0.75x + 0.5y = -11
u + 0.25v + w - x = 17.5
Using Excel Solver to Solve Simultaneous Linear Equations
To use Excel Solver, you'll need to set up a worksheet like the one below:
Fig. 1 - Specimen Worksheet Before Running Excel Solver
|
Note that each of the five equations is entered as a formula in separate cells that are directly below each other. This arrangement makes it easy to set up Excel Solver because we can select all formulas using a single range. We do the same with the unknowns (u, v, w, x and y) and the constants on the right hand side of each equation. That is, we've arranged them in a vertical block of cells.
You might want to enter an initial guess for u, v, w, x and y. We haven't done so here (the cells are blank) and often you won't need to. If Excel Solver can't find a solution, it might be necessary to enter a good guess for the unknowns.
Now start Solver (by clicking on Tools then Solver...). Clear the "Set Target Cell" edit box and in the "By Changing Cells" edit box, enter a range for the solution. The Solver Parameters dialog box should now look something like this:
Fig. 2 - Solver Parameters Dialog Box
|
Next set the constraints by clicking on the Add button. When the Add Constraints dialog box opens, fill it out as shown below (adjusted if necessary for the way you've set up your spreadsheet):
Fig. 3 - Add Constraint Dialog
Box
|
This ensures that Excel Solver is constrained to find a solution that matches the constants on the right hand side of the simultaneous linear equations. Now click on OK to return to the Solver Parameters dialog box:
Fig. 4 - Completed Solver Parameters Dialog Box
|
Lastly click on the Solve button. You should see the following:
Fig. 5 - Solver Results Dialog Box
|
Click on the OK button to keep the solution. The spreadsheet should now look like this:
Fig. 6 - Specimen Worksheet After Running Excel Solver
|
As you can see, Excel Solver has updated the spreadsheet and replaced the contents of F3:F7 with values of u, v, w, x and y that solve the simultaneous linear equations.
Using Matrix Functions to Solve Simultaneous Linear Equations
As an alternative to using Excel Solver, you can use the matrix functions mmult and minverse.
When using mmult and minverse we need only use the coefficients of each equation. (Coefficients are the numbers on the left hand side of each
equation. As an example, for the second equation, the coefficients are 1, 2, 1, -0.5 and 2.) With this in mind, set up a spreadsheet where the coefficients for each equation are entered into consecutive rows. Also, enter the constants from the right hand side of each equation into a vertical block of cells. You'll end up with a spreadsheet like this:
Fig. 7 - Specimen Worksheet Before Using Matrix Functions
|
Now select an empty area on the worksheet. Make sure the area is exactly five rows high and one column wide. Click on the formula bar and enter "=mmult(minverse(A3:E7),G3:G7)" as shown below. (You may need to change the ranges to be consistent with the layout of your spreadsheet.)
Fig. 8 - Excel Formula Bar
|
When you've finished typing in the formula, don't press Enter. Press Ctrl + Shift + Enter instead. (That is, press the Ctrl, Shift and Enter keys together.) This enters the formula as an array which is the same size as the solution of the simultaneous linear equations. You should see the solution of the simultaneous linear equations in the cells you selected:
Fig. 9 - Specimen Worksheet After Using Matrix Functions
|
Matrix functions are slightly more flexible than Excel Solver. If you change a coefficient or constant, the solution is updated immediately. If you use Excel Solver and make a change, you'll have to re-run Solver to calculate a new solution. A disadvantage of using matrix functions is that they restrict you to solving linear equations. This is where using Excel Solver can be an advantage; you may be able to solve non linear equations as well as linear ones.
Download the spreadsheet for this tutorial. (Note: you'll need Excel 95 or later to use this spreadsheet.)
|
Email this article to a friend
Write to the editor
 UltraSleuth
|
| Compare, check, analyze and document your Excel projects with UltraSleuth.
|
|
Gain confidence in your spreadsheet results
|
|
|
Speed up your spreadsheet development
|
|
|
Understand third party spreadsheets
|
|
|
Create an audit trail
|
 Learn More...
|
|
|
Other Tutorials
|
