Tracking aluminum quantities

MartaGH

New Member
Joined
Nov 14, 2018
Messages
4
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

wwbwb

Well-known Member
Joined
Oct 20, 2003
Messages
513
Welcome to the board!

Try this:

Code:
=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

New Member
Joined
Nov 14, 2018
Messages
4
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!!!!!
 

Zenwood

Board Regular
Joined
Sep 2, 2017
Messages
64
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

New Member
Joined
Nov 14, 2018
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top