Apologies if the title isn't very clear.

What I am trying to do is get a google sheet to automatically calculate how many lengths of a material I will need to cover an area, hopefully to include a mix if needed. There are three different lengths of material that never change, but the total area I need to cover changes on a case by case basis. It is only a straight line so there is no need to worry about width or height.

The data breaks down as follows: Pre-set lengths to choose from

• 10'6"
• 12'6"
• 14'6"

Length of area I need to cover only comes in inches (ie. 68 1/2"; 70"; 59")

The only thing I have been successful in doing is getting the length I need to cover and then manually picking out how many pieces of each length I need, but I cannot think of any way for me to have a formula or script optimize how many of each piece I need. I can understand formulas well enough, but once trying to script anything comes into play I start getting lost. I believe this issue may be beyond the capabilities of formulas.

CodePudding user response：

This is an interesting problem - I don't have the 'reputation' required to comment, but to be clear: you're actually trying to find the 'best fit' of the available lengths to cover the required length? If that's the case then yes, you're not going to get there without scripting. Fortunately, there are other folks who have this problem and have solved it... you could look at this online cut-list calculator for an example. I think that one even includes an embeddable script for your sheets.

If you're looking to solve the problem yourself because it's interesting, googling 'optimal cut list' or the like will turn up references. Usually you're optimizing on two variables (e.g. 'fewest joins' and 'least waste'), which tips you over into the world of linear programming (only just...) if you want to go there. If it were me, I'd just dig up a few example scripts and map how they operate back to a theoretical description (e.g. this wiki article.)