Bar Cutting Optimization????


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hello, can anyone help me please? I am trying to create a worksheet from Excel that will allow me to optimize bar cutting lengths for example. I will have metal bars of a standard length and will need to cut various different lengths from this. Once i imput my smaller cut lengths and quantities of each i want the worksheet to be able to work out how many total bars i need to order of standard length and if possible identify which smaller bits need to be cut ffom the bigger bits. It might seem a bit difficult to comprehend but anyone who has used a bar cutting optimizer will understand my query! If anyone has already set up such a worksheet i would be most grateful if they could either let me know how i can get it or how to create one. Thanks
Similar Excel Video Tutorials
Formulas: Copy Vs. Cut
 The Excel Basics Series shows a systematic description of what Excel can do from beginning to end.
#10 Video topics:
1)See the differences b ...
Similar Topics
Hello, can anyone help me please? I am trying to create a worksheet from Excel that will allow me to optimize bar cutting lengths for example. I will have metal bars of a standard length and will need to cut various different lengths from this. Once i imput my smaller cut lengths and quantities of each i want the worksheet to be able to work out how many total bars i need to order of standard length and if possible identify which smaller bits need to be cut ffom the bigger bits. It might seem a bit difficult to comprehend but anyone who has used a bar cutting optimizer will understand my query! If anyone has already set up such a worksheet i would be most grateful if they could either let me know how i can get it or how to create one. Thanks
Hi All,
I have a problem that is quite similar to this thread
http://www.mrexcel.com/forum/showthread.php?t=53178 but a little different. The problem is i need to find the best 2 or 3 long lengths to use in order to be able to cut the long lengths into shorter lengths with quantities dictated by demand.
I have sales statistics for 4 different width beams with lengths from 3.0m to 6.6m in .3m increments. The maximum length i can use to cut from is 12.0m and the length needs to be kept as long as possible to maximise efficiency. These beams are timber and come in treated and untreated so there are two sets of statistics.
195mm
230mm
260mm
295mm
Length
Pieces
Length
Pieces
Length
Pieces
Length
Pieces
3
206
3
149
3
50
3
4
3.3
137
3.3
208
3.3
73
3.3
4
3.6
408
3.6
494
3.6
310
3.6
94
3.9
246
3.9
359
3.9
192
3.9
155
4.2
390
4.2
709
4.2
374
4.2
232
4.5
119
4.5
271
4.5
126
4.5
64
4.8
359
4.8
557
4.8
453
4.8
281
5.1
142
5.1
225
5.1
291
5.1
133
5.4
350
5.4
524
5.4
454
5.4
359
5.7
40
5.7
105
5.7
282
5.7
67
6
289
6
610
6
812
6
441
6.3
62
6.3
86
6.3
84
6.3
56
6.6
239
6.6
300
6.6
271
6.6
217
195mm
230mm
260mm
295mm
Length
Pieces
Length
Pieces
Length
Pieces
Length
Pieces
3
93
3
96
3
63
3
37
3.3
345
3.3
488
3.3
275
3.3
99
3.6
364
3.6
458
3.6
297
3.6
141
3.9
319
3.9
407
3.9
331
3.9
249
4.2
326
4.2
661
4.2
351
4.2
314
4.5
176
4.5
355
4.5
204
4.5
169
4.8
227
4.8
460
4.8
385
4.8
238
5.1
229
5.1
415
5.1
1236
5.1
477
5.4
232
5.4
361
5.4
502
5.4
371
5.7
12
5.7
39
5.7
88
5.7
18
6
180
6
381
6
694
6
691
6.3
12
6.3
57
6.3
76
6.3
78
6.6
123
6.6
182
6.6
174
6.6
183
Based off how many pieces are sold annually in the various lengths and keeping waste to a minimum, how do i work out what 2 or 3 long lengths would be the most efficient to use for cutting into the shorter lengths?
I have attemped to work this out myself but it's a bit beyond my mediocre excel skills. As i said earlier it does seem to be quite similar to the thread posted by Bruno back in 2003. Any help or advice would be much appreciated.
Thanks
Hi Guys sorry about the naming of this
but i rele havnt got a clue what this is that im asking for
anyone know if there is any where that i can get help on the naming of threads???
i need some simple formula for this
bar lengths are 5600
i have Quantitys and a cell with the total
length
i need to be able
to put in the length to be cut off
and then the ammount that i want.
so say i have 5 bars
with a total of 28000
and i need 7 lengths of 2000
i need to have a formula to see
how many full bars are needed
and what i will have left
so i will have 1600 x 3
and 3600 x 1
using 4 bars
any ideas
this could also be done in a form
Hi again,
I am trying to create a spreadsheet which calculates how many pieces of rod (its like a length of screwed bar) i can get from entering certain figures.
Basically Rod comes in 3m lengths, and in packs of 20 x 3m lengths.
We use varied amounts for each project.
What im trying to create is a sheet where i can input say upto 8 various lengths, the spreadsheet then calculates how many pieces i can get from a 3m length and ultimately how many lengths to order.
For ex, i might have 2 peices at 1.08m which means i only get 2 of those from a length but i might also have one at 0.7m which leaves me only 0.14 wastage.
I need to be able to enter how many and what lengths and let the spreadsheet do the math. At the moment im drawing lines on paper which im sure is not too hitech!
Any ideas to get me started would be appreciated,
Thanks
Lee
Does anyone know if Excel has the capability to optimize cutting layouts on a lineal length of material?
Example: I need to cut pieces of trim at lenghs of:
3 @ 48"
12 @ 13"
1 @ 21"
and so on...........
Stock sizes to cut from are 8, 10 and 12 foot.
Can Excel optimize the cutting layout and choose the correct stock lengths to minimize waste?
I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'10".....13' 9".. 12'8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
Any and all advice/help would be appricated.....
Hello All,
I have a value and need to divide it into standard quantities.
Lets say i have a length of 23.5m and i have standard sections of 5m, 3m & 1m.
I need a formula to divide the initial length into the standard sections but in whole lengths ie the "best fit" or match and rounding up any remainder to a whole length.
If it worked out i would like 4 x 5m, 1 x 3m and 1 x 1m.
If the formula could start at the larger size and work down that would be best as it would linit the number of sections.
I have tried roundup and other solutions but failed  please can anyone help.
Thanks in advance..
David
I am stumped as to "how to approach" the calculation my coworker has asked me about: "Do you know if there is way to make excel determine an efficient method to cut multiple lengths of beams from a quantity of standard lengths? Or have you heard of any other program or method to do this?
E.g. We are ordering 600 LF of beam, in 240" lengths, and need to cut these pieces out of it.
Qty*** Length
10***** 76
6****** 44
2****** 45
2****** 36
6****** 32
2****** 20
4****** 31
1****** 54
4****** 82
3****** 82
1****** 54

