Microsoft Office Tutorials and References

In Depth Information

**Specifying variable cells**

Figure 18-25
The Solver lists the constraints and uses defined cell and range names whenever

possible.

Notice that two of the constraints have range references on the left side of the comparison

operator. The expression $D$2:$D$7>=$G$15 stipulates that the value of each cell in D2:D7

must be 6 or greater, and the expression $F$2:$F$7<=$G$14 stipulates that the value of

each cell in F2:F7 must be no greater than 33.30 percent. Each of these expressions is a

shortcut way of stating six separate constraints. If you use this kind of shortcut, the

constraint value on the right side of the comparison operator must be a single cell reference, a

range of the same dimensions as the range on the left side, or a constant value. We could

have entered constant values as constraints, but this way we can change the numbers on

the spreadsheet (where we can see them) and rerun the Solver.

After completing the Solver Parameters dialog box, click Solve. In the advertisement

campaign example, the Solver succeeds in finding an optimal value for the objective cell while

meeting all the constraints and displays the dialog box shown in Figure 18-26. The values

displayed on your worksheet at that time result in the optimal solution. You can leave these

values in the worksheet by selecting the Keep Solver Solution option and clicking OK, or

you can restore the original values by selecting the Restore Original Values option and