Solver Inquiry - Excel

[Anthony_User]

Hey - trying to run parameters in solver where the result would be the 2 highlighted values showing 1 and 1 since they equate to Value of 9029. Do you know what I am doing wrong?

Thanks in advance!

[#2]

@Anthony_User 

Maybe like in this example. The changing variable cells are $C$2:$C$6 and subject to the constraints is $C$2:$C$6=binary.

[#3]

Thanks, but when I ran this last, cell B8 was def 0, and no columns consisting of what you have in D and E. When it worked out for me, it just had the rows and columns in my original screenshot.

[#4]

@Anthony_User 

The formula in the B8 is =SUMPRODUCT(B2:B6, C2:C6).

Note that the By Changing range is C2:C6, not including B2:B6.  The same with range for the binary constraint.

You could Set Objective to B8 and set To Value Of 9029, as you did.

But I think it is more flexible to enter the goal value in Excel, as you did in D2.  Then Set Objective to B9 and To Value Of 0, where B9 is the formula =B8-D2, as shown.

BTW, in case equality is not possible, a more flexible set-up is to set B9 To Max, with the constraint B9<=0.

To demonstrate, set B8 to 9020.  Solver selects 1568 and 7282, for a total of 8850 in B8, which is 170 less than 9020 (-170 in B9).

PS....  I had not see @OliverScheurich's solution before I clicked Post.

[#5]

thank you!