Terms on the left (work and cutter) are given, while A, B, C and PG must be chosen from a set of 29 elements (it is a set in common among unknowns, so we are not allowed to use the same gear twice for two different variables, even though there are duplicates).
How do we find the right gears?
If we only need one combo, or maybe one that minimizes the sum of teeths, a first approach might be through Excel Solver. Since we can use a gear only once, it makes sense to build a model with a table of binary variables, 28x4, and force exactly one gear for each variable, and at most one variable on each gear (that is, looking at the picture below, the sums on the columns are equal to 1, those on the rows are <=1).
Unfortunately, Solver is not really up to the task. The problem is non linear, and both GRG and Evolutionary engines fail to find a solution (both are set to stop after 600 seconds). The standard engine should use a Branch and Bound algorithm, but it may be so basic to become useless when there are more than a few dozens variables.
However, OpenSolver has two non-linear engines that can solve MILP problems, Bonmin and Couenne. The last one can find a solution in a few seconds.
If we want to find all the possible combinations, we need a little of VBA. Even though there are less than a million of combos available, we strive for speed. To this end, I have built a subroutine that splits in two the check of constraints.
In the main loop it will select a set of different gears and check for the two tightest constraints, that with the given set are
B + C >= 160
A + PG >= 122
Only when these conditions are met, we substitute the values in the main equation, and if the absolute value of the difference is within a tolerance, an external function will check for remaining constraints, once again ordered more or less in order of tightness.
No comments:
Post a Comment