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.