Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Bar Cutting Optimization????

Forum Register
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

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 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
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
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
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

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




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


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 hi-tech!

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 co-worker 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




Hello,

So far the people here have helped me so much with this it has been amazing. but I have another question. Please see the attached Excel file to understand what I am about to say.

CUTLIST MASTER COPY V2.xlsm

I am looking to add a column to the pivot table on the Material Summery Sheet. This column would be called yield. If you look at the CutList page you will see it is made up of different lengths and qty of different materials. On the material summery page it grabs all of the similar materials converts their lengths to feet, and than totals everything for me. Here's the problem, because of sizes and lengths I need more info than just the total length of material, i need to know how many lengths of said material I need.

Here's an example:

I have one material say: Steel I know that I can only buy steel in 24ft lengths.
On the CutList there are three entry's for steel:

1 qty at 228 in.
3 qty at 156 in.
15 qty at 36 ft.

So in total i have 1236 inches of Steel. Which is 103 ft. Which in theory tells me exactly how much Steel I would need to purchase. But that's where the fun comes in I can only buy steel in 24 ft lengths and those pieces of steel would need to be broken up in a different way to make sure I can cut all my pieces to the same length. In reality I would need more than 103ft of steel.

So I am looking to add a column to the pivot table or a brand new system to show totals that allows me to:

1. Beside each material on the totals list enter a length that it must be broken up into.
2. And than have the program calculate how to best divide said material into said sizes.

My math skills aren't the best but I am sure someone here knows a thing o two about how to do this.

Any help at ALL even suggestions of different ideas are very welcome.

Thankyou so much in advance

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!




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 - MS-45x45x4

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 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 array-entered 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