excellinear-programmingsolver

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?


Solution

  • Roughly:

    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. ;)