Tracking aluminum quantities

[MartaGH]

Hello,

New to this forum and thanking you in advance for any assistance you can offer.

This is what I am attempting to do: Me and my husband recently opened a small shop to fabricate aluminum frames. The orders are starting to come in and I am trying to come up with a system that will allow me to track how much aluminum we have left so I can reorder when it gets to a certain point. The frames are usually custom sizes.

The aluminum comes from the factory in a bar that is 157.48" long. What I have been attempting to create (without success) is a formula that will capture the length of the frame in the order multiplied by 2 plus the width of the frame in the order multiplied by 2, deduct it from the original 157.48" and then calculate if there is enough aluminum in that original bar to complete that frame or if another bar is needed.

For example... we get an order to manufacture 8 frames 18"x48".
My current formula: 8*18"=144"*2=288" ---- 288/157=2 (rounded up) ----- 157.48-144= 13" ----- which means that to fabricate this we would need 2 bars of aluminum and after we finish the cutting, we would be left with 2 pieces of 13" each.
For the other 2 sides of the frame, this would happen:
My current formula: 8*48"=384"*2=768"----- 768/157=5 bars (rounded up) ... but his is inaccurate because we need a whole piece of aluminum to build a frame: To cut 16 48" frames, we would actually need 6 bars because out of one 157" bar we can only cut 3 48" frames. The remaining 13" piece is discarded and another bar is then used to cut the next 3... and so on until all 16 are cut.

I am not sure if Excel can do this... I am fairly certain that there is software out there designed to track this sort of thing but we are just starting and can't afford it. Right now we are going to the back and physically counting the bars, which takes too long and is not efficient at all. I was hoping for a more automated way to do it.

Again, any assistance would be greatly appreciated!!!

Thank you!!!!

Marta

 

[wwbwb]

Welcome to the board!

Try this:

=ROUNDUP((B2*C2*2)/(A2-ROUNDDOWN(A2-ROUNDDOWN(A2/C2,0)*C2,0)),0)

A2- Bar Length (157.48)
B2- Number of frames (8)
C2- Length of 2nd side (48)

 

[MartaGH]

I don't know if you wear a pointy hat and a white beard but you can certainly do magic! Thank you SO MUCH!!! works like a charm!!!!!

 

[wwbwb]

Thank you! Good luck!

 

[Zenwood]

What about the 1st side? The 18" sides?

You can get two 48" pieces and two 18" pieces from a single bar. Still need eight bars with 25" extra each.

 

[MartaGH]

Hi Zen,

Yes, you are correct. I have been racking my brains to see if I can come up with a way to combine the 2 measurements into one formula so it calculates the actual number of bars used and tells me how much scrap is left behind afterwards... and then come up with another formula/macro that will recognize that there are scrap pieces left from previous orders that can be long enough to utilize in a new order.

For example if after cutting Side 1 I am left with a 45" piece, I can then use that piece to start cutting Side 2. If after cutting all the Side 2s I am left with a 15" piece, then a future order of 12" can pick it up to use it.

It appears that achieving this on my own is way above my excel skills at the moment... I was thinking of making a new post to see if anyone had an idea.

Thank you!!!

Marta