# Optimization problem when Solver doesn't work

1. ## Optimization problem when Solver doesn't work

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

2. ## Re: Optimization problem when Solver doesn't work

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.
If you are using an IF statement in your formula i.e. "=IF(A2=1,B2=5,2) this will not work with solver as solve can't handle discontinuous functions like "IF", "COUNTIF" and "SUMIF"

Perhaps you could upload a sample workbook.

Alf

3. ## Re: Optimization problem when Solver doesn't work

Thank you for the answer so far. Yes there are indeed some ifs involved in the equations which clears up why solver doesn't work. As you requested I'm attaching a simplified version of it. Basically I'm trying to maximize cell "O63". This is supposed to be done by changing values S4:S62 between "0" and "1". In the example file the sum of them (S63) is equal to 10. I would like for their sum to be equal to S65 (i.e. 15). So the final problem is which 15 cells amongst S4:S62 that should take the value "1" in order to get the maximum value for cell O63

4. ## Re: Optimization problem when Solver doesn't work

Looking at your formula in cell J4
``Please Login or Register  to view this content.``
I would change that to

``Please Login or Register  to view this content.``
so if you multiply the VLOOKUP part with the corresponding S value you either get 0 or the VLOOKUP
value depending on if the S value is 0 or 1.

Alf

There are currently 1 users browsing this thread. (0 members and 1 guests)