Hello,
I am not sure if what I want to create is possible but any assistance or guidance in the right direction will be very much appreciated. I am trying to create a spreadsheet that will allow me to track the aluminum and drywall needed in the fabrication of certain frames that we make.
The aluminum comes in bars that are 157.48 inches long. The drywall comes in sheets that are 4ft x 8ft.
What I am trying to achieve:
Two orders comes in, for example: One is for 8 units that are 18"x48" and the other is for 4 units 12"x12".
I have a row for each order. In the "Quantity" column I would input 8 and 4 respectively. In the "Horizontal Side" column, I would input 18" and 12" respectively and in the "Vertical Side" column, I would input 18" and 12".
Now the fun starts: I would like excel to somehow be able to calculate exactly how much aluminum and how much drywall I need to manufacture those frames. Meaning, the system should "know" that to make the 8 18x48 frames I will need 8 bars of aluminum:
- 2 bars are used for the 18" (18*4=144" out of the 157.48" bar and I am left with 2 pieces of scrap that are 13.48" long)
- 6 bars are used for the 48" (15 pieces of 48*3=144" out of the 157.48" bar, and I am left with 5 pieces of scrap that are 13.48" long plus 1 more 48" piece out of the 6th bar and I am left with a bar 109.48" long).
Then, the formula should capture that there are now 7 pieces that are 13.48" long plus one more piece that is 109.48" long so, when the next order gets calculated, it uses up those pieces of aluminum (if they are big enough) before it starts using up a new aluminum bar.
So, for order #2, I would require a total of 16 12" pieces. The formula would recognize that there are 7 pieces that are 13.48" long that can be used to make 7 of the 16 12" frames, then realize that the remaining 9 pieces can be made out of the 109.48" bar.
Same idea with the drywall piece... the formula should calculate how many of the 8 18"*48" panels can be cut out of the 4ft x 8ft sheets and recognize that the 2 12"x12" may be cut out of the remaining pieces.
I have the formula that calculates how many bars are needed to manufacture the frames:
=IF([@Quantity]="","",ROUNDUP(([@Quantity]*[@[Inside Panel Horizontal]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Horizontal]],0)*[@[Inside Panel Horizontal]],0)),0)+ROUNDUP(([@Quantity]*[@[Inside Panel Vertical]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Vertical]],0)*[@[Inside Panel Vertical]],0)),0)
I have not been able to figure out how to do the rest and I am coming to you for help... Does any of you believe this formula/macro is possible?
Thank you again in advance!!
Marta
