USING NESTED LOOP FORMULAS TO MANAGE VERY LARGE NUMBER OF DATA

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

0 Replies