How to replicate excel solver in R

Question

I used excel solver to solve an optimization problem, and I am trying to replicate it in R.

I found many packages like optim, ROI etc., but it seems all of them only take a vector as the object to optimize and allow the variables to take any continuous value. In my case, I have a constraint matrix that also needs to be satisfied and my variables can only take binary values.

Here is problem I want to solve:

A-D are machines, 1-3 are tasks and the number in the first matrix is the value generated by using X machine to do Y task. The constraints are: A-D can do and only can do one task (cannot split); each task can be worked and only be worked by one machine.

Here is the code I am using:

par = rep(c(0,1),6)

mat <- matrix(c(9,10,11,4,5,10,1,3,5,7,5,4), nrow = 3)

fr <- function(x) {  
  y= matrix(x,nrow = 4)
  sum(mat %*% y)
}

a = optim(par, fr)

Some questions: How can I optimize maximum, seems this function default optimize minimum? How can I add constraints into it? How can I limit to binary variables?

Answer 1

You need to construct a vector for the objective function and a constraint matrix, finally solving with one of the R LP solvers:

library(lpSolve)
costs <- matrix(c(9, 10, 11, 4, 5, 10, 1, 3, 5, 7, 5, 4), nrow=3)
nr <- nrow(costs)
nc <- ncol(costs)
columns <- t(sapply(1:nc, function(x) rep(c(0, 1, 0), c(nr*(x-1), nr, nr*(nc-x)))))
rows <- t(sapply(1:nr, function(x) rep(rep(c(0, 1, 0), c(x-1, 1, nr-x)), nc)))
mod <- lp("max", as.vector(costs), rbind(columns, rows), "<=", rep(1, nr+nc), binary.vec=rep(TRUE, nr*nc))

Now you can grab the solution and the objective function:

mod$objval
# [1] 27
matrix(mod$solution, nrow=nr)
#      [,1] [,2] [,3] [,4]
# [1,]    0    0    0    1
# [2,]    1    0    0    0
# [3,]    0    1    0    0

Note that functions like optim are not well suited for this problem both because they don't consider matrices of constraints and also because they cannot limit to binary variable values.