CODEWITHSUNDEEP
excellinear-programmingsolver
jerH
jerH

Reputation: 1299

Using Excel, return 1 for all values >0 WITHOUT using an IF

Working on a covering problem using solver in Excel. I have a matrix of adjacent cells, and a set of binary decision variables. I can use sumproduct() across the decision variables and each row of the adjacency matrix to tell me whether a cell is covered by a particular solution. If I have a constraint on the number of decision variables that can be 1, and I want to maximize the number of covered cells, I need to count how many of those sumproducts return a value >0. Simple answer would be =IF(<address> > 1, 1, 0) and then sum those up, but solver tells me that anything with IF in it doesn't meet linearity conditions. I also tried using -- in front of a logical test as shown below and I get the same error...

enter image description here

enter image description here

Any suggestions?

Upvotes: 1

Views: 81

Answers (1)

AirSquid
AirSquid

Reputation: 11938

Roughly:

  • Make the items in column N for covered into binary variables, along with the other declarations you have made.
  • Add the constraint that those variables in the column are LTE (<=) to the adjacent values in column M.

That's the linearization... for maximizing the sum of "covered"

N    M
0 <= 0
1 <= 1
1 <= 4
1 <= 3
...

If it gets much more complicated, I'd switch to a "real" linear programming framework. ;)

Upvotes: 3

Related Questions