complicated calculation

Originally Posted by doudou

Hi,
anybody that would help would be very much thanked...
I would like to find a way to solve a problem I am very often confronted with, and always have to solve by hand.
Among a range of products, I have several possible lengths. For one product, I need to have several lengths. How can I calculate the most economic combination ?
Example: I need 80+65+79+254+158+345+53 within a range of 240, 270, 300, 330, 360, 400. What should be the best combination so that the final total length is the smallest possible ?
Good luck

If I read Pascal's Triangle correctly, there are 127 combinations of 7 numbers, which combinations you want to compare to be equal of less than 6 other legths to calculate the smallest wastage.

Whilst this is not impossible, one method is as per the attached, where (for the first few combinations) the wastage is calculated for the combination, and the lowest figure is deemed to be the best fit, however, the remaining row patterns required will take some effort to complete, and I guess the question then remains as to whether you expend more effort doing that or doing the 'best fit' manually.
(note, to view or set more formula, select Tools, Options, View and tick Formula, the pattern can be seen as will be required for columns A to G where the final row will be A =A1, B =B1, C =C1, D =D1, E =E1, F =F1 and G =G1)

When all combinations are entered, the wastage is calculated for each pattern, and the total wastage for the row. (impossible lengths are flagged as 9999). The row with the smallest waste is reflected at M3

Hope this helps, and maybe someone can organise a pattern generator for any combination of 1 to 7 from 7 to assist in the Row entries.


Of course, if you then want 8, 9 or 10 lengths . . . . . (you will need a much bigger set of patterns)
note, you need only setup the patterns for the largest combination required, it works for smallers sets, and you can extend the Lookup at A2:G2, amend the formula (currently) at I5 and formula-fill sideways and downwards.
---