Max Yield Formula

[razman10]

I have looked for a solution, but I cannot seem to find it; I might not be searching the correct terms so I apologize if this has been answered before.

I need to figure out how many units across I can print at one time on a standard size roll. The size of the units are variable so there is a field (cell) that can be changed manually. I have an array with the standard roll sizes (6", 8", 10"). I am trying to figure out the max yield with least amount of waste.

For example, if I need to print 3.5" units, a 6" roll will yield only 1 at a time, an 8" roll will yield 2 at a time, and a 10" roll will also yield 2 at a time (with more wast than an 8" roll). Based on the unit size and the given roll sizes, I'd like the formula to tell me I should use an 8" roll.

I definitely consider myself an advanced Excel user, but cannot seem to figure this one out! Should I be using VBA?

[FDibbins]

Hi, welcome to the forum

This got a bit messy, probably someone can think up an array formula to do this, but this seems to work...
=IF(MIN(IF(A2>6,99,INT(6/A2))*MOD(6,A2),IF(A2>8,99,INT(8/A2))*MOD(8,A2),IF(A2>10,99,INT(10/A2))*MOD(10,A2))=IF(A2>6,99,INT(6/A2))*MOD(6,A2),6,
IF(MIN(IF(A2>6,99,INT(6/A2))*MOD(6,A2),IF(A2>8,99,INT(8/A2))*MOD(8,A2),IF(A2>10,99,INT(10/A2))*MOD(10,A2))=IF(A2>8,99,INT(8/A2))*MOD(8,A2),8,10))

[José Augusto]

Hi
Try this
C3:C5={6,8,10}
E3=3.5
Use the following array formula =INDEX(C3:C5,MATCH(MAX(INT(C3:C5/$E$3)*$E$3/C3:C5),INT(C3:C5/$E$3)*$E$3/C3:C5,0))
I hope it helps
Regards

[razman10]

Thank you Ford / Obrigado José!

I used José's array formula as it was a lot cleaner and it works well with the roll widths I mentioned on my original post. For some reason, however, it does not calculate well when I use different roll sizes (different material).

My example:
Unit size: 2.5" Roll widths 3.375", 5", and 6". This should calculate to 3.375" roll with a yield of 1, however it is coming up with a roll width of 5" with a yield of 1. Any ideas?

Also, José, would you please explain how that formula is working? If I understand exactly what is going on, I may be able to tweak it myself to account for waste between the units. Obrigado!

Regards,
Rui

[José Augusto]

Hi Rui

To explain further as I thought, I decided to expose the formula calculating the percentage of waste.

See explanation in the file MaxYieldFormula.xlsx

Regards

[razman10]

Thank you again José!

This worked perfectly (in fact the first formula you provided worked well; I was confused as I had made a modification to it and THOUGHT I removed it but had not; I apologize for my confusion!)

Ès o maior!

Regards,
Rui