Finding optimal combination of two sizes

[Jasonx]

Hello,

I am hoping someone can help me out with this formula. I am looking to find a formula that will calculate the least amount of waste when two sized packages are needed to fill an order. The price per unit is the same for both packages, so that is not an issue.

Two sizes of the same product are used to fill an order. Example: Package (A) "630 units" and Package(B) "135 units". An order of 17,000 units is needed. How would you find the best combinations of A and B to come closest to the 17,000 unit order? Ideally the combination of the two would be within + or - 5% of the target (although this is probably unattainable in every situation).

Thanks

 

[kpark91]

Thanks for the feebackk.
Btw,

    If targetNum < num1 Or targetNum < num2 Then
        optCombo = "#>TRG"
        Exit Function
    End If

should be

    If targetNum < num1 And targetNum < num2 Then
        optCombo = "#>TRG"
        Exit Function
    End If