3, that are typically describing limits on the supply of raw materials or the demand for finished products. The third type is the objective cell. This cell contains a formula that should be minimized or maximized in adjusting the problem variables. The library includes a subroutine named EKKSMDL that enables you to load or update a model in spreadsheet format. This enables you to integrate existing models, in spreadsheet format, with applications that use Optimization Library modules. You can enter linear, quadratic, and mixed-integer programming models with EKKSMDL by creating the model in a spreadsheet, saving the model to a file, and then running a program that includes a call to EKKSMDL. For a detailed description of the EKKSMDL subroutine, see "EKKSMDL - Specify a Model in Spreadsheet Format". For examples of problems in spreadsheet format, see linear programming, quadratic programming, and mixed-integer programming below.
Optimization problems are defined in the Lotus 1-2-3 Version 3 spreadsheet by three types of cells. The first is the problem variables or adjustable cells. These contain values that can be changed to satisfy problem constraints and to yield an optimal solution. The second type is constraint cells. These cells contain logical formulas, such as ( 5*A1 ) 3, that are typically describing limits on the supply of raw materials or the demand for finished products. The third type is the objective cell. This cell contains a formula that should be minimized or maximized in adjusting the problem variables.
The following examples show how to get started using EKKSMDL with linear, quadratic, and and mixed-integer programming problems. For more information about the formulation of problems in Lotus 1-2-3, see "Creating and Defining an Optimization Problem with Lotus 1-2-3".
The linear programming problem:
minimize 2a + 3b + 4c + 5d
subject to:
a + b = 1
c + d = 2
-a -c = -2
-b -d = -1
where:
a 
0
b 0
c 0
d 0
can be entered into Lotus 1-2-3 by using cells A1, B1, C1, and D1 as adjustable cells. Since adjustable cells must be nonempty, the first step is to load 0 into cells A1, B1, C1, and D1. The second step is to create constraint cells for the problem constraints. The range A2...A9 can be loaded with constraints as follows:
A2: +A1 + B1 = 1 A3: +C1 + D1 = 2 A4: -A1 - C1 = -2 A5: -B1 - D1 = -1 A6: +A1 = 0 A7: +B1 = 0 A8: +C1 = 0 A9: +D1 = 0
You can create the objective function by loading the formula (2*A1) + (3*B1) + (4*C1) + (5*D1) into cell A10, and you can save the file for use by Optimization Library modules.
The names of the adjustable cells, the constraint cells,and the objective cell are passed to library modules in application program. Character control variables Cssolution, Csconstrts, and Csobjective should be set before the call to EKKSMDL to indicate the names of these cells. The following code fragment can be used to solve the linear programming problem described above:
CALL EKKCGET(RTCOD,DSPACE,OSLC,OSLCLN) CSOBJECTIVE = 'A10' CSCONSTRTS = 'A2...A9' CSSOLUTION = 'A1...D1' CALL EKKCSET(RTCOD,DSPACE,OSLC,OSLCLN) C UNIT = 45 ACTION = 1 TYPE = 1 RES = 0 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES) CALL EKKSSLV(RTCOD,DSPACE,1,2) CALL EKKPRTS(RTCOD,DSPACE) ACTION = 2 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES)
The parameter action indicates that the file will be read, rather than updated. The parameter type indicates that the file is a Lotus 1-2-3 file. The parameter unit indicates that the Lotus 1-2-3 worksheet file will be attached to unit 45. The final parameter, res, is reserved; its value must be 0. The column names appearing in the output from EKKPRTS are the same as the names of the adjustable cells, and the row names are the same as the names of the constraint cells.
The final call to EKKSMDL updates the solution values in the Lotus 1-2-3 worksheet file.
Consider the problem of finding a point in the trapezoidal region defined by 1 (x + y) 2 , 0 x , 0 y, which is closest to the point (2,2). The problem can be formulated as:
minimize ( x - 2 )2 + ( y - 2 )2
subject to: 1 x + y 2
where:
xyAs with the linear programming example, an initial value, such as 0, should be loaded into the adjustable cells A1 and A2. The constraints can be loaded into the range B1...D1 as follows:
B1: 1 <= A1 + A2 <= 2 C1: 0 <= A1 D1: 0 <= A2
The logical formula in cell B1 involves two inequalities defining two different constraints. EKKSMDL will merge these into a single row with an upper and a lower bound. You can create the objective function by loading the formula (A1-2)2 + (A2-2)2 into cell A3, and the file can be saved for use by an application program. You can use the following code fragment to solve the quadratic programming problem:
CALL EKKCGET(RTCOD,DSPACE,OSLC,OSLCLN) CSOBJECTIVE = 'A3' CSCONSTRTS = 'B1...D1' CSSOLUTION = 'A1...A2' CALL EKKCSET(RTCOD,DSPACE,OSLC,OSLCLN) C UNIT = 45 ACTION = 1 TYPE = 1 RES = 0 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES) CALL EKKQSLV(RTCOD,DSPACE,1,1) CALL EKKPRTS(RTCOD,DSPACE) ACTION = 2 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES)
The output from EKKPRTS includes a column named EKKCONST with lower and upper bounds of 1 and a cost of 8. EKKSMDL adds this column to incorporate an additive constant into the objective function. The additive constant ensures that the library solver will give the correct optimal objective value for the problem as it appears in the spreadsheet.
The final call to EKKSMDL updates the solution values in the Lotus 1-2-3 worksheet file.
Consider the mixed-integer programming problem:
minimize -2a - 3b
subject to: a - (5 b / 7) ( 5 / 2 )
where: a 0, b 0, and a and b must be integers.
As before, you should load initial values into the adjustable cells A1 and A2. You can load the objective function (-2*A1) - (3 *A2) into cell A3. The linear constraints can be defined:
B1: +A1 - (5/7)*A2 <= (5/2) B2: +A1 = 0 B3: +A2 = 0 D2: +A1 <= 1000 D3: +A2 <= 1000
The upper bound constraints for A1 and A2 are needed to indicate that these cells contain general integer variables, rather than 0-1 integer variables. To impose integrality on A1 and A2, a range of formula cells is created. Each cell in the range corresponds to one integer variable. The cells can be defined:
C1: +A1 C2: +A2
The range C1...C2 can now be used to define a special ordered set (SOS) of type 4 (general integer variables). The following code fragment can be used to solve the problem:
CALL EKKCGET(RTCOD,DSPACE,OSLC,OSLCLN) CSOBJECTIVE = 'A3' CSCONSTRTS = 'B1...B3,D2...D3' CSSOLUTION = 'A1...A2' CSEKKSOS4 = 'C1...C2' CALL EKKCSET(RTCOD,DSPACE,OSLC,OSLCLN) C UNIT = 45 ACTION = 1 TYPE = 1 RES = 0 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES) CALL EKKMSLV(RTCOD,DSPACE,1,0,0) CALL EKKPRTS(RTCOD,DSPACE) ACTION = 2 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES)
The final call to EKKSMDL updates the solution values in the Lotus 1-2-3 worksheet file. The additive constant ensures that the library solver will give the correct optimal objective value for the problem as it appears in the spreadsheet.
This section describes, in detail, the use of Lotus 1-2-3 in formulating optimization problems. The first subsection describes range sets and shows how to use them to indicate the location of problem data in the worksheet. The next three subsections tell how a linear or quadratic problem should be formulated in the spreadsheet by discussing adjustable cells, constraint cells, and the objective cell. The next subsection discusses creating names for problem variables and constraints The next subsection discusses the specification of mixed integer programming problems in a spreadsheet format. Integer variables are identified by membership in special ordered sets, and these provide the key to specifying a mixed integer programming problem. Finally, the functionality of EKKSMDL is summarized, and restrictions to Lotus 1-2-3 format are identified
Range sets are used to specify the cells that contain problem variables, problem constraints, the objective function, and special ordered sets. A range set is a character string containing a list of ranges, separated by commas. The following code fragment causes cells A1, B1, C1, and D1 to be adjustable in a subsequent call to EKKSMDL:
CALL EKKCGET(RTCOD,DSPACE,OSLC,OSLCLN) CSSOLUTION = 'A1...C1,D1' CALL EKKCSET(RTCOD,DSPACE,OSLC,OSLCLN)
You can also use range names in range sets. If sol is the name given to the range A2...A5 in the Lotus 1-2-3 worksheet file, the assignment:
CSSOLUTION = 'sol'
is equivalent to:
CSSOLUTION = 'A2...A5'
You can use full cell names in range sets. Lowercase letters can also be used. The following assignments are examples of legal assignments:
CSSOLUTION = 'A:A5...B:B1' CSSOLUTION = 'a1...A:a2,c1,d1...f5' CSSOLUTION = 'SOL,BIG,LITTLE'
The character strings defining range sets can have up to 80 characters.
The seven character control variables Cssolution, Csconstrts, Csobjective, Csekksos1, Csekksos2, Csekksos3, and Csekksos4 can have range sets assigned to them. While the syntax for valid assignments is the same for all of these control variables, the assigned range sets are processed in different ways during the call to EKKSMDL. The range set assigned to Cssolution describes a single, possibly disjoint, set of cells. This is also true for Csconstrts. In contrast, each range in a range set assigned to Csekksos1, Csekksos2, Csekksos3, or Csekksos4 describes a different set. Each set of cells describes a different special ordered set. The range set assigned to Csobjective should have only one cell. The default value for all of these control variables is blank.
Adjustable cells correspond to the mathematical programming problem variables. These cells contain values that can be changed to satisfy constraints and yield an optimal value of the objective function. You can create them by loading values into a set of cells; you can define the cells to be adjustable by assigning a range set to character control variable Cssolution. The range set should contain a list of the cells or ranges, separated by commas, containing values you can change. These cells are then transformed by EKKSMDL into problem variables. For more information on range sets, see "Using Range Sets".
Adjustable cells must be nonempty, unprotected cells. They must not contain formulas or labels; they are ignored if they do. The EKKSMDL interface does not allow ranges to span multiple worksheet files. Formulas must not refer to cells in files other than the file attached to unit. All adjustable cells have default upper bounds of +infinity and lower bounds of -infinity. You can use constraints to tighten the default bounds.
Constraint formulas are logical formulas involving linear functions, equalities, and inequalities. Lotus 1-2-3 assigns a value of 1 to logical formulas that are satisfied, and a value of 0 to those unsatisfied. The following is an example of a constraint formula:
(1/12)*A1 + 2*B1 <= 5
Either or both of the cells A1 and B1 can be adjustable. Constraint formulas can refer to cells containing linear formulas. They cannot, however, contain references to cells containing logical or nonlinear formulas. Constraint formulas can involve the Lotus 1-2-3 @Functions @SUM and @SUMPRODUCT. The @SUM function returns the sum of its operands. For example, @SUM(A3,5,B3...B4) returns the value of 11 if cell A3 contains the value 1, B3 contains the value 2, and B4 contains 3. If cells B3 and B4 are adjustable, EKKSMDL transforms the constraint:
@SUM(A3,5,B3...B4) <= 10
into:
B3 + B4 <= 4
This is because the value of A3 is assumed to be the constant 1, and the sum (1+5) is subtracted from both sides of the inequality to simplify the constraint.
The Lotus 1-2-3 function @SUMPRODUCT can also be used to define constraints. This function has ranges or cells as operands, and all operands must have the same size and shape. It returns the sum of the products of corresponding cells in the operands. For example, @SUMPRODUCT(A1...A2,B1...B2) returns the value (A1*B1) + (A2*B2). If A1 and A2 are adjustable and if B1 and B2 contain 2 and 3, respectively, the constraint:
@SUMPRODUCT(A1...A2,B1...B2) <= 10
is transformed into:
2*A1 + 3*A2 <= 10
during a call to EKKSMDL. Range names can be used as parameters for the @Functions @SUM and @SUMPRODUCT. For example, if the name sol refers to the range A1...A2 and row1 refers to B1...B2, the following:
@SUMPRODUCT(sol,row1) <= 10
is transformed into:
2*A1 + 3*A2 <= 10
during a call to EKKSMDL.
Constraints can be combined by putting two or more equalities or inequalities in the same logical formula. For example, the logical formula:
0 <= A1 <= B1 <= 10 (where A1 and B1 are adjustable)
is transformed by EKKSMDL into column bounds and a constraint. The constraint 0 A1 is transformed into a lower bound on the value of A1. The constraint B1 10 is transformed into an upper bound on the value of B1. The constraint A1 B1 is transformed into the constraint (A1-B1) 0. The constraint:
1 <= @SUMPRODUCT(sol,row1) <= 5
is transformed by EKKSMDL into a single constraint with a lower bound of 1 and an upper bound of 5.
Constraint cells can be created by loading logical formulas like the ones described above into a set of cells. The cells are defined to be constraints by assigning a range set to character control variable Csconstrts. The range set should contain a list (separated by commas) of the cells containing logical formulas modelling problem constraints. For more information on range sets, see "Using Range Sets". If EKKSMDL encounters a nonlinear constraint, it issues a warning message and the constraint is ignored.
You can create an objective function by loading a linear or quadratic function into a cell. The cell should not be protected. You then define that cell to be the objective function by assigning the name of the cell to character control variable Csobjective. If the objective function is quadratic, the problem must be solved by EKKQSLV or EKKQPAR, and EKKDSCM must be called before EKKSMDL to create 5 or more internal matrix blocks. If EKKSMDL encounters a nonlinear and nonquadratic function, it issues a warning message, and a zero objective function is used.
EKKSMDL assigns spreadsheet cell names to columns and rows. The name for each column is the name of the corresponding adjustable cell. For example, if the adjustable cells are A1, B1, and C1, the names of the columns will be A:A1, A:B1, and A:C1. The names given to rows are cell names for cells containing constraint formulas. Formulas containing more than one constraint are transformed during the call to EKKSMDL into more than one row; therefore, row names are augmented to give each row a unique name. For example, if cell C5 contains the formula A1 (B1 + C1) D1, the row names for the two generated constraints would be A:C5 and A:C5.1. Storage for row and column names is allocated through a call to EKKNAME; therefore, control variables that apply to EKKNAME also apply to EKKSMDL.
Special ordered sets of types 1, 2, and 3 are created by loading linear formulas into cells. You define these sets by assigning range sets to the character control variables Csekksos1, Csekksos2, or Csekksos3.
To create a special ordered set of type 1 that includes adjustable cells A1, A2, and A3, you can load the cells B1, B2, and B3 as follows:
B1: +A1+1 B2: +A2+2 B3: +A3+3
The reference row entries are 1 for A1, 2, for A2, and 3 for A3. Reference row entries are required for each member of a special ordered set of type 1, 2 or 3, and the value of the reference row entry is indicated by the additive constant. The formula describing each member should include a reference to exactly one adjustable cell. The additive constant becomes the reference row entry.
To create a special ordered set of type 4 (general integer variables) that includes adjustable cells G1, G2, and G3, you can load the cells H1, H2, and H3 as follows:
H1: +G1+1-5 H2: +G2 H3: +G3-2
As with special ordered sets of types 1, 2, and 3, formulas describing members of special ordered sets of type 4 should include a reference to exactly one adjustable cell. EKKSMDL ignores formulas that contain references to more than one cell and issues a message. In the example given above, cell G1 has an up-pseudocost of 1 and a down-pseudocost of 5. Any added or subtracted constants that are omitted are replaced by default pseudocosts of 0.001. EKKSMDL ignores formulas containing more than one positive constant or more than one negative constant and issues a message.
To define a special ordered set of type 4, you can assign the following before the call to EKKSMDL:
CSEKKSOS4 = 'H1...H3'
To define a special ordered set of type 1, you can assign the following before the call to EKKSMDL:
CSEKKSOS1 = 'B1...B3'
If there is more than one set, all sets should belong to disjoint ranges, and the ranges can be listed (separated by commas) in the range set assigned to appropriate control variables. Special ordered sets must be located in contiguous cells. For example, the following assignments cause the creation of three special ordered sets, two of type 1 and one of type 2:
CSEKKSOS1 = 'B1...B3,C1...D2' CSEKKSOS2 = 'F5...F9'
The cells B1...B3, C1...D2, and F5...F9 should be loaded with linear expressions identifying the members of the sets and their reference row entries (or pseudocosts).
EKKSMDL includes a call to EKKIMDL for mixed-integer programming models, if integer data exists. For this reason, restrictions on problem formulation required by EKKIMDL are also required by EKKSMDL. For example, the formulation of a problem with a special ordered set of type 3 requires the creation of a convexity constraint. A convexity constraint is an equality constraint with coefficients for the problem variables of 0 or 1 and a right-hand side of 1. If the cells F1, F2, and F3 are the three members of a special ordered set of type 3, you can load the convexity constraint into cell G1 as follows:
G1: +F1 + F2 + F3 = 1
EKKSMDL sets the priority of all special ordered sets to 1000. All control variables that apply to EKKIMDL also apply to EKKSMDL.
To bypass the setting of control variables Csekksos1, Csekksos2, Csekksos3, and Csekksos4, you can create range names with Lotus 1-2-3 that begin with the prefix EKKSOS1, EKKSOS2, EKKSOS3, or EKKSOS4. You can create multiple special ordered sets of a given type, as long as they all have names beginning with the correct prefix. For example, you can create three special ordered sets having the names:
EKKSOS1.1 EKKSOS1.2 EKKSOS1.3
All three sets will be passed to EKKIMDL during the call to EKKSMDL. Setting control variables Csekksos1, Csekksos2, Csekksos3, and Csekksos4 overrides any definitions that are implied by range names found in the worksheet files.
The "read" option of EKKSMDL not only creates problem constraints, but also loads the solution region with the values of the adjustable cells found in the worksheet files. If a nearly optimal solution is stored in the worksheet files, many of the library solver subroutines can be called by use of the "values pass" restart option. For example, EKKSSLV can be called with init=3, or EKKNSLV can be called with init=1.
After a solution has been found, Lotus 1-2-3 worksheet files can be updated using EKKSMDL. This updates the adjustable cells in the file and leaves the rest of the file unchanged. The following code fragment can be used to read a Lotus 1-2-3 worksheet files, solve a linear program, and then update the file:
CALL EKKCGET(RTCOD,DSPACE,OSLC,OSLCLN) CSOBJECTIVE = 'A10' CSCONSTRTS = 'A2...A9' CSSOLUTION = 'A1...D1' CALL EKKCSET(RTCOD,DSPACE,OSLC,OSLCLN) C UNIT = 45 ACTION = 1 TYPE = 1 RES = 0 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES) CALL EKKMSLV(RTCOD,DSPACE,1,0) CALL EKKPRTS(RTCOD,DSPACE) ACTION =2 CALL EKKSMDL(RTCOD,DSPACE,UNIT,ACTION,TYPE,RES)
EKKSMDL extracts only certain types of information from the Lotus 1-2-3 worksheet files. For the most part, this information consists of cell contents, range names, and formula expressions. There are a few restrictions on this type of information:
[ Top of Page | Previous Page | Next Page | Table of Contents ]