Mar 10 2020 04:00 AM - edited Mar 10 2020 04:05 AM
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
Mar 16 2020 03:11 AM
@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.
Apr 28 2020 06:17 PM
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
Dec 19 2020 11:47 PM
Dec 20 2020 02:17 AM