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!
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!