I jumped right to the "round up" formula but this won't take advantage of the excess material from other pieces/cuts. I don't think there is a "simple" approach or formula for this especially because the "multiple" quantities for each length.
We are trying to take advantage of the "leftover" pieces from the cuts: for example, only two each 82 inch pieces can be cut from the standard 240 inch length but the leftover piece will be 76 inches long which will provide one of the ten 76" pieces needed on the first line (or two of the 36" pieces). Ouch, my head hurts!
Any ideas on how to approach this...if even possible? Thank you all so much in advance and for the great help everyone is past, present & future!
Imagine a whole box of lengths of aluminium sections for making windows.
To make those windows I need to cut these lengths into smaller sizes.
I need to do this economically.
How can I use Excel to help me make this a reality.
Firstly you would take a big size out of it and then another size out of the offcut piece.
And then another size out of the offcut left over.
I need to know how many full stock lengths (6500) long it will take to cut all the pieces listed above.
Is it possible?
You need to take into account the quantity of each size and fit it all in together for the most optimal outcome.
See attached example of possible layout.
Hi all,
I've got a mathematical problem.
We use, at work, standard steel profiles with a length of 12 metres.
I need to calculate the optimum to devide the standard lengths into the needed lengths.
quantity
length
1
70
7,06
2
15
0,36
3
13
5,26
4
2
6,16
5
5
1,66
6
6
4,36
7
22
2,56
8
1
0,80
9
1
1,30
10
1
1,40
11
1
2,30
12
2
1,40
13
3
3,54
14
2
2,30
15
5
3,61
16
2
2,90
17
12
7,34
18
2
3,30
19
2
6,40
20
4
5,65
Every beam could be devided into several parts, not just 2, more is possible when needed. Even welding them together is possible, rather not when not needed, but possible.
Could somebody help me? I really have no idea were to start?!
Greeetings Mathijs.
I have a workbook I use as my "template" for some projects. I am simple in my excel usage. It is for cable lengths because I work for a place that we need this information. What I have is one column is cable name, one column is cable type, another is cable length. The lengths are fairly standard, say 25', 50' 75' so forth.
I found a function that will count the unique instances at the bottom of we can call it column c, but it just says my total is 5, as 5 different lengths. I want to some how have a function that says 75' 5, 100' 6 and so forth. I want summarize my different instances. How difficult would this be to do?
Matt
Hi all
I am a noob to excel and need help solving some problems.
(1st post was wrong titled lol..told you i was a noob head lmao apologies).
The 1st is i have a list of ten wood lengths and a total length (sum of them used) and i need to check how many times the largest lenght of wood goes into the total then the next smaller length etc etc until all ten have been checked leaving the remainder. So i can work out properly what lengths of wood are used. i.e 1, 5, 6, 8, etc into say 1668 meters total = 208.5 but if the number has 6 remainder i would like to then check again for 6 then 5 then 1 etc etc.
My second problem is when i copy and paste a row to a new sheet it adds it to the info already there and doesnt move it down a row ready for the next copy paste.
I would apprechiate any help, thanks in advance :
Hiya!
I'm Running Excel 2007 on Vista. Trying to put error bars on a bar chart. Standard error works fine but standard deviation doesn't work  I get a row of bars the same length, hovering way above the top of the data series bars in a straight line. Did all latest updates this morning. Is this incurable Vista weirdness? Anybody experienced this or knows how to fix it??? Cheers!
First time post.
I have been attempting to create code for a user form to show options of possible combinations of components to get to a user specified length.
I have components that come in 6 lengths (43,49,54,60,66) and I can use any combination or multiple of these lengths plus an single offcut length (0,3,5,8,11,16,19,25) to get within +/ 2 of the user specified length.
The user specified length can range from 43 through to 1000.
I have created tables of all possible combinations and have used lookup and match functions but I am thinking macro might reduce the size of this spreadsheet and enable further functionality such as choosing only 2 of the length variables to use instaed of looking at all 5.
Help on this code would be greatly appreciated.
Thanks
I have a chart:
A11 B11 C11 D11
Room
Qty
Width
Length
101A
1
60
50
101B
1
48
50
102A
1
60
50
102B
1
60
50
102C
1
48
50
103A
1
60
50
103B
1
60
50
104A
1
60
50
104B
1
60
50
104C
1
60
50
108A
1
60
50
108B
1
60
50
109A
1
48
50
109B
1
60
50
109C
1
60
50
109D
1
60
50
110A
1
48
50
110B
1
60
50
110C
1
60
50
116A
1
60
50
116B
1
60
50
122A
1
120
48
Is there a way to get excel to identify the width and length as 1 value then total up each time the value appears? I.E. :
6050 = 17
4850 = 5
12048= 1
This sheet will be used as a template and copied with different widths and lengths each time and would like to just be able to enter the widths and lengths and have it do an automatic tabulation each time without having to create a new value chart each time.
Huge thanks if someone can figure this one out!!!!!!
I work in an industry where we install wire (fire, security, etc), and one of the biggest challenges is to get the best yield of wire off a spool. We generaly buy rolls of wire that are 1000ft. If I need 7,500 feet of wire for a job and order 8,000ft of wire, I can still run out of wire if the lengths are not taken into account. For example, there may be 600ft left total on 8 rolls, but the longest length on any given roll is only 200ft, but yet I need 250ft for my last wire. Now I either have to splice wire (not recommended or acceptable in most cases) or buy another roll.
I currently have this job that has 52 different wire lengths, from 8ft to 386ft.
So my challenge is to create an excel spreadsheet that allows me to enter in the amount of rolls of wire, and how much wire is on each roll. Then enter in all the different lengths of wire required, and have it calculate which rolls to cut off how much so maximize my yield, so that each roll is left with the shortest amount possible, then the last roll with the majority of wire that should have been left over in a single length. So basically if I have 8 rolls of 1000ft of wire, and I only need 7,500ft, then the last roll should have 500ft left on it and the other rolls empty (in a perfect world).
Is this possible in Excel?
I have two worksheets, "Pricesheets" and "Orders".
Columns in Pricesheets (2500 rows):
 Customer (text)
 Plant (text)
 SKU (text)
 Standard lengths (text, like this: 48' 50' 60')
Colums in Orders (10,000 rows):
 Customer (Text)
 Plant (text)
 SKU (text)
 length ordered (integer)
For each order (a row in Orders), I want to find the matching row in
Pricesheets (based on Customer, Plant and SKU), then return a 1 if the
length ordered was listed in "standard lengths".
I've tried combinations of arrayentered SUM(IF()) and FIND(text, within)
functions, but I can't get it to work. On top of which, it takes a heckuva
long time to process.
Is there a more elegant solution?
Thanks.
Hi, I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.
I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.
equivalent lengths.xlsx
Thanks in advance
I have a chart:
A11 B11 C11 D11
Room
Qty
Width
Length
101A
1
60
50
101B
1
48
50
102A
1
60
50
102B
1
60
50
102C
1
48
50
103A
1
60
50
103B
1
60
50
104A
1
60
50
104B
1
60
50
104C
1
60
50
108A
1
60
50
108B
1
60
50
109A
1
48
50
109B
1
60
50
109C
1
60
50
109D
1
60
50
110A
1
48
50
110B
1
60
50
110C
1
60
50
116A
1
60
50
116B
1
60
50
122A
1
120
48
Is there a way to get excel to identify the width and length as 1 value then total up each time the value appears? I.E. :
6050 = 17
4850 = 5
12048= 1
This sheet will be used as a template and copied with different widths and lengths each time and would like to just be able to enter the widths and lengths and have it do an automatic tabulation each time without having to create a new value chart each time.
Huge thanks if you can figure this one out!!!!!!
Hi Guys I am trying to get a spreadsheet that can do the following 
I build sheds for a living and I need a program that I can put in the width and height of the shed panels and the formula can work out how many lengths of cladding I need with minimal waste.
Now for the tricky bit the cladding comes in set lengths of 3.65m, 5.85m & 8m long and is 255mm wide boards.
Shed 1
So if the front & back panels are 2.7m wide x 2.0m high I will need 8 x 255mm boards in hieght to get to 2.0m high & the best combo would be 5.85m boards as we would get 2 @ 2.7m out of each 5.85m length with least waste.
So if the side panels are 2.0m wide x 2.0m high I will need 8 x 255mm boards in hieght to get to 2.0m high & the best combo would be 8m boards as we would get 4 @ 2.0m out of each 8m length with least waste.
Know it gets harder again.
Shed 2
I have a shed with front & back panels that are 2.1m wide & 2.0m high I still need 8 boards high.
If the side panels are 1.8m wide then I need the formula to work out what is the best set lengths for both panels as they have a combined length of 2.0m (front & back) & 1.8m (sides) total 3.8m lengths so 8m lengths will be least waste as we can get 2 @ 2m & 2 @ 1.8m with least waste.
Hope this all makes sense!!!
Is this possible to write a formula that could do all this for me?
Thanks Shayne
Hi Guys,
I'm new to this, I was wondering if someone could help me? I'm looking for an Excell programme that will allow me too what i call "nest", certain lengths of steel into a larger length of steel????
For example:
If I needed to cut 12no lengths of steel @ 125mm long and 17 @ 2354mm long, and I could buy lengths of steel @ 10m and 12m ,
the programme would tell me how many lengths @ 10 & 12m I would need to buy in....Confused? I hope not
If you can do this, could you please tell me how as it would make my life a lot easier.
Many thanks in advance
Hello,
Hopefully I posted this right. I am not even sure how to title my request, but here is what I am looking for.
I have a 252" length of steel.(controlled)
I have various cut lengths requried. ie: 213", 31.87", 42", 77.74", etc.
I was wondering if there was a formula to calculate the best possible combination of various cut lengths to acheive as close as possible to the overall lenght of 252". This will help determine the total count of 252" lengths required.
I have set up a 2dimensional random walk chart. I'm interested in looking at the distribution of lengths obtained in 1,000 random walks. Each time I hit F9, I get
a plot of the 1,000 steps and a length in cell J5. It's easy to hit F9 and get both
a new chart and a new length, but it's tedious to copy the length for each say in
column K. That is, I'm hitting F9, getting a new pattern, copying length, then F9
and so on.
Here's my question: Is there some way for me to only have to hit F9 so the length is
automatically captured? That is, after I hit F9 500 times, I'll have a column with the
500 associated lengths.
Thanks,
ric
http://www.triplescreenmethod.com
http://www.twitter.com/triplescreen
I have a list of 11 items that are available in either 2, 3 or 4 lengths depending on the item being considered.
The task is to find and list the smallest number of lengths that are required to construct the whole length.
So, what I need is:
Required Length of item(2) = L
item(1)  (available lengths) a,b,c,d
item(2)  b,c,d
item(3)  b,d
...
item(11)  b,d
There may be numerous solutions (n) to derive L, so I want
L = x.a + y.b + z.c
Where x+y+z is the minimum of x(n)+y(n)+z(n)
I'm creating a dashboard report using the new Data Bars options under conditional formatting of Excel 2007.
I already have one set of data bars created on data that ranges from 2 up to 320.
I now want to add another set of data bars to a different set of data with a much smaller number range (10 to 55).
Both data sets will increase week on week, but the maximum number in the second data set will always be about 5 or 6 times less than the maximum value in the first data set.
Is there any way  using the data bars  to display the length of the bar so that it relates to the length of the bars in the first data set? In other words, a value of 50 in both data sets will have a data bar of the same length?
UPDATE:
I've figured out a way to do it by rearranging my data so that I had the figures for both data sets in the same column and just creating a single range with the data bars conditional formating. Simples!