Solver - Maximize objective but limiting the count of variable

Hello every one,

I have an optimization problem which i need some help. I have to allocate bunch of Product into a container, so that I maximize the efficiency of the container (lower bound 95% - Upper Bound 105%). Other constraint include , i can put a maximum of 15 different product and minimum 20 of each product. I have modelled the problem in the attached sheet. How do i add the constraint that force solver to limit the number of SKU chosen <= 15. I am trying to avoid non linearity.

I tried different cost function, but unable to find a right constraint modelling. Appreciate help
Thanks

Originally Posted by Crooza

I changed formula in column J to this

Please Login or Register  to view this content.

so the container allocation only shows when there is a selected SKU in column H. then ran it on a GRP non linear to get the attached result which gives a 105% utilisation

Hello,

Thank you so much. I understood the non linearity, but would prefer to transform it to a linear equation. Sometime the non linear equation gives absurd result. I dont know much math behind it, but screws up the work

Originally Posted by Hydraulics

The problem can be linearized, but Solver cannot handle it (too many constraints). There may be easier solutions, but I'm in a bit of a hurry now.

If you switch to OpenSolver the best solution is 105% of container allocation with a cost of 1011800.

HTH,

Francesco

EDIT: the formula in column D can be used to resize ranges, and then the problem is solvable with Solver. I'm updating the worksheet accordingly.

Damn, you are a gem in linear programming. Thanks so much for the effort, and especially about the open solver. I tried linearizing it in excel solver from some google search, and found the exact issue. Open solver resolved it. Awesome. Thanks again.