University of Pittsburgh: Department of Chemical and Petroleum Engineering


Example of Using Solver in Excel

You may want to solve a system of nonlinear equations in your homework. You can do this in a wide variety of ways, but a simple way to find a solution is to use Microsoft Excel's Solver tool. Be warned that Solver is NOT a very robust tool and may fail to find solutions! In general, finding roots to nonlinear functions is a very difficult problem. Excel does a poor job compared to more powerful programs such as Matlab, but it will suffice for simple problems.

As a very simple example, let's suppose you want to solve a system of linear equations using Solver. (This is a bad idea, because there are exact methods for linear equations, but this is just an example.) Consider

3x + y + 2 = 0
x + 2y + 5 = 0
We can use linear algebra to find the solution, which is x = 0.2, y = -2.6. You can use the Solver tool in Excel to find the solution to the above equations.

Save the sample spreadsheet to disk and open it with Excel. You should see that cells A2 and B2 hold the initial guess values of x and y, respectively. Cells C2 and D2 hold the formulas for the two equations shown above, and Cell E2 holds the objective function, which in this case is just the sum of the two equations, C2 and D2.

Now click on Tools, Solver. (NOTE: If Solver does not appear on your Tools menu, then you have to add it through the Tools, Add-Ins menu item.) The Target Cell should be $E$2. Equal to: should be set to Value of: 0. The By Changing Cells should be $A$2:$B$2. This means that solver will attempted to set the value of $E$2 to zero by adjusting the variables in cells $A$2:$B$2. Now, in this case just setting the objective function to zero does not lead to a unique solution. We can add constraints to the solution to make sure that both equations are in fact zero. It is sufficient to set a constraint on one of the equations also being zero and then the fact that the sum must also be zero will require the second equation to also be zero. Hence, in Subject to the Constraints: box you should see $C$2 = 0. You can add the additional requirement that $D$2 = 0, but this is redundant.

If you click on the Solve button you should notice that the values in Cells A2:E2 change, with Cells C2:E2 being very close to zero. You should see a dialog box. Make sure Keep Solver Solution is checked and click OK. You should have values very close to 0.2 and -2.6 in Cells A2 and B2, respectively.




HOME