Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

View Answers     

Similar Excel Tutorials

Introducing Logic into Formulas and Functions in Excel
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
Insert and Manage Page Breaks in Excel
How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and confusing but this tutori ...

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

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

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.


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.


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


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.

Ketan Parikh

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 am required to nest certain lengths of items together to get the smallest physical length I can to keep length required as small as possible and eliminate waste. In the attached file you will see that items are catagorized by column F and then sorted shortest to longest using column N. As in the example of Rows 2 thru 6, I thought the best way for me to figure on the shortest length of beam required would be to take 3 times the longest post length (cell N60) and 3 times the shortest post length (cell N2) and add them together. (For reference, each BEAM will produce six POSTS in this particular situation). This returned a value of 26.01 ft which I rounded up to 26' 4" (all order lengths of beams need to be rounded up to the nearest 4" increment). I then add a determined amount of allowable scrap to the final length to determine what the optimal purchasing length would be. I "thought" this would work fine, however, when I started to nest the three longest with the three shortest posts and so forth, as I approached the middle of the pack, the total length of all six posts was starting to exceed the purchased length of the beams.

Is there a macro that could be writen where I could highlight a specific cell range, run the macro which would promt me for the number of posts per beam, the macro would then automatically group the rows together so as to keep the scrap value as low as possible? Then, maybe highlight every other grouping so as to make the grouping be more visible? I tried using the average, the mean, etc. but couldn't find anything that worked.

Any help would be greatly appreciated.



I am trying to create a string of text that grabs info from other cells, which is easy so for example


would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.

Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this

At the moment


Would like


This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.

What formula do I use for that?

I want to create a file with fixed length records (lines). The fields
(columns) have defined lengths. When I export the file, it should be in a
flat text format, there should be no spacing between columns of data, and the
total line length should be exactly what I want.

How do I do this? What format should I export the data into?


Not sure how to go about doing this. I want to time the text of subtitle so it displays the specific text at the right time.

I have column A which is the starting frame number. Column C is the Text and they are different lengths in each row.

Column A Column C
00003 This is the text column and in different lengths.
00055 This is the second subtitle text in the column and long
00225 This is the third subtitle text.

What I want to do is depending on the length of the text in column C, have excel insert another Row (lets say if text is longer that 15 characters) everything else in text gets shifted down to the next newly inserted row and if another 15 characters is passed then that gets inserted into another newly added row right below it and so on.
Now there is A:1 which has 00003 , A:2 and A:3 is going to be blank.
A:4 is 00055. I want A:2 and A:3 to be the number which evenly fits between 00003 and 00055. So the new inserted one would look like this:

Column A Column C
00003 This is the text
00021 column and in different
00038 lengths.
00055 This is the second subtitle....

I only want the text which gets inserted to the new row to be cut off during spaces and not in the middle of a word. In the example above I didn't count the 15 character length for text.

Hope this makes sense to someone out there and can help me. Any help appreciated. Thanks


Is there some way to assign custom error bars to many different data series on a chart all at the same time? I will try to describe what I mean with a pictu

So the picture has three data series (types 1, 2, and 3) all plotted against time (day). Below the means are the standared deviations corresponding to each point above.

I want to use the standard deviations as the magnitude of the error bars. In my actual workbook I many several more series and it has become difficult to add the error bars to each data series individually. If possible I would like to tell excel that for a particular chart it should look to the corresponding cells below for error bars for all the series on the chart.

Thank you very much,


Hi again:

I need to cut rows from a filtered list and move to another place on the same spreadsheet. I don't want to sort and cut so I need to filter and cut.

Is there a way to do that without cutting what is hidden?



How can i fix the character lengths for following columns. I am not sure if there is a way to fix the length or will it be a case of vb edit deleting characters.

A 2
B 16
C 6
D 6
E 42
F 14
G 9
H 14
I 6
J 1
K 2



I want to enter a paragraph of text into an excel cell. SOmetimes its one or 2 lines and sometimes its 10 or so.
Id like that all the cells would be the same size, e.g. display 5 lines. Therefore if the cell has 10 lines of text the cell would have scroll bars in it.

Ive done some browsing and come up with nothing.

I then looked into using Text Boxes. These looked promising. I can setup a text box so i can enter multiple lines and have a vertical scroll bar. I want the size of the textbox to match the size of the cell. So if i make the cell bigger/smaller the textbox will also re-size. The textbox does have Format Control - Properties - "Move and size with cells". The problem is that this control seems to scale the text box. If i make the cell bigger, the font gets larger and distorted and the scroll bars get bigger(distorted). If i make the cell smaller, the font gets small etc..

Anyone have a solution?


Hi forum,

I'm trying to come up with a formula for the above.

I have a range of cells A1:A100 and each cell can have differing string lengths. I need to count the number of times the string length of each cell is greater than X. Lets say in cell B1 the value of X is stored.

I did try COUNTIF in combination with LEN , but couldn't get this to work

Many thanks

I create files on a mainframe that are both fixed-length AND delimited (usually semi-colon) and load them into Excel as .txt. I bring them into Excel as delimited but have to individually adjust COLUMN width. Since I frequently have 20 or more columns, this is tedious. I know there must be a way that Excel can figure out my lengths, but I don't know what am I doing wrong (either in Excel or upload from mainframe). Thanks.

