I have a problem which I tried to use with solver but it doesn't give me a right answer and I was wondering how and if it's possible to right a program to do what I need.
I'll give a simple version of what the problem is and maybe somebody can help out.
I have a column (lets say A1:A10) which I want to have binary values (zeros or ones).
I have another column (lets say B1:B10) that take values from a complicated formula depending on where the (zeros and ones) values are on column A.
I have a third column (lets say C1:C10) that have some values that don't change.
What I want to succeed is to create a program that depending on how many values of "1" I want in column A to find the optimal pattern that would maximize the sumproduct(B1:B10,C1:C10)
What I tried to do is put solver to find the max of a cell that I had the sumproduct equation, by changing A1:A10 with two constraints. The first is that A1:A10 is binary and the second is that the SUM(A1:A10) is equal to the value that I want. It works mechanically but doesn't give the best answer (I've tested different solver algorithms but I had no luck)... It seems the result uses the last options (e.g. if i choose 3 values of 1, they always appear in C8:C10...)
Is there a way to overcome this with VBA code? Thank you in advance
Bookmarks