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]

What do you mean by optimal combination of two sizes?
Do you mean the least common multiple?

 

[Jasonx]

A combination of two sized packages of a product to come closest to a target number for an order.

Package(a) = 630 units
Package(b) = 135 units

!7,000 total units needed for an order. What combination of "a" and "b" is needed to come closest to this 17,000 unit order?

 

[kpark91]

Oh. Ok. I understood.
What defines the "best" solution? Less package numbers?

 

[Jasonx]

The best solution would be the count of packages "a" and "b" to come closest to the total units needed. The total number of packages is not really an issue in this situation.

Thanks for your help on this.

 

[kpark91]

Hi, jasonx.

I don't think I will be able to do this with just formulas.
I think I would need VBA to code UDF (User-defined function)..

Is that okay?

Meanwhile any other takers?

 

[Jasonx]

If it needs to be done in VBA, please do.

Thanks

 

[kpark91]

Ok. working on it :P

 

[kpark91]

Put this in your normal module and use it like
=optCombo(630, 135, 17000)

as well as
=optCombo(A1, A2, A3)

If any error, it will show up with "#" in front
If result, it will show up as "27;0" meaning 27 packages of "630s" and 0 packages of "135s"

If you want any other parameters needed as result, you can either edit the code or ask on this thread.

Public Function optCombo(num1 As Variant, num2 As Variant, targetNum As Variant) As String
 
    Dim dblConvLimit As Double
    dblConvLimit = 0.00000001
 
    If num1 < 0 Or num2 < 0 Or targetNum < 0 Then
        optCombo = "#NEG"
        Exit Function
    End If
 
    If VarType(num1) = vbString Or VarType(num2) = vbString Or VarType(targetNum) = vbString Then
        optCombo = "#STR"
        Exit Function
    End If
 
    If num1 - CInt(num1) >= dblConvLimit Or num2 - CInt(num2) >= dblConvLimit Or targetNum - CInt(targetNum) >= dblConvLimit Then
        optCombo = "#DECI"
        Exit Function
    End If
 
    If targetNum < num1 Or targetNum < num2 Then
        optCombo = "#>TRG"
        Exit Function
    End If
 
    Dim loop1#, loop2#, dblMaxNum#, dblDiffLimit#, dblDiv1#, dblDiv2#, i#, j#, dblTempLoop#, dblDiff#
 
    dblMaxNum = WorksheetFunction.Max(num1, num2)
    dblDiffLimit = dblMaxNum
    loop1 = targetNum \ num1 + 1
    loop2 = targetNum \ num2 + 1
 
    If dblMaxNum = num1 Then
        For i = loop1 To 0 Step -1
            dblTempLoop = (targetNum - i * num1) \ num2
            If dblDiffLimit = 0 Then GoTo Ans
            If dblTempLoop >= 0 Then
                For j = dblTempLoop To 0 Step -1
                    dblDiff = Abs(targetNum - i * num1 - j * num2)
                    If dblDiff < dblDiffLimit Then
                        dblDiv1 = i
                        dblDiv2 = j
                        dblDiffLimit = dblDiff
                    End If
                Next j
            End If
        Next i
    Else
        For i = loop2 To 0 Step -1
            dblTempLoop = (targetNum - i * num2) \ num1
            If dblDiffLimit = 0 Then GoTo Ans
            If dblTempLoop >= 0 Then
                For j = dblTempLoop To 0 Step -1
                    dblDiff = Abs(targetNum - i * num2 - j * num1)
                    If dblDiff < dblDiffLimit Then
                        dblDiv1 = j
                        dblDiv2 = i
                        dblDiffLimit = dblDiff
                    End If
                Next j
            End If
        Next i
    End If
 
Ans:
    optCombo = CStr(dblDiv1) & ";" & CStr(dblDiv2)
End Function

If anybody wants to optimize it, you can and post it here as I want to learn as well

Thanks!

 

[Jasonx]

Thank you very much.