Page 678 - Excel 2013 All-in-One For Dummies

660 Using the Solver
When setting up the problem for the Solver add-in in your worksheet, define the following items:
✦ Objective cell: The target cell in your worksheet whose value is to be maximized, minimized, or made to reach a particular value. Note that this cell must contain a formula.
✦ Variable cells: The changing cells in your worksheet whose values are to be adjusted until the answer is found.
✦ Constraint cells: The cells that contains the limits that you impose on the changing values in the variable cells and/or the target cell in the objective cell.
After you finish defining the problem with these parameters and have the Solver add-in solve the problem, the program returns the optimum solution by modifying the values in your worksheet. At this point, you can choose
to retain the changes in the worksheet or restore the original values to the worksheet. You can also save the solution as a scenario to view later before you restore the original values.
You can use the Solver add-in with the Scenario Manager to help set up a problem to solve or to save a solution so that you can view it at a later date. The changing cells that you define for the Scenario Manager are automati- cally picked up and used by the Solver when you select this command, and vice versa. Also, you can save the Solver’s solution to a problem as a sce- nario (by clicking the Save Scenario button in the Solver dialog box) that you can then view with the Scenario Manager.
Setting up and defining the problem
The first step in setting up a problem for the Solver to work on is to create the worksheet model for which you will define the objective cell, variables cells, and the constraint cells.
Keep in mind that the Solver is an add-in utility. This means that, before
you can use it, you need to make sure that the Solver add-in program is still loaded, as indicated by the appearance of the Solver button in the Analysis group at the end of the Data tab on the Ribbon. If this button is missing, you can load Solver by opening the Add-Ins tab of the Excel Options dialog box (Alt+FTAA) and then clicking the Go button after making sure that Excel Add- Ins is displayed in the Manage drop-down list box to its immediate left. Then, select the Solver Add-in check box in the Add-Ins dialog box to put a check mark in it before you click OK to close the dialog box and reload the add-in.
To define and solve a problem with the Solver add-in after you’ve loaded the add-in and have created your worksheet model, you follow these steps:
1. Click the Solver command button in the Analysis group at the end of the Ribbon’s Data tab.