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

Question

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

Any suggestions?

AirSquid · Accepted Answer · 2024-10-01 01:06:10Z

3

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

answered Oct 1, 2024 at 1:06

AirSquid

12k2 gold badges11 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jerH Over a year ago

That makes sense...never would have thought of adding additional decision variables. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related