Operations Research Models and Methods / Methods / Nonlinear Programming /

 Define the Variables

 We begin by defining the decision variable vector. Since a problem may involve more than one function defined on the same variables, we make the process of defining the vector of variables independent of the function definitions. As we will see in the next section, the variable vector must be defined before the function. To create a new vector of variables, choose the Add Variable item from the menu. The range defining the variable will start at the cell where the cursor lies when the dialog appears, so before choosing the menu option have the cursor placed on a vacant cell where the information is to be stored. The program displays the dialog below.

The text entry field labeled Vector Location holds the Excel cell address where the variable values will be stored. In the case above the cursor was in the upper left corner of the worksheet, so the location shows the address of the cell at the upper left corner, \$A\$1. This field is locked so it cannot be changed. If the location is wrong, simply click on the Cancel button. The cursor can then be placed at the desired cell before calling the dialog.

The Vector Name refers to the variable in the nonlinear programming formulation. It should be a simple letter or small number of letters. The name cannot resemble the address of a cell such as X1. Excel will not allow this and an error will occur. The program has built-in names: X, Y, Z, P, S, T, U, V and W (R is not allowed). These will be automatically presented when the dialog is called. When they are exhausted the letters are doubled: XX, YY etc. The Vector Name field can be changed by the student to any name that Excel will accept.

The Number of Elements field holds the number of components of the decision vector. The number can range from 1 to 9.

 When the OK button is clicked, the array is placed on the worksheet as shown in the figure. The range defining X starts at cell A1 and goes to B5. The range B2:B5 is given the Excel name X. When X is used in a function definition, the numbers stored in these cells hold the values of the decision variables. The figure shows three variables X, Y and Z defined for different ranges of the worksheet. The scope of a name is an entire workbook, so a name used on one worksheet can refer to a range on another. A range may be cut and pasted to a different location. Clearing the field where a variable is defined or replacing the contents of the cells does not change the range defined by a name. A name can be deleted through the Insert:Name:Define dialog.

Next

Operations Research Models and Methods
by Paul A. Jensen and Jon Bard, University of Texas, Copyright by the Authors