Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

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

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Labels:

- Tags:
- @Riny_van_Eekelen

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 19 2020 11:47 PM

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

- Tags:
- Best Cut rev.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dec 20 2020 02:17 AM

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.