Goal Seek and Solver

1. Goal Seek and Solver

3. Goal seeking helps you • Find a specific value for a target cell by adjusting the value of one other cell whose value is allowed to vary.

4. 1 On the Tools menu, click Goal Seek. • 2 In the Set cell box, enter the reference for the cell that contains the formula you want to resolve. • 3 In the To value box, type the result you want. • 4 In the By changing cell box, enter the reference for the cell that contains the value you want to adjust.

6. Solver • Solver is given a specific target cell whose value is to be • maximized, • minimized, or • made equal to a specific value. • It is also given a set of cells whose value is to be varied to accomplish the desired optimization or solution, together with constraints on their values, if any. • Then it tries to obtain a solution by iteratively varying the values of the variable cells.

7. The cells you select must be related through formulas on the worksheet. • If not related, changing one cell will not change the other. • As an example, Solver can be used to find the best choice of advertising expenditures to generate maximum profit.

9. Define and solve a problem by using Solver • 1 On the Tools menu, click Solver. • 2 In the Set Target Cell box, enter a cell reference or name for the target cell. The target cell must contain a formula.

10. 3 To have the value of the target cell be as large as possible, click Max. • To have the value of the target cell be as small as possible, click Min. • To have the target cell be a certain value, click Value of, and then type the value in the box.

11. 4 In the By Changing Cells box, enter a name or reference for each adjustable cell, separating nonadjacent references with commas. • The adjustable cells must be related directly or indirectly to the target cell. • You can specify up to 200 adjustable cells. • To have Solver automatically propose the adjustable cells based on the target cell, click Guess.

12. 5 In the Subject to the Constraints box, enter any constraints you want to apply. • 6 Click Solve. • 7 To keep the solution values on the worksheet, click Keep Solver Solution in the Solver Results dialog box. • To restore the original data, click Restore Original Values.

13. Tip: • You can interrupt the solution process by pressing ESC. • Microsoft Excel recalculates the worksheet with the last values found for the adjustable cells.

14. To add a constraint in Solver • 1 On the Tools menu, click Solver. • 2 Click Add. • 3 In the Cell Reference box, enter the reference or name of the cell range for which you want to constrain the value.

15. 4 Click the relationship ( <=, =, >=, Int, or Bin ) that you want between the referenced cell and the constraint. • If you click Int, "integer" appears in the Constraint box. • If you click Bin, "binary" appears in the Constraint box. • 5 In the Constraint box, type a number, a cell reference or name, or a formula.

16. 6 To accept the constraint and add another, click Add. • To accept the constraint and return to the Solver Parameters dialog box, click OK.

17. Notes • You can apply the Int and Bin relationships only in constraints on adjustable cells.

18. When the Assume Linear Model check box on the Solver Options dialog box is selected, there is no limit on the number of constraints. • For nonlinear problems, each adjustable cell can have the following constraints: a binary constraint; an integer constraint plus upper, lower, or both limits; or upper, lower, or both limits; and you can specify an upper or lower limit for up to 100 other cells.

19. Change or delete a constraint in Solver • 1 On the Tools menu, click Solver.2 In the Subject to the Constraints box, click the constraint that you want to change or delete.3 To delete the constraint, click Delete.To change the constraint, click Change, and then make the changes you want.

20. Set Solver solution time and iterations • 1 On the Tools menu, click Solver. • 2 Click Options. • 3 In the Max Time box, type the number of seconds that you want to allow for the solution time. • 4 In the Iterations box, enter the maximum number of iterations that you want to allow.

21. Notes • If the solution process reaches the maximum time or number of iterations before Solver finds a solution, Solver displays the Show Trial Solution dialog box.

22. Set the degree of precision and convergence in Solver • 1 On the Tools menu, click Solver. • 2 Click Options. • 3 In the Precision box, type the degree of precision that you want; the smaller the number, the higher the precision.

23. 4 In the Convergence box, type the amount of relative change you want to allow in the last five iterations before Solver stops with a solution; the smaller the number, the less relative change that is allowed.

24. To set the tolerance in Solver • 1 On the Tools menu, click Solver. • 2 Click Options. • 3 In the Tolerance box, type the percentage of error you want to allow in the solution.

25. When to start from different initial solutions • For nonlinear problems, it can be helpful to try different starting values for the adjustable cells, especially if Solver has found a solution that is significantly different from what you expected. • You can also reduce the solution time by setting the adjustable cells to values that you suspect are close to optimal.

26. For linear models (that is, when the Assume Linear Model check box on the Solver Options dialog box is selected), the initial values of the adjustable cells don't affect the final values or the solution time.