Using MOD, SUMIF, and VLOOKUP for a table.

Hello,

I am trying to calculate aluminum sections required and feet wastage for making window frames.

Sections are available in only 2 sizes. 12 Feet and 15 feet.

Eg: If i want 1 window of size 5 x 6, i will need 2 sections of 12 feet and wastage would be 2. ( 6+6 for height, 5+5 for width )

Eg BUT 30 windows of 5x6 ( wont have wastage 2 x 30 = 60 ), i would need 30 sections of 12 feet ( 60 pieces of 6 feet ) and 20 sections of 15 feet. Wastage zero. ( I need 60 pieces of 5 feet, and from one section of 15 feet i can make 3 of them. So 20 sections of 15 feet. )

Eg: Window of size 8 x 4, 30 windows. I would need 60 sections of 12 feet. ( 2 section per window as 8+4 is twelve ) zero wastage..

Eg: 11 x 15, 5 windows. 10 sections of 15 feet. 10 sections of 12 feet. Wastage 10 feet. ( 1 feet wastage per section fo 12 feet. )


Cell A1 and A2. where i can enter size A x B and a third cell B1 where i can enter number of windows.

Wastage should be minimum and i would prefer lesser number of sections whenever possible. ( For size 12 x 3, 2 windows -- i would require 4 sections of 15 feet and not 5 sections of 12 feet. wastage would be zero in both cases. But its ok to have multiple options displayed if possible as in this case. )

Detailed explanation again.

Lets first do it for only 1 type of section to avoid confusion. 12 Feet sections only. ( I will add 15 later )

Now for 5 x 6 window, i need something like this.
A B C D
Width 5 No of 12 feet sections 2
Length 6 Wastage 2

Quantity 1

D1 should show 2 as answer.

Since window is 5 x 6, we need 2 pieces of 5 feet for width and 2 pieces of 6 feet for length. ( Rectangle window with 4 sides, 2H + 2B )

12 feet section can be cut into 2 equal 6 feet sections for length. ( No wastage )

12 feet section can be cut into 2 pieces of 5 feet each for width. ( 2 feet will be wastage, as only 10 feet of the 12 will be utilized.

For second window, same would happen. ( The wastage cannot be used because sections cannot be joined ).

So if quantity is 10, we should have 20 in D1 and 20 feet waste in D2.

Suppose Width and length is 6.5 and 5.5

In this case, we will again need 2 sections of 12 feet per window ( 6.5 + 5.5 = 12 ) but wastage would be zero.

Another Eg: Width 12, Length 6. And quantity 10

We will need 3 sections per window, 30 sections total and wastage zero. ( 12 + 12 + "6 + 6" )

Now let me also have sections of 15 feet and take the above first example again.

For 5 x 6 window, i need something like this.
A B C D
Width 5 No of 12 feet sections 30
Length 6 No of 15 feet sections 20
Wastage 0
Quantity 30

I have changed the quantity of windows to 30 and also added 15 feet sections..

for 30 windows i will need 60 pieces of 6 feet and 60 pieces of 5 feet.

So 30 sections of 12 feet. ( Can make 2 - " 6 feet " from 1 "12 feet" )
20 sections of 15 feet. ( Can make 3 - "5 feet " from 1 - " 15 feet" )
Wastage 0.

If quantity would have been 31, 2 more sections of 12 feet, and wastage would be 2. Not 1 section of 12 and 1 of 15. Wastage should be minimum.
I can use any number of helper Sheets / columns / functions / macros. I am not looking for one single magical function.

Check Attached file.

TIA

Reset. Subject changed.