For example,

Maximize/Minimize /other (over the choice parameters Xc ¦) Y = f(X1, X2 ¦)

Subject to the inequality constraints:-

C1 = ¦.C2 >=¦ , C3 <= ¦

238

Chapter 14: The Solver Tool for Constrained Optimization

The Add-In “Solver” can solve such models. In the Solver dialog (user-

input form), the options equate with the function above. The “mapping” of

the dialog to different parts of the optimization function is shown in the

next table.

Table 40: The “Solver”

Option in the Solver dialog Equate to the following part of the optimization

¦. function¦

Equal to:” The optimization function

Set Target Cell” Function that needs to be optimized

By Changing Cells” The choice parameters Xc¦.

Subject to the Constraints” The constraints C1, C2, ¦

The Solver permits constraints of inequality. This makes the solver

extremely powerful.

Choose the menu option TOOLS/ADD-INS. Choose the Add-In “Solver” as

shown in Figure 168. Execute the dialog by clicking on the button OK.

Figure 168: Selecting the Solver Add-In

239

Statistical Analysis with Excel

You have activated the “Analysis ToolPak.” If you go to the menu

TOOLS, you will see the option “SOLVER“” this option was not there

before you accessed the Add-In. Please define a sample problem and try it

on an Excel workbook37.

Access the feature through the menu path TOOLS/SOLVER. The dialog

shown in Figure 169 opens. The “Target Cell” contains the formula for

the function you are attempting to optimize.

The “Equal to” area is where you choose the optimization criterion“

” Maximization (Max)

” Minimization (Min)

I do not supply the sample data for most of the examples in chapter 42 to chapter 46.

37

My experience is that many readers glaze over the examples and do not go through

the difficult step of drawing inferences from a result if the sample data results are

the same as those in the examples in the book.

240

Chapter 14: The Solver Tool for Constrained Optimization

Figure 169: Setting the target cell

The choice parameters are the numbers the algorithm plays around with

to find the max/min.

You have to tell Excel about the cells that contain these parameters. One

can do it manually, or, an easier option is to click on the button “Guess.”

Excel automatically chooses all the cell references for use in the formula

in J10 (the target cell/objective function). This is illustrated in Figure

170.

241

Statistical Analysis with Excel

Figure 170: Selecting the criterion for optimization

ADDING CONSTRAINTS

14.2

The optimization function has been defined, as have the “choice

parameters.” At this stage, you have to add the constraints.

Click on the button “Add” and write in a constraint as shown in Figure

171.

Figure 171: The first constraint

After defining the first constraint, click on the button “Add” (see Figure

171.) Write the second constraint” see Figure 172.