Mar 15 2024 07:22 PM - edited Mar 15 2024 07:29 PM
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 LEAST a specific value.
For example, the numbers are:
1922
2404
3013
3788
4755
I am trying to combine the smallest number of their multiples that will give me a value of at least 18000.
So,
1922+2404+3013+3788+4755 = 15902 = Doesn't work
1922+4808 (2404*2)+3013+3788+4755 = 18306 = Works
If possible, I'd also like to find the combination that is closest to 18000, but that's not a necessity. I'd also like to find all combinations if that's not a problem, and not just a single instance. And it doesn't need to involve every number in the set, it can also just be 4755 * 4 = 19020.
I know I could do this with brute force and just write down all multiples. but I will be using a similar idea with various groups of numbers and final sums, so I want to just be able to plug and play.
Thanks for any help, and sorry if this isn't clear. Let me know if more information is needed.
Mar 15 2024 08:03 PM
@mcinqb3
You can achieve that using the 'Excel Solver', if you're not familiar with it. You can use these steps in order to do that.
To make the solver option appear, follow these steps:
Mar 15 2024 08:12 PM
Mar 17 2024 12:50 AM
re:
So,
1922+2404+3013+3788+4755 = 15902 = Doesn't work
1922+4808 (2404*2)+3013+3788+4755 = 18306 = Works
I am afraid 18041 also works and closer to 18000
1922+3788+3788+3788+4755=18041
18041 | 41 | 1922+3788+3788+3788+4755 | 4755 | 5 | 5 |
18046 | 46 | 2404+2404+2404+2404+2404+3013+3013 | 3013 |
Mar 17 2024 02:40 AM
For finding the closest match in the given example, I think we can list all combinations of up to 9 of the values including replacements. For listing combinations I used a lambda function courtesy of @m_tarler . The closest I could find to 18000 with this method was
1922 * 5 + 2404 + 3013 * 2 = 18040
[ref: 'example of counting multi-subsets' section of Combination - Wikipedia article]
Mar 17 2024 02:56 PM
Mar 17 2024 04:03 PM - edited Mar 17 2024 04:13 PM
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.
Mar 17 2024 05:05 PM