Sheet metal estimating problem
 

I am wanting to put together an equation that will do the following. I
buy sheet metal to be stamped into various parts and the sheets come
in 3 widths, 30", 36" and 48". They also come in different lengths of
96" and 120".
When estimating the cost of a part I need to determine the best sheet
size to use. By reading the blueprint I can firgure the length and
width of one piece. then I need to divided the width and length of the
available sheet sizes to determine which one will give me the least
amount of scrap. For example: the part is 3.5" wide X 5" long. The
width will determine the best sheet width and the length, the best
sheet length. I'll divide 30" by 3.5 = 8.57. I will always round down
as a portion of a part doesn't work. Then I try 36" = 10.29 and
finally 48" = 13.71. Based on the amount to the right of the decimal,
36" is best as the scrap is only .29. Next I do the same thing with
the length: 96 / 5 = 19.2 and 120/5 = 24. The most optimum sheet for
me to use for this part is 36" X 120". I want to be able to list the
sheet sizes available to purchase as not all thicknesses of metal come
in the various widths and lengths. Once I determine the best sheet
size, I want to take the two results and multiply them together to
learn how many pieces one sheet can make. In this example it would be
10 X 24 or 240 pieces.

Is someone able to help me with this?

Thank you.

Sheet metal estimating problem
 

I didn't use one formula but multiple cells. My Results are shown below in
the range A1:G8.

I typed in data to cells A1:B2, A4:A8, E4:E5
I enter formula in cells B4:C8, F4:G5 (some cells are blank)


A B C E F G

1 width Length
2 3.5 5

4 30 8.571428571 0 96 19.2 0
5 36 10.28571429 1 120 24 1
6 48 13.71428571 0

8 Pieces 240

B4:B6 - enter B4 and copy to B5 and B6
=A4/A$2
=A5/A$2
=A6/A$2

C4:C6 - enter in c4 and copy to C5 and C6
=IF((B4-INT(B4))=MIN(B$4-INT(B$4),B$5-INT(B$5),B$6-INT(B$6)),1,0)
=IF((B5-INT(B5))=MIN(B$4-INT(B$4),B$5-INT(B$5),B$6-INT(B$6)),1,0)
=IF((B6-INT(B6))=MIN(B$4-INT(B$4),B$5-INT(B$5),B$6-INT(B$6)),1,0)

F4:F5 - enter in f4 and copy to F5
=E4/E$2
=E5/E$2

G4:G5 - endter in G4 and copy to g5
=IF((F4-INT(F4))=MIN(F$4-INT(F$4),F$5-INT(F$5),F$6-INT(F$6)),1,0)
=IF((F5-INT(F5))=MIN(F$4-INT(F$4),F$5-INT(F$5),F$6-INT(F$6)),1,0)

B8
=INT(SUMPRODUCT(B4:B6,C4:C6))*INT(SUMPRODUCT(F4:F5 ,G4:G5))


"rudyeb" wrote:

I am wanting to put together an equation that will do the following. I
buy sheet metal to be stamped into various parts and the sheets come
in 3 widths, 30", 36" and 48". They also come in different lengths of
96" and 120".
When estimating the cost of a part I need to determine the best sheet
size to use. By reading the blueprint I can firgure the length and
width of one piece. then I need to divided the width and length of the
available sheet sizes to determine which one will give me the least
amount of scrap. For example: the part is 3.5" wide X 5" long. The
width will determine the best sheet width and the length, the best
sheet length. I'll divide 30" by 3.5 = 8.57. I will always round down
as a portion of a part doesn't work. Then I try 36" = 10.29 and
finally 48" = 13.71. Based on the amount to the right of the decimal,
36" is best as the scrap is only .29. Next I do the same thing with
the length: 96 / 5 = 19.2 and 120/5 = 24. The most optimum sheet for
me to use for this part is 36" X 120". I want to be able to list the
sheet sizes available to purchase as not all thicknesses of metal come
in the various widths and lengths. Once I determine the best sheet
size, I want to take the two results and multiply them together to
learn how many pieces one sheet can make. In this example it would be
10 X 24 or 240 pieces.

