242

Chapter 14: The Solver Tool for Constrained Optimization

Figure 172: The second constraint

Continue with constraint definitions. After defining the last constraint,

execute the dialog by clicking on the button OK (see Figure 172).

Note:

The constraints are shown in the area “Subject to the Constraints” as

shown in Figure 173.

Figure 173: The constraints for the Solver

CHOOSING ALGORITHM OPTIONS

14.3

You need to choose the options for the analysis. So, click on the button

“Options.” The dialog shown in Figure 174 opens.

243

Statistical Analysis with Excel

You may want to increase the iterations to 10,000. If you want to relax

the requirements for preciseness, increase the value of “Precision” by

removing some post-decimal zeros.

“Save Model” is used to save each optimization model. You can define

several optimization problems in one workbook. The other options are

beyond the scope of this book. Click on the button “Continue.”

Figure 174: Options in the Solver Add-In

Running the Solver

Execute the procedure by clicking on the button “Solve.”

The following output can be read from the spreadsheet.

• the optimized value of the Objective Function (that is, the value of

the formula in the cell defined in the box “Set Target Cell”)

244

Chapter 14: The Solver Tool for Constrained Optimization

is the combination of the choice variables (that is, those whose

•

value is obtained from the cells defined in the dialog area “By

Changing Cells”)

Figure 175: The completed constrained optimization dialog

245

Statistical Analysis with Excel

INDEX

AVEDEV ........................................156, 157

#

AVERAGE....................66, 89, 90, 106, 155

µ 122

AVERAGEA...........................................106

σ2 122

B

A

BETADIST .............113, 115, 132, 140, 141

A1................................................ 25, 28, 232

BETAINV .......................133, 134, 140, 141

ABS......................................................... 153

BINOMDIST...................................113, 140

ADD“IN.................................................. 161

BIVARIATE ...........................................169

ADD-INS . 17, 161, 163, 165, 170, 176, 178,

C

187, 190, 195, 199, 205, 219, 231, 234,

236, 240

CDF 109, 110, 111, 112, 113, 114, 115, 119,

ADD“INS INSTALLED WITH EXCEL161

120, 121, 123, 125, 127, 128, 129, 130,

AND.................... 35, 52, 109, 144, 161, 169 132, 133, 134, 136, 137, 138, 140

ANOVA . 129, 156, 163, 183, 187, 203, 205, CELL.............................................25, 52, 89

206, 207, 208

CELL REFERENCE .................................25

AUDITING ........... 17, 76, 78, 79, 80, 81, 84

CELLS...........................................15, 16, 52

AUTOCORRECT .....................................17

CENTRAL TENDENCY ..........................89

AUTOFORMAT.......................................16

CHIDIST ......... 113, 115, 130, 131, 140, 141

246

Index