[R] Optimization in R similar to MS Excel Solver

Hans W Borchers hwborchers at googlemail.com
Tue Mar 12 10:17:10 CET 2013


Pavel_K <kuk064 <at> vsb.cz> writes:
> 
> Dear all,
> I am trying to find the solution for the optimization problem focused on
> the finding minimum cost.
> I used the solution proposed by excel solver, but there is a restriction
> in the number of variables.
> 
> My data consists of 300 rows represent cities and 6 columns represent the
> centres. It constitutes a cost matrix, where the cost are distances between
> each city and each of six centres. 
> ..+ 1 column contains variables, represents number of firms.
> I want to calculate the minimum cost between cities and centres.  Each city
> can belong only to one of the centres.

(1) The solution you say the Excel Solver returns does not appear to be 
    correct: The column sum in columns 3 to 5 is not (greater or) equal
    to 1 as you request.

(2) lpSolve does not return an error, but says "no feasible solution found",
    which seems to be correct: The equality constraints are too strict.

(3) If you relieve these constraints to inequalities, lpSolves does find
    a solution:

    costs <- matrix(c(
    30, 20, 60, 40, 66, 90,
    20, 30, 60, 40, 66, 90,
    25, 31, 60, 40, 66, 90,
    27, 26, 60, 40, 66, 90), 4, 6, byrow = TRUE)

    firms <- c(15, 10, 5, 30)

    row.signs <- rep (">=", 4)
    row.rhs   <- firms
    col.signs <- rep (">=", 6)
    col.rhs   <- c(1,1,1,1,1,1)

    require("lpSolve")
    T <- lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
                       presolve = 0, compute.sens = 0)
    T$solution
    sum(T$solution * costs)     # 1557

Of course, I don't know which constraints you really want to impose.
Hans Werner

> A model example:
> costs: distance between municipalities and centres + plus number of firms
> in each municipality
> "Municipality" "Centre1" "Centre2" "Centre3" "Centre4" "Centre5"
> "Centre6"
> "Firms"    
> "Muni1" 30 20 60 40 66 90 15
> "Muni2" 20 30 60 40 66 90 10
> "Muni3" 25 31 60 40 66 90 5
> "Muni4" 27 26 60 40 66 90 30
> 
> The outcome of excel functon Solver is:
> cost assigned
> "Municipality" "Centre1" "Centre2" "Centre3" "Centre4" "Centre5" "Centre6"
> "Solution"    
> "Muni1"  0 20 0 0 0 0 300
> "Muni2" 20  0 0 0 0 0 200
> "Muni3" 25  0 0 0 0 0 125
> "Muni4"  0 26 0 0 0 0 780
> 
> objective : 1405
> 
> I used package "lpSolve" but there is a problem with variables "firms":
> 
> s <- as.matrix(read.table("C:/R/OPTIMALIZATION/DATA.TXT", dec = ",",
> sep=";",header=TRUE))
> 
>       [2] [3] [4] [5] [6]
> [1] 30 20 60 40 66 90
> [2] 20 30 60 40 66 90
> [3] 25 31 60 40 66 90
> [4] 27 26 60 40 66 90
> 
> row.signs <- rep ("=", 4)
> row.rhs <- c(15,10,5,30)
> col.signs <- rep ("=", 6)
> col.rhs <- c(1,1,1,1,1,1)
> lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
> presolve=0, compute.sens=0)
> lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
> presolve=0, compute.sens=0)$solution
> 
> Outcome:
> Error in lp.transport(costs, ...):
>   Error: We have 6 signs, but 7 columns
> 
> Does anyone know where could the problem ? 
> Does there exist any other possibility how to perform that analysis in R ?
> I am bit confused here about how can I treat with the variables "firms".
> 
> Thanks 
> Pavel
>



More information about the R-help mailing list