I have a very simple stacked bar chart with only two bars showing the results I need. I want to create thin bars and reduce the gap between them to the same thickness as the bars.

I can reduce the gap but doing so increases the thickness of the bars, which does not produce the effect I want.

This has to be so simple but I just can't figure it out.



Using Excel 2002.


I'm trying to create a bar chart with three series of data, which have different standard errors. When I add the error bars to the chart, they're all the same height, but the correct values. When I try to click the custom button to select the series to change, the dialogue box that should come up asking to choose the series doesn't appear.

Can anyone see where I've gone wrong?


I would like to create a bar graph (in Excel or PP) that contains multiple
groups of information. For example, on one graph, a group of bars of "gender"
with bars of male and female, then a group of bars of "race" with bars of
black, white, Hispanic, then a group of bars of "education" with <HS, HS,
HS+, etc.

Right now I can create the different groups, but the legend remains
constant, so my "race" group has bars of male and female!

Any suggestions would be appreciated!



I have columns of varying lengths. I would like to average the values within a defined fraction of each column. For instance, if I wanted the first 20% of the length, I would want average(A1:A5) of a column with 25 values, but average(A1:A10) for a column with 50 values.

I can use count() to determine the length of the column of values.

How can I specify a range of cells based on the result of that calculation?



can anyone advise me if it is possible to turn off the Horizonatal and the Vertical scroll bars on one worksheet only, because I have tried turning them off on the work sheet I require them to be turned off but it also applies to the other worksheets in my workbook.

I would be most gratful for any assistance in this matter

Many thanks in advance


Excel Version 2003


I am using the 2004 mac version of excel. I want to use the standard error of the mean for error bars. This means, that I want to use individual error bars for each data point, rather than the same for each condition. I have calculated these numbers by hand, and want to input them into the graph. However, it only allows for 1 data value for every condition, rather than each data point.

In old versions of Excel, the maximum Worksheet Name length is 31 characters (inc. blanks). Does this change in any later version of Excel? I am using a software package which renames Worksheets to a Title which can be any length (usually max 70 characters).

I have a lot of text data in a column. The text is different lengths.(see
below). I want to remove the last letter. I cannot use text to columns
because it left justifies(is there some way to right justify). I tries left
and right functions but becuase the text is different lengths (some 5, 6,7,
10 characters etc.). this does not work. I looked at truncate but it is
text not numbers to this does not work.
Finally I thought of Find and Replace but because the letter may be in the
body of the text as well as a suffix, this does not work. It cannot be done
by hand accurately becasue I have thousands of items. Any Ideas????

Thanks for taking time to look at this. I am trying to strip off the last
one or
two characters from the column B list. Since the text is different length
cannot be done with text to colums -this uses a left justify. I tried right
and left functions
but again with no luck. The find & replace won"t work because the same
letter sometimes
is in the number as well as at the end. There are too many to do it by hand.
Any ideas?

Original Corrected

SYJ600002 SYJ600002
SYJ600002 SYJ600002
SYJ6014004 SYJ6014004
M680972 M680972
M580619T M580619 Remomve T
SYJ600002 SYJ600002
SYJ600002 SYJ600002
SYJ6000001 SYJ6000001
SYJ6000001 SYJ6000001
SYJ6009004 SYJ6009004
SYJ6009010 SYJ6009010
226540A 226540 Remove A
SYJ6011003E SYJ6011003 Remove E
404881A 404881 Remove A
SY600000071 SY600000071
SYN50479B SYN50479 Remove B
CSQ060059 CSQ060059
SYJ6030014 SYJ6030014
T4436605 T4436605
T4436605 T4436605
BO4K440674C BO4K440674 Remove C
405247A 405247 Remove A
SYJ600002 SYJ600002
SYJ6014004 SYJ6014004
SYJ6014004 SYJ6014004
SYJ6026001 SYJ6026001
SYJ6000001 SYJ6000001
SYJ6011003G SYJ6011003 Remove G
405464A 405464 Remove A
SYJ6026002 SYJ6026002
CSYN50003 CSYN50003
CSYN50003 CSYN50003

I am match captain for a golf league which entails providing from a membership of 70 odd players a list of foursomes randomly chosen. I have tried several of the solutions in the random list thread and have been able to get some of them to work. I attach an example of what I currently use. There are two features that would be nice to have.
1) it would be nice to have a start/stop button so that the randomization does not run until I want it to and stops after I have the list randomized. I have tried disabling macros and setting security level very high but in both cases I get randomization when I don't want it.
2) It would be nice to arbitrarily select the list length since varying numbers of golfers show up to golf every week. I tried one of the programs that claimed to let me set number of choices and list length but it did not work. What I do now is edit the program I have to various lengths, or have several programs for differing numbers of players


I've been trying to build a code that draws a series of rectangles with different widths and lengths based on the contents in a range of cells. The code works but the problem is that although I used the Application.CentimeterstoPixels method, when I print the drawing, there is a noticeble discrepancy (about 5-6%) in the length of the lines. I think that this might have to do with taking into account the screen resolution but I don't know for sure.

Can anyone please illuminate me on this?