# Steel Rebars Cutting List Optimization Using Excel Formulas (for very large number of probabilities)

Occasional Contributor

# Steel Rebars Cutting List Optimization Using Excel Formulas (for very large number of probabilities)

 For Excel Experts : I need your Help to solve the following problem using Excel formulas to be repeated automatically everytime with no need to use tools or macros (only formulas) I am a civil engineer and I need to prepare and print (Rebar Cutting List) to be ready for use by Steel Fixers according to the following given data (Rebar Diameters are uniqe = Ø 16mm): L 3 5.6 7 2.4 8 6 10 3.5 N 100 25 150 70 150 250 70 20 L = required bar Length to be cut N = total required number from each rebar length (L) It is well known that standard rebar Length =12.00m (Produced by factories and delivered to projects in bundles) This means that maximum number obtained from single standard rebar for each separate required length can be calculated from (n max. = INT( 12/L)) L 3 5.6 7 2.4 8 6 10 3.5 n max 4 2 1 5 1 2 1 3 each separate required length can be cut n times for each single standard rebar n varies ( from 0 to n max) We can get Losses factor for each length which indicates how much can this length produce Losses fro the standard rebar if this length was cut separately Losses factor = 12 - n max. x L L 3 5.6 7 2.4 8 6 10 3.5 n max 4 2 1 5 1 2 1 3 LOSS FACTOR 0 0.8 5 0 4 0 2 1.5 Values that has Losses factor = 0 do not produce losses if cut separately Whenever Losses factor value increased this means that the corresponding length produces Bigger Losses if cut separately And Losses obtained from each cutting operation can be calculated from the equation: Loss = 12 - n1 x L1 - n2 x L2 - n3 x L3 - …………..ect. Loss = 12 - n1 x 3.0 - n2 x 5.6 - n3 x 7.0  - n4 x 2.4  - n5 x 8.0  - n6 x 6.0  - n7 x 10.0  - n8 x 3.5 n1 varies (from 0 to 4) - n2 varies (from 0 to 2) -n3 varies (from 0 to 1) -and so on Best cut operation should satisfy two conditions: 1- Priority to minimum Losses value> 2-Maximum value of Losses factor (the 2nd condition is to guarantee to get rid of bad lengths at the beginning of cutting list) Now it is required to print best cutting list operaions following the next steps: 1- find the best cut operaion which satisfies the above given conditions for the given lengths 2- Repeat step No.1 until we finish the first required amount of the first finished length 3- Repeat steps 1&2 after excluding the ended length obtained from step No.2 and do the same for the remaining lengths 4- Find Methodology to get the above solution in case of 100 rebar Lengths and different 17 values of rebar diameters

Attached a trial for explaining required printed data in sheet-2

4 Replies

# Re: Steel Rebars Cutting List Optimization Using Excel Formulas (for very large number of probabilit

@Mahmoud_Hossam_El_Deen Have bumped into this tread over and over again, trying to understand the issue and come up with a solution. Came up with a table that allows you to do some What-If analysis.

Re-using left-overs from lengths that allow only 1 pc out 1 rebar seems to be way to minimise waste. E.g. L 7 and 8 produce waste of 5 and 4 meters respectively. These are long enough to be used for some of the shorter lengths required (2.4, 3 and 3.5).

My table in the attached workbook is far from automatic. It would have to be re-done for every different set-up, but it may help you finding a solution or your problem. Move around the numbers in R7:T9 and see what happens. If not helpful, kindly ignore this post.

# Re: Steel Rebars Cutting List Optimization Using Excel Formulas (for very large number of probabilit

Thanks for your answer, your respond was creative as usual, But It should be Automatic and number of inputs shouldn't be limitted.

Thanks again@Riny_van_Eekelen

# Re: Steel Rebars Cutting List Optimization Using Excel Formulas (for very large number of probabilit

I am interested this excel file.Please tell me sbout this excel as possible as you can.

# Re: Steel Rebars Cutting List Optimization Using Excel Formulas (for very large number of probabilit

I want to know various type of cutting lengths are to be the number of 12 Metre lengths. So, Which formula can use? Tell me, please.