How to Determine group of numbers best fit into 1 number

[RickB]

I am trying to determine the following:

I have 3 sizes of product I need to cut from different width sheets of
aluminum. (the lenght is all the same so length is not needed in formula).

Blank size width:

10 inches
15 inches
20 inches

Available aluminum Sheet widths:

36 inches
48 inches
60 inches

What I want to determine is when I put in width sizes the program would
automatically figure the quantity of the optimal size sheets to use in order
to cut down on waste since aluminum is expensive. The width size of the
blanks will vary from job to job. The available aluminum sheet widths are
always the sizes I have above.

 

[John_John]

Hi!

You can do something like the example above:

A1 = "Blank width"

B1 = "Available sheet widths"
B2 = 36
B3 = 48
B4 = 60

C1 = "Waste"
C2 = "=MOD(B2;$A$2)"
C3 = "=MOD(B3;$A$2)"
C4 = "=MOD(B4;$A$2)"

D1 = "Quantity"
D2 = "=INT(B2/$A$2)"
D3 = "=INT(B3/$A$2)"
D4 = "=INT(B4/$A$2)"

Formula for the conditional formatting of "B24".
"=$C2=MIN($C$2:$C$3;$C2)"

Select the pattern you want for the conditional formatting.

Type in cell A2 the width of the blank sheets.

(Substitute the ";" character of formulas if it needs.)

I hope it helps.


Ο χÏήστης "RickB" έγγÏαψε: