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...
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]
- my impression of what mcinqb3 needs or wants for this problem is the min number of elements and SECONDLY would be the combination that is closest to the target. Although I don't have time right now I would suggest a do a combination matrix based on a max number of elements determined by ROUNDUP( TARGET / [max element] ). Then filter by those > TARGET and then use the MIN of that (i.e. the closest to the TARGET without being under).
If mcinqb3 could chime in and say if my assumption is what they want, i would be happy to do it when I have a chance or maybe what was already presented above is what they want.
