The second item on the Teach NLP
menu presents the function dialog shown below. A function has
a location, given by a cell address, and a name. The cell address
is determined by the cursor placement on the worksheet when
the dialog appears. The Function Location text field
is locked, so have the cursor placed at the desired location
when the menu item is selected. A function definition may take
up a number of cells on the worksheet, so leave an appropriate
amount of empty space below and to the right of the cursor
location. The program gives a warning before the function overwrites
cells that contain information.
In the Function Name field, the program presents one
of nine preset names: F, G, H, J, K, L, M, N, and O. When these
names have all been used, the letters are repeated, FF, GG,
etc. When all the two letter pairs are generated, three letters
are used. The student can change the suggested name. The name
is used by the program to identify various regions on the worksheet.
Every function depends on cells on the worksheet that hold
variable values. For this dialog, we expect that a decision
vector has been previously defined using the Add
Variable menu command. The name of the variable range is
placed in the Decision Vector text box.
The check boxes in the lower half of the dialog,
determine the data structure that is placed on the worksheet.
The figure below shows the worksheet areas created by the dialog.
The range D1:L6 is first cleared by the program. If cells in
the range are not empty, the program gives a warning. If the
student does not want the cells to be overwritten, he or she
can cancel construction of the function and choose a new location.
The function name is placed at the top left cell
of the region and the Excel formula computing the function
is placed in the cell immediately below. The cell with the
red text is given the Excel name that is the name of the function.
In this case cell D2 has the name F. The remainder of the range
defines data and results areas.
The second column, column E, names the decision
variable and provides indices for the linear coefficient vector.
The third column is for the linear coefficients of the function.
The values in the range F2:F5 were assigned randomly for this
case. They can of course be changed. At the bottom of the column,
the matrix product cx is computed. Cell F6 is colored
yellow to indicate that it contains a formula. Although areas
colored yellow may be changed by the student, changes should
be done very carefully.
Column G begins the presentation of the Q matrix
that holds the coefficients for the quadratic terms of the
function. The coefficients are stored in the range H2:K5. Cell
H6 holds the result of the matrix computation: ,
which is the contribution of the quadratic terms to the objective
function. The range L2:L5 holds the results of the computation Qx.
These results are necessary for computing the number in H6.
The value in cell D2 is the sum of the linear and quadratic
computations. The value depends on the contents of the decision
variable X. The numbers in the variable range B2:B5 are arbitrarily
entered for this illustration.