Lp_solve link to Excel

Lp_solve is a freeware mixed-integer program solver. This document describes how to run it from Windows Excel, S-Plus, or R.

Version 5.7 Implementation (Calling lpSolve 5.5), May 12, 2010.

Features new to 5.7:

  • Choose "Max" or "Min" as appropriate
  • The top box is labeled "Enter Range containing coefficients of Objective Function." Click and drag so that that box contains the cell range holding the objective function. In the top example, that would be B7:D7.
  • The second box holds the matrix of constraints. To the left are radio buttons that allow you to enter these as either rows or columns. Each constraint must take up one row (column). It should be two cells longer than the objective function: the first extra column (row) holds the sign associated with the constraint ("<", "<=", "=", "==", ">", ">="), and the second extra column (row) gives the right-hand side value associated with the constraint. In the top example the constraint matrix is in B9:F10.
  • The third box expects a reference indicating which variables are to be integers. This vector should be as long as the objective function reference, that is, it should have one entry for each variable. A 0 indicates that the variable is not required to be integer; a 1 indicates that it must be an integer; any other choice indicates that the variable must be binary. If you don't enter a range at all, then no variable is required to be an integer or binary. In the top example, you might use the range B8:D8 to indicate that all three variables need to be integers. Or you might not.

    The checkboxes allow you to set set all variables to be integers, or all to be binary. These over-ride any choice you make in the box -- and of course binary over-rides integer.

  • The fourth box tells Excel where to put the results. This reference should be one cell longer than the objective function; each cell holds the optimal coefficient for its corresponding variable, and the final, extra cell holds the value of the objective function. See the picture for details.

    All the boxes are required to be filled in, except for the integer one. However you can fool the spreadsheet by putting something that isn't a valid range into a box. Right now I don't try to catch that. Don't do that.

    When you're ready, press the "Run Lp_solve" button and your results should appear. If there's an error, you should see a pop-up box giving the error's number. There are two sorts of errors: lpSolve's own internal errors and VB errors. Happily, their numbers rarely overlap. Error codes include:
    2: "No feasible solution."
    3: "Problem is unbounded."
    4: "Problem is degenerate."
    5: "Numeric failure."
    13: "Type mismatch." This VBA error might occur when you select only one cell where I expected a range.
    48: "Error in loading DLL." The DLL is not on your path or not named lpslink57.dll.
    49: "Bad DLL calling convention." You're using the R dll for Excel.

    For an assignment problem, make that choice on the problem selection window. Enter a matrix of costs and a space for decision variables into the appropriate places. We assume each row and column need to add up to 1 and that all variables are integers. The optimal value will be placed one row and one column past the bottom right-hand corner of the cost matrix. See the workbook for examples.

    For a transportation problem, make that choice on the problem selection window. Enter a matrix of costs and a space for decision variables into the appropriate places. The decision variable matrix should have two extra rows giving signs and right-hand sides of constraints, and likewise two extra columns. We assume that all variables are integers. The optimal value will be placed in the bottom right-hand corner of the cost matrix. See the workbook for examples.

    Old (v. 5.6) Excel stuff: Excel addin; DLL; Tester.


    For R:

    This is now in package form. Go to the
    R archive and install lpSolve as you would any other R package.

    For S-Plus:

    You will need two files: the DLL and the code. Download both. Start S-Plus. Use the source () command on the code file to create the S-Plus objects you need. Now edit the lp.load () function so that it refers to the DLL that you downloaded. Now you're ready to run. Here are some examples of the lp functions in action.

    lp() example:

    #
    # Set up problem: maximize
    #   x1 + 9 x2 +   x3 subject to
    #   x1 + 2 x2 + 3 x3  <= 9
    # 3 x1 + 2 x2 + 2 x3 <= 15
    #
    > f.obj <- c(1, 9, 3)
    > f.con <- matrix (c(1, 2, 3, 3, 2, 2), nrow=2, byrow=TRUE)
    > f.dir <- c("<=", "<=")
    > f.rhs <- c(9, 15)
    #
    # Now run.
    #
    > lp ("max", f.obj, f.con, f.dir, f.rhs)
    Success: the objective function is 40.5
    
    > lp ("max", f.obj, f.con, f.dir, f.rhs)$solution
    [1] 0.0 4.5 0.0
    #
    # Run again, this time requiring that all three variables be integer
    #
    > lp ("max", f.obj, f.con, f.dir, f.rhs, int.vec=1:3)
    Success: the objective function is 37
    > lp ("max", f.obj, f.con, f.dir, f.rhs, int.vec=1:3)$solution
    [1] 1 4 0
    
    

    lp.assign() example:

    
    
    > assign.costs <- matrix (c(2, 7, 7, 2, 7, 7, 3, 2, 7, 2, 8, 10, 1, 9, 8, 2), 4, 4)
    
    > assign.costs
         [,1] [,2] [,3] [,4]
    [1,]    2    7    7    1
    [2,]    7    7    2    9
    [3,]    7    3    8    8
    [4,]    2    2   10    2
    
    > lp.assign (assign.costs)
    Success: the objective function is 8
    lp.assign (assign.costs)$solution
    
         [,1] [,2] [,3] [,4]
    [1,]    0    0    0    1
    [2,]    0    0    1    0
    [3,]    0    1    0    0
    [4,]    1    0    0    0
         
    

    lp.transport() example:

    
    #
    # Transportation problem, Bronson, problem 9.1, p. 86
    #
    # Set up cost matrix
    #
    > costs <- matrix (10000, 8, 5); costs[4,1] <- costs[-4,5] <- 0
    > costs[1,2] <- costs[2,3] <- costs[3,4] <- 7; costs[1,3] <- costs[2,4] <- 7.7
    > costs[5,1] <- costs[7,3] <- 8; costs[1,4] <- 8.4; costs[6,2] <- 9
    > costs[8,4] <- 10; costs[4,2:4] <- c(.7, 1.4, 2.1)
    #
    # Set up constraint signs and right-hand sides.
    #
    > row.signs <- rep ("<", 8)
    > row.rhs <- c(200, 300, 350, 200, 100, 50, 100, 150)
    > col.signs <- rep (">", 5)
    > col.rhs <- c(250, 100, 400, 500, 200)
    #
    # Run
    #
    > lp.transport (costs, row.signs, row.rhs, col.signs, col.rhs)
    Success: the objective function is 7790
    
    > lp.transport (costs, row.signs, row.rhs, col.signs, col.rhs)$solution
         
         [,1] [,2] [,3] [,4] [,5]
    [1,]    0  100    0  100    0
    [2,]    0    0  300    0    0
    [3,]    0    0    0  350    0
    [4,]  200    0    0    0    0
    [5,]   50    0    0    0   50
    [6,]    0    0    0    0   50
    [7,]    0    0  100    0    0
    [8,]    0    0    0   50  100