Is someone able to help me with this?

Thank you.

Sheet metal estimating problem
 

With the layout above, you could also try:

=LOOKUP(2,1/FREQUENCY(0,MOD(A4:A6,A2)),A4:A6)
=LOOKUP(2,1/FREQUENCY(0,MOD(E4:A5,A2)),E4:E5)

for the Width and Length respectively and then the number of pieces is:

=INT(Width/A2)*INT(Length/B2)

Ideally i would have thought that the calculation would require the total
number of pieces as an input, so that you could minimise the total scrap
usage but i suppose if enough sheets are used, it should be a close
approximation.

"rudyeb" wrote:

I am wanting to put together an equation that will do the following. I
buy sheet metal to be stamped into various parts and the sheets come
in 3 widths, 30", 36" and 48". They also come in different lengths of
96" and 120".
When estimating the cost of a part I need to determine the best sheet
size to use. By reading the blueprint I can firgure the length and
width of one piece. then I need to divided the width and length of the
available sheet sizes to determine which one will give me the least
amount of scrap. For example: the part is 3.5" wide X 5" long. The
width will determine the best sheet width and the length, the best
sheet length. I'll divide 30" by 3.5 = 8.57. I will always round down
as a portion of a part doesn't work. Then I try 36" = 10.29 and
finally 48" = 13.71. Based on the amount to the right of the decimal,
36" is best as the scrap is only .29. Next I do the same thing with
the length: 96 / 5 = 19.2 and 120/5 = 24. The most optimum sheet for
me to use for this part is 36" X 120". I want to be able to list the
sheet sizes available to purchase as not all thicknesses of metal come
in the various widths and lengths. Once I determine the best sheet
size, I want to take the two results and multiply them together to
learn how many pieces one sheet can make. In this example it would be
10 X 24 or 240 pieces.

Is someone able to help me with this?

Thank you.

Sheet metal estimating problem
 

I found a slight error with my columns when I posted the data. My worksheet
is ok.

From B1 to E1 the word length
From B2 to E2 the number 5.

"Lori" wrote:

With the layout above, you could also try:

=LOOKUP(2,1/FREQUENCY(0,MOD(A4:A6,A2)),A4:A6)
=LOOKUP(2,1/FREQUENCY(0,MOD(E4:A5,A2)),E4:E5)

for the Width and Length respectively and then the number of pieces is:

=INT(Width/A2)*INT(Length/B2)

Ideally i would have thought that the calculation would require the total
number of pieces as an input, so that you could minimise the total scrap
usage but i suppose if enough sheets are used, it should be a close
approximation.

"rudyeb" wrote:

I am wanting to put together an equation that will do the following. I
buy sheet metal to be stamped into various parts and the sheets come
in 3 widths, 30", 36" and 48". They also come in different lengths of
96" and 120".
When estimating the cost of a part I need to determine the best sheet
size to use. By reading the blueprint I can firgure the length and
width of one piece. then I need to divided the width and length of the
available sheet sizes to determine which one will give me the least
amount of scrap. For example: the part is 3.5" wide X 5" long. The
width will determine the best sheet width and the length, the best
sheet length. I'll divide 30" by 3.5 = 8.57. I will always round down
as a portion of a part doesn't work. Then I try 36" = 10.29 and
finally 48" = 13.71. Based on the amount to the right of the decimal,
36" is best as the scrap is only .29. Next I do the same thing with
the length: 96 / 5 = 19.2 and 120/5 = 24. The most optimum sheet for
me to use for this part is 36" X 120". I want to be able to list the
sheet sizes available to purchase as not all thicknesses of metal come
in the various widths and lengths. Once I determine the best sheet
size, I want to take the two results and multiply them together to
learn how many pieces one sheet can make. In this example it would be
10 X 24 or 240 pieces.

Is someone able to help me with this?

Thank you.