Optimising to get a specific sum from a range of values

thsscapi

New Member
Problem:
I need a sort of calculator that will take a set of input values and group them together into a specific sum while minimising "wastage".

Example:
Say I input 20 numbers ranging between 1 and 19. I need some sort of way to group these into sets that each sum to exactly 40. Above 40 is actually possible, but any extra is wasted as it could have been used in another set which would sum to exactly 40 instead.

What I have so far:
If I add all the input values and divide by 40, then remove the remainder, say 176/40=4.4, I know the most optimised would be 4 sets of numbers.
Another plausible method is to add each input value as I enter them, until it gets exactly a sum of 40, then it groups them together. The problem here is that this method may have more "wastage" than when compared to a formula that attempts to add all the values simultaneously rather than adding as they are added.

Any ideas which might help me solve this will also help. I'm new to the formula side of Excel, though I've experimented with the simpler ones.

Thank you!

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Replies
1
Views
509
Replies
5
Views
165
Replies
2
Views
115
Replies
3
Views
546
Replies
0
Views
353

1,203,242
Messages
6,054,349
Members
444,717
Latest member
melindanegron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back