How To Find Maximum Sum From The List With nth Number of Values Not Greater Than To X

Cross-posted at https://www.mrexcel.com/board/thread...-cell.1191127/ with solution


Hi, I am new here.
I have a list of different numbers in range A1:A18
What i need is to sum up any 3 or 4 values that can be near to 240 but not more than that
For Example, I have This List

Range A1 to A18 (Largest to Smallest)
A1: 87
A2: 86
A3: 85
A4: 84
A5: 83
A6: 82
A7: 81
A8: 81
A9: 72
A10: 70
A11: 69
A12: 66
A13: 63
A14: 59
A15: 57
A16: 54
A17: 53
A18: 50


Now i need Any Combination of 3 values or 4 values which sums the maximum number but the total always be less than 240.
Like in Above List The Possible Combinations to make Totals which should be less than 240 is below.

85 + 84 + 70 = 239
83 + 82 + 72 = 237
81 + 81 + 69 = 231
63 + 59 + 57 + 54 = 233
53 + 50 = 103

It should give me the totals Like That but not more than 240
and the cells which are used should not be used again like in the first combination Cell A3 (i.e. 85) , A4 (84), and A10 (70) is used so it should not be used again.

if there is any macro any brother can help for this.

Thanks for your support in advance.

Please Login or Register  to view this content.

Please read the rules before continuing.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have provided the link this time, but there is already a solution there that you said works. That is why we have this rule.

working in your workbook is slow, working in memory is rocket speed.
So collectiong data and only once writing to the workbook

Please Login or Register  to view this content.

Understood. What is the goal ?

If you get there 10 possible solutions, which one is the best ?
Now you have 5 times <=240 and 2 remaining numbers.

The goal is to sum-up the highest totals first like 87 + 86 + 66 = 239 so it is the best combination and once this 87 (i.e cell A1) is used so this cell A1 should not be used again for addition in other combinations. as in image above. and so on for other cells.
Remember that in image above (with red rectangle) i want to be like this, but now the macro is not working as image. i want to do same as image

I think this is a classic "cutting" problem.

see attachment, i give >105.000 solutions with 18 digit divided over 4*4.
Of them there are 120 of which the smallest partition = 103

So , that's the frustatican part of the answer.
What's the "best" solution ???

Lucky it was Christmas, the computer calculated this workbook, while i was busy.

Edit : file too big.

when i looked to the "cross-posted" site, it's indeed a cutting problem.
this recent question is almost the same problem https://www.excelforum.com/excel-gen...izes-cuts.html
I didn't check the given solution in relation to your problem.
Normally you can also use "solver" or the newer version for those problems

As what you asked, I can give you 616 different cut solutions, see sheet "Blad3", of which i think solution 298 is the "best".
Of the 6st wood, you only use 103 of the 240 (cm?).

So you asked me an image, i give you 616 images.

Perhaps look to the other thread and ask help to "Davesexcel" or "Alf"

What i need is to sum up any 3 or 4 values that can be near to 240 but not more than that

Not going to spend to much time on this...This is my take...

Please Login or Register  to view this content.

i need exactly as below in image.

I do not get your combinations though...

Untitled.png

Pleasure...Glad I could contribute...
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.