Solver Optimization Tool

[AJP11]

Hello,

I have used solver to optimize the sum of numbers given certain constraints, however I am now trying to optimize the product of numbers given certain constraints. When finding the optimal sum I can use a binary list to select the combination of item to sumproduct together to find the optimal solution. When trying to find the optimal product using binary selection the zeros will automatically make my answer zero and when filtering out the zeros the solver is unable to run.

I am trying to use the solver tool to find the combination of items that maximizes the Probability of A while producing a corresponding Probability of B <= 50%. The only constraint is that Probability B must be <=50%, and each probability can only be used once. There is no constraint on how many items are used to optimize the function. Hard to explain by words but hopefully the pictures and google sheets link can help assist with you answer.

For reference the formulas I am using in cell B16 and Cell D16 are the following: =PRODUCT(FILTER(B2:C15,C2:C15<>0)) and =PRODUCT(FILTER(C2:D15,C2:C15<>0)).

Link:
Google Sheets Example

Pictures:

 

[Eric W]

Welcome to the MrExcel forum.

Given that you're using Google Sheets, this question really should be in the General Discussion and Other Applications forum. However, I gave it a shot using Excel and Excel's built-in Solver. I believe that your problem is the formulas you're using in B16 and D16. They should be:

=PRODUCT(FILTER(B2:B15,C2:C15<>0)) and =PRODUCT(FILTER(D2:D15,C2:C15<>0))

When I used those, the Solver worked fine. I even double checked the result by writing a macro that checked every possibility. Hope this helps.