Does anyone have a good Excel formula to automatically calculate the minimum possible waste for a given number of lengths?
So if, as an example, you are nailing moldings and you have 2 x 450 lengths, 3 x 210 lengths, and 1 x 330 length, you enter it into a list, then you enter the lengths you need, for example; 30 cm, 45 cm, 45 cm, 120 cm, 200 cm, 220 cm, etc. Then Excel should automatically calculate how to best distribute the needed cut lengths over the full molding lengths to maximize output and minimize waste.
Possibly this needs to be scripted? Or is there a good formula for this?
It would be very interesting for many builders who want to minimize waste and not end up with a lot of scrap and missing long lengths, etc.
Of course, you can calculate this manually, but it takes so much longer!
So if, as an example, you are nailing moldings and you have 2 x 450 lengths, 3 x 210 lengths, and 1 x 330 length, you enter it into a list, then you enter the lengths you need, for example; 30 cm, 45 cm, 45 cm, 120 cm, 200 cm, 220 cm, etc. Then Excel should automatically calculate how to best distribute the needed cut lengths over the full molding lengths to maximize output and minimize waste.
Possibly this needs to be scripted? Or is there a good formula for this?
It would be very interesting for many builders who want to minimize waste and not end up with a lot of scrap and missing long lengths, etc.
Of course, you can calculate this manually, but it takes so much longer!
I've thought a lot about that question.
1. Today's craftsmen don't care. They keep taking new lengths all the time because it's the builder who pays.
2. I usually calculate this on paper slips. I know Excel quite well, but the time to script and verify is probably longer than calculating on a grid paper.
3. Additionally, as a part-time builder, I find the planning more enjoyable than the work. I first assess the need, then attempt a cut list, and then they still don't have the lengths I need.
4. It takes about half an hour to calculate manually, and you have to weigh that against the value of your own work time.
1. Today's craftsmen don't care. They keep taking new lengths all the time because it's the builder who pays.
2. I usually calculate this on paper slips. I know Excel quite well, but the time to script and verify is probably longer than calculating on a grid paper.
3. Additionally, as a part-time builder, I find the planning more enjoyable than the work. I first assess the need, then attempt a cut list, and then they still don't have the lengths I need.
4. It takes about half an hour to calculate manually, and you have to weigh that against the value of your own work time.
Excel has a feature called Solver. With it, you can solve this quite easily.
It is an example of the knapsack problem (https://sv.wikipedia.org/wiki/Kappsäcksproblemet), which is a so-called NP-hard problem. It is a group of problems that are difficult to compute and often require approximate methods. It is not something that can be formulated with Excel's standard library as far as I know. Fundamentally, it is solved with linear integer programming, so one would have to check if Excel supports that. There should, however, be some suitable good-enough solution that is simpler...
How is it "quite easily" then? Do you have a formula for this?Micke64 said:
It may take some time to first set up a template in Excel, so for some projects it might not be worth it. But if you need to use it multiple times in the future, it can be beneficial in terms of time. It makes work a bit more enjoyable too.
I recall that there is a built-in guide, but it's been a while since I used it.Martin_B said:
How is "quite simple" then? Do you have a formula for this?
In Solver, you specify a number of variables/cells that it should adjust (try to find the best value) so that the result of a formula or another cell reaches a certain target value. For example, making the sum of waste approach zero.
Found an online version, it might be convenient if you don't want to spend time in Excel: https://jonathan.overholt.org/projects/cutlist. One limitation is that you can't specify how many boards you have available of different lengths. The advantage of this is that it becomes much simpler and faster to solve.
Aha ok... Hmmm can't find the problem solver, need to look into this a little more.
Frantically searching online and come across names like Greedy Algorithm, Cutting Stock Problem, etc.
Very interesting.
Frantically searching online and come across names like Greedy Algorithm, Cutting Stock Problem, etc.
Very interesting.
Typical that it's in inches...A arasmus said:
Nah, now I'm switching to pen and paper and straining my brain for a while. This won't be solved anytime soon. I kind of understand why one still goes manual... haha I'm already completely exhausted now after all the searching for Excel formulas. Sigh
I'll get back as soon as I've found something though.
I'll get back as soon as I've found something though.
The risk with overly narrow optimizations is, on the other hand, that you lose tolerance for drunken carpentry, for example, cutting at the wrong angle or measuring with the ruler upside down. It's equally frustrating every time you find yourself having to buy a single short length of something due to a mistake.