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

Occasional Contributor
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):
L35.672.486103.5
N10025150701502507020
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))
L35.672.486103.5
n max42151213
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
L35.672.486103.5
n max42151213
LOSS FACTOR00.8504021.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

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

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 

I am interested this excel file.Please tell me sbout this excel as possible as you can.
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.