Excel Formula problem

[Madz75]

Hi Guys,

I need some help with this formula if anyone can help!

I use a basic formula to work out how many 3600 blanks I need to make an amount of finished Tops. My formula works well provided the finished top sizes are the same. The one i use for that looks like this:

=ROUNDUP(Qty/(ROUNDDOWN(3600/(Width+10),0)),0)

Qty = quantity of finished tops required
Width = the finished size of the tops
I add 10mm to that for saw cuts
the blanks are always 3600

When i have multiple finished sizes i could use the below formula which just sums the array of widths and quantities - it looks a little like this:

=SUMPRODUCT(ROUNDUP($A$2:$A$5/(ROUNDDOWN((3600/((B2:B5)+10)),0)),0))

The problem with this is it won't use the remainders of each calculation
If i wanted (Qty)12 x(Width)900, it would return an answer of 4
And if i want 4 x800, it would return an answer of 1
The total would then be 5 Blanks
Realistically I can get the 4 x800 out of the remainders of the first 4 blanks - SO i need a formula that will return the best yield for multiple sizes and quanties!

Thanks in advance!

 

[Madz75]

Thanks Denis, for replying with a link to the same question I also posted on the excel forum

 

[Deleted member 4993]

Thanks Denis, for replying with a link to the same question I also posted on the excel forum

Denis did a favor for us - by warning that you are trolling for answers in other web-sites.

That information will save the "volunteer's" time - by "not answering questions" that may have been answered at other web sites.