Excel Solver - binary variable constraints

Hi everyone,

I would appreciate if you could help me. I have a set of values (B2:B126) and binary variables (C2:C126). My target cell : Sumproduct(C2:C126;B2:B126). I would like to add this parameter as a constraint : Sum(C2:C126) <= 4. By doing this, I would like to limit my number of binary variables which is required for my problem. Do you know how could i add this on Solver? Thanks in advance

Originally Posted by MrShorty

It seems so obvious to me that I wonder if there is more to the question. I would expect:

1) Enter a formula in a convenient cell =SUM(C2:C126).
2) Add constraint to Solver that cell used in step 1 is less than or equal to 4.

Is that what you are trying to do, or is there more to it than that?

Yes, this is exactly what i am trying to do! But, i had tried this before and the binary variables didnt come as 0 or 1. In the solution, they were decimal numbers between 0 and 1. Is it because there werent a feasible solution? Do i need to change the constraint or add a new one?

In the solution, they were decimal numbers between 0 and 1. Is it because there werent a feasible solution?

That is a possibility as solver may give a decimal solution if there are no solution with the current constraint setting. Another possibility is that the solver setting under "Options" is the default setting i.e. "Ignore integer Constraints"

a_int.jpg

Alf

When i tried my own, I found the solution by using only 6 binary variables out of 125. I expect from Solver doing the same thing. However, it gives the decimal values to all of my binary variables.

I've set up your model as your uploaded file did not have the proper settings for "cells to change" and constraints for these and target value.

Alf

Hi Alf,

I really appreciate for your help. It works now. I have one further question regarding limiting the number of binary variables for the solution. In the file you have sent, the number of binary variables used for the solution is 11. However, I can see that we can reach the target value by using only 6 binary variables. I added a constraint like : SUM(C2:C125) <=7 and it didn't work. Is there any other way for doing it?

Thanks.

I had set the same constraint. I don't know what the problem is but it works now, thanks so much! One more question, I also want to minimize SUM(C2:C125) as I want to use the minimum number of binary variables if possible. Because the constraint SUM(C2:C125) <=6 won't work if i change the target value. Each time i need to change this constraint. So, do i need to set my target as SUM(C2:C125) and minimize it?

I tried a setting like this that should work theoretically but it seems like this setup is to complex for solver to find a solution to this problem setting
a_solv.jpg
so you may have to run solver by trial and error i.e. setting a value <= for the sum(C2:C125) and if this gives you a solution you reduce the value.

An other alternative would be to set solver to run in a loop using VBA.

Alf

it's that the number of solutions is overwhelming

I never thought about that. Thanks for explanation shg I may be able to build solver models but my mathematical know-how is not that profound.

Alf

Thanks for the more extensive explanation.

f you have a bunch of (non-negative) numbers, then every sum must fall somewhere between 0 and the sum of all the numbers.

This I understands / make sense to me but I think I do need to a bit of serious googling to see if I can understand the combination part. I see the principle can be used in the "Sock-picking" problem which I have thought was a probability problem. Is this a case of different "labels" for using the same technique in solving the problem?

It looks interesting and I'll have a go at seeing how much I can understand of the pigeon principle.

Alf