Bar Cutting Optimization???? 


Bar Cutting Optimization????  Excel 
View Answers 
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
Formula & Conditional Formatting To Show Waste From Cutting Rebar
 Download file: http://people.highline.edu/mgirvin/
Learn how to create a formula and Conditional Formatting that can calculate and show the waste ...
Learn how to create a formula and Conditional Formatting that can calculate and show the waste ...
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 ...
#10 Video topics:
1)See the differences b ...
Helpful Excel Macros
Create a Line Chart with a Macro in Excel
 Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
 Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
 Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
 Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Create a Column Chart with a Macro in Excel
 This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
 This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
WEEKNUM UDF for Excel Versions That Don't Contain This Function  UDF
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
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
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
At minimum: I need to make a sheet that takes a single cut length (i.e. 16 inches), then looks up the best yield for that cut out of various standard lengths of lumber by priority (i.e. 6, 8, 10, 12, 14, 16)
Ultimately: I want a sheet that can take multiple quantities and cut lengths (i.e. 4 at 16", 7 at 24", and 3 at 27"), then look up the best yield for those cuts out of various standard lengths of lumber.
My goal is to be able to have the spreadsheet tell a guy running a saw how each board should be cut to maximize yield and minimize scrap/waste from the lumber.
I don't need a "drawing" like this, but I need this info in a numerical format: 7, 16" cuts and 1, 24" cut out of 2, 6 foot boards would look like this:
[16"16"16"24"0 WASTE]
[16"16"16"16"8" WASTE]
Ideas?
Is there a formula to calculate this
I need to know how many furring channels, and what cuts I should make to optimize wastage.
The room is 3.63 meters (length) x 4.1 meters (width)
The channels only come in 6 meter lengths so if i cut each length at 3.63 meters I have wasted 2.37 meters
The spacing for the width would be at .6 meters so I think i need 7 lengths at 3.63 meters
My rough guess is if I cut the 6 meter lengths at 1.2 metres
A formula would be much easier to work these problems out for future rooms
I would like to avoid as much wastage as possible
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
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
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?
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.....
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 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!
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.
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'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
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 :
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!
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!
Hi,
We are in having a requirement to cut Steel Angles as per Required length from the available Stock Length. Example is given below:
Steel Section  MS45x45x4
Stock Length  11 Mtrs  3 Pc / 12 Mtrs  4 Pc / 10.5 Mtrs  8 Pc
Fabrication Requirement :
Mark No. X  3.4 Mtrs  10 Pc
Mark No. Y  2.8 Mtrs  15 Pc
Mark No. Z  3.8 Mtrs  20 Pc
We need a solution which Stock Length and How many number of pc to be used with the cutting instruction so that we minimize the scrap  Wastage.
Regards
Ketan Parikh
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 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!!!!!!
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 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.
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 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!!!!!!
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
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
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
Col A has wire part numbers. Many are duplicated.
Col H has wire lengths for those part numbers. Some lengths may be duplicates for the wire part number. (by design)
In another column, I'm using SUMIF to calculate the total wire length used for each wire type.
The problem: Need to look at the col A part number, and if a length for that part number is duplicated or just has a single instance, I need to multiply a single instance of that piece of wire by 2.
Example:
(source data)
Wire 1 length 12
Wire 1 length 12
Wire 1 length 15
Wire 2 length 6
Wire 3 length 55
Wire 3 length 20
Result:
SUMIF column: (SUMIF(A:A,wire_fromA,H:H))
Wire 1 Length 39
Wire 2 length 6
Wire 3 length 75
Setup Column (the problem column) (sum of unique lengths for that part number multiplied by 2.)
Wire 1 length 54
Wire 2 length 12
Wire 3 length 150
Thanks for any help obtaining a setup value. Plenty of room for helper columns. I tried creating unique values by combining col A and col H, counting the instances. . . but don't exactly know if that might contribute to a correct approach.
Peter