Forum Discussion
How to find which combination of multiples of a set of 5 numbers will return a given value
Hi all. New to excel, but decent using spreadsheets for trivial things. Right now, I'm trying to figure out which combinations of a set of 5 numbers or their multiples will give me a sum of AT LE...
I think Solver is the way to go.
The method is Simplex LP (linear programming).
The integer multipliers x are the optimisation variables.
The objective function is basically the sum of x but I have added a small proportion of the constraint exceedance to act as a tiebreaker.
The main constraint is on g=SUM(a*g) which must exceed the target.
The figure shows the solution returned by Solver.
- I thought about suggesting the solver but i think I can usually create a function/lambda faster than figuring out how to best implement the Solver. lol. That said, your solver solution is elegant with 1 caveat. I think your 'tie breaker' should be tied to the target (or probably 2x target) to make sure in a more general solution, that tie breaker doesn't become > that primary criteria (sum of X). I think dividing by 2x target will guarentee it as I don't think dividing by target alone will guarentee it.
