Excel Solver
From DDL Wiki
Contents |
What is Solver?
Solver is an add in tool found in Microsoft Excel. It is primarily used to determine the maximum or minimum value of a problem, which is assigned to a cell in an Excel spreadsheet. In optimization, Excel Solver is a very powerful tool. The Solver is a combination of a numerous amount of programs. These programs functions are composed of among which are the Graphical User Interface (GUI), an algebraic modeling language (for example, GAMS), and optimizers for linear, non-linear, and integer programs. With these powerful tools put together, Solver is able to find an optimal value for a target cell in a worksheet.
How Solver Works
Excel Solver is a tool that falls under the category of what-if analysis. In optimization, this means that Solver is the type of tool that will determine what would happen in a problem's outcome if one parameter was changed. Within one spreadsheet, there are essentially three basic types of cell.
Target Cell: This is typically one cell in a spreadsheet. It is usually a function that inputs other values that are found within the same spreadsheet. These cells fall within the next two categories.
Adjustable Cells: These cells must be given an initial value. When Solver is run, it will change the values of these cells in order to reach the optimum solution in the target cell.
Constraint Cells: These are set values that will restrict values that Solver will use. They can refer to other cells in the spreadsheet.
Example
The following provides an example of how Solver could be used. Suppose the goal is to minimize latex($$ f(x) = \pi r^2 L \rho $$). The following is an illustration of how this problem could be executed in Excel Solver (where cell B5 is the location of the objective function):
Cell B% inputs values for the radius, length, and rho which are specified in cell D12, D15, and D17 respectively under the design parameter category. This set up also incorporates some constraints, designated in cells D8 and D9. The two constraints represented by those cells are [[latex($$ \sigma(yield) = \frac{My}{I} \leq 0$$)]] and [[latex($$ \delta(maximum) = \frac{F L^3}{3 E I} \leq 0$$)]].
The Solver tool is an Add-In of Microsoft Excel located under "Tools" of the main menu. The three things that must be specified are the target cell, the adjustable cells, and the constraint cells. The target cell should simply reference the cell in which the objective function was inserted. This is the cell that solver wants to optimize. In order for it to optimize well, Solver must know which variables of the target function it is allowed to change. In this particular example, Solver is allowed to change the radius, latex($$ r$$). However, in this example, Solver has also been specified to comply to some constraints, of which the cells have been specified. Once the set up is complete, click Solve, and the worksheet will be update the cells with an optimum solution in the target cell as well as update the values that are allowed to change, which in this case is the radius. With Solver, more constraints can be added as well as more changeable parameters to find different optimal values.
