How many pieces in a bar

Hello,

Hope you are all keeping well, safe, healthy and happy during these crazy times!

Hoping also for some help and thanking in advance anyone willing to lend a hand:

How would I go about calculating how many bars of aluminum I need in order to fulfill an order that requires me to cut each bar into smaller pieces of a certain size?

For example: If I get an order for 120 frames measuring 12" x 18", how do I calculate how many bars of aluminum (I buy them in a standard length of 160") I will need so I can order the right amount?

For this particular example, I would need 2 cuts of each size (2 cuts of 18" and 2 cuts of 12") per frame (frames are rectangular and need 4 sides) … which means I would need to cut a total of 240 cuts of 18" each and 240 cuts of 12" each... how many bars would that be and how much material I would have left from each bar that I could potentially re-use for future orders?

I have been searching online and I saw a reference to a Quotient formula to find out the number of cuts and a MOD formula to determine what is left... but I have been trying to no avail to make it work and I have not been able to figure it out. Maybe there is another way or maybe I am just doing it wrong.

I would be very grateful if there was a way that I could input in a Column the width, in another column the length, in a third column the quantity and have the next 2 columns give me the answers of how many bars in total and how much waste per bar for this order.

Thank you very, very much for any suggestions you may offer.

Summer

Oh wow... and here I was thinking the solution would be simple... holy cow!!

Lots of reading to do... thank you!

And a solver setup as well. I tested both excel solver and OpenSolver. No difference in the number of bars needed and total wastage. OpenSolver found a solution in a matter of
seconds but excel solver oscillated for quite some time so I set a time limit of 10 second.

I also assumed a rectangular frame so for 120 frames you would need 120*2 18" and the same number of 12" pieces. As the waste pr bar is 4" I don't think you need to consider cut
thickness or?

the biggest difference between OpenSolver and Excel solver is that OpenSolver only uses 2 cutting pattern as opposed to excel that uses 4 different cutting pattern.

Alf

Thank you SOOOO much!!! This is fantastic!! I will test the solutions but I am sure they will work great. You guys rock!!

I was not aware of the reputation star but now I am and it is done. Thank you!!!

As soon as I test the solutions I will mark as solved.

Thank you again!