Trying to figure out the best way to utilize two packages

[HZRMike]

My business transfers donuts to different store and has to use two different sized packages.
Large Package fits 60 donuts and the small packages fits 36 donuts.
How do I write a formula to maximize the efficiency for packing donuts in the proper packaging.
For example, if I need to package 36 donuts, I'd use small package, if I have to send out 60, I'd use one small package, if i needed 68, I'd use two small packages, and if I needed 92 i'd use a small and a large.

In a perfect world any formula I create would be flexible so I can change the number of donuts that fit in each package so the spreadsheet doesn't become void, if anything changes.

Thanks for any help....

 

[JamesCanale]

Slightly improved formula:

=LET(check,A4,smallbox,$A$1,bigbox,$A$2,maxboxes,INT((check-1)/smallbox)+1,mat,SEQUENCE(maxboxes+1,maxboxes)-1,mata,MOD(mat,maxboxes)+1,matb,INT(mat/maxboxes),matc,mata>matb,matd,IF(matc,smallbox,bigbox),diag,N(INT((SEQUENCE(maxboxes,maxboxes)-1)/maxboxes)+1<=MOD(SEQUENCE(maxboxes,maxboxes)-1,maxboxes)+1),matf,MMULT(matd,diag),matg,matf-check,mati,IF(matg<0,bigbox+1,matg),matj,mati=MIN(mati),x,matj*mata,y,matj*(matb+1),xx,MIN(IF(x=0,bigbox+1,x)),yy,MIN(IF(y=0,bigbox+1,y)),out,TEXTJOIN(", ",TRUE,INDEX(matd,yy,SEQUENCE(xx))),out)

 

[HZRMike]

Maybe this helps:

MrExcel posts20.xlsx
	C	D	E	F	G	H
3	Capacity	Package			Doughnuts	Packages
4	0	1S			0	0
5	36	1L			1	1S
6	60	2S			35	1S
7	72	1S,1L			36	1S
8	96	2L			37	1L
9					59	1L
10					60	1L
11					61	2S
12					92	1S,1L
13					120	2L
14					121	2L
15					122	2L
Sheet16

Cell Formulas
Range	Formula
H4:H15	H4	=XLOOKUP(G4,$C$4:$C$8+1,$D$4:$D$8,0,-1)

Thank you for your help. I made a few changes that make this more flexible so that if the number of donuts/package change and/or the maximum number of donuts being shipped changes, the whole thing won't have to be scrapped. I also made the match the next number larger rather than next number lower so that we'd never be over max capacity.

=XLOOKUP(B8,Table1[Capacity],Table1[Config],0,1)
Capacity Formula - =(E4*$C$3)+(F4*$C$4)
Configuration Formula - =IF([@Small]=0,[@Large]&"L",[@Small]&"S"&IF([@Large]=0,"",", "&[@Large]&"L"))

Anyways, thanks to all for your help!

Mike

 

[DRSteele]

Thank you for your help. I made a few changes that make this more flexible so that if the number of donuts/package change and/or the maximum number of donuts being shipped changes, the whole thing won't have to be scrapped. I also made the match the next number larger rather than next number lower so that we'd never be over max capacity.

=XLOOKUP(B8,Table1[Capacity],Table1[Config],0,1)
Capacity Formula - =(E4*$C$3)+(F4*$C$4)
Configuration Formula - =IF([@Small]=0,[@Large]&"L",[@Small]&"S"&IF([@Large]=0,"",", "&[@Large]&"L"))

View attachment 89939

Anyways, thanks to all for your help!

Mike

You're welcome.

 

[OaklandJim]

Fluff. I apologize I forgot to post code. It is a klunker but it works. The formula approach offered above is pretty good.

By the way, in the formula there is a +1 that I do not understand. I'm guessing that it means add one to whatever value is found in $C$4:$C$8?

=XLOOKUP(G4,$C$4:$C$8+1,$D$4:$D$8,0,-1)

'Note that this function uses worksheet "code names" rather than rely on the sheet/tab name.
'That way if the sheet/tab name changes the code will still work.
Function LookupPackaging(piQty As Long) As String

'   If quantity is zero then do nothing.
    If piQty <= 0 Then Exit Function

'   Header cell for Capacity data.
    Dim rAnchorCellCapacity As Range

'   Range of cells containing Capcity data.
    Dim rLookUpRange As Range

'   Count of rows in the Capacity DATA cells range.
    Dim iDataRows As Long

'   For a given span of Capacities this is the lowest value.
    Dim iFloorQty As Long

'   For a given span of Capacities this is the highest value.
    Dim iCeilingQty As Long

'   For the number of box(es) this is the count of small boxes.
    Dim iSmallBoxCount As Long

'   For the number of box(es) this is the count of large boxes.
    Dim iLargeBoxCount As Long

'   Maximum quantity for which values are provided.
    Dim iMaxQty As Long

'   Used to loop through
    Dim rCell As Range

'   Point the rAnchorCellCapacity range variable to the "header cell"
'   for the Capacity values in the data table.
    Set rAnchorCellCapacity = [Data].Range("Header_Capacity")

    iDataRows = rAnchorCellCapacity.Offset(10000).End(xlUp).Row - rAnchorCellCapacity.Row

    Set rLookUpRange = rAnchorCellCapacity.Offset(1).Resize(iDataRows)

    iMaxQty = rAnchorCellCapacity.Offset(iDataRows)

    For Each rCell In rLookUpRange

'       For a given span of quantity values in the data table this is
'       the lowest quantity that can be accommodated.
        iFloorQty = rCell.Value

'       For a given span of quantity values in the data table this is
'       the highest quantity that can be accommodated.
        iCeilingQty = rCell.Offset(1).Value

'       Handle quantity specified is less than the iFloorQty
        If piQty < iFloorQty _
         Then
            LookupPackaging = "1 Small"
            Exit For

'       Handle quantity specified is between floor and ceiling.
        ElseIf piQty >= iFloorQty And piQty <= iCeilingQty _
         Then

'           Small quantity is two cells to the left of the cell containing Capacity value.
            iSmallBoxCount = rCell.Offset(1, -2).Value

'           Large quantity is one cell to the left of the cell containing Capacity value.
            iLargeBoxCount = rCell.Offset(1, -1).Value

'           Handle small box quantity > zero and large box quantity = zero.
            If iSmallBoxCount > 0 And iLargeBoxCount = 0 _
             Then
                LookupPackaging = iSmallBoxCount & " Small"
                Exit For

'           Handle small box quantity = zero and large box quantity > zero.
            ElseIf iSmallBoxCount = 0 And iLargeBoxCount > 0 _
             Then
                LookupPackaging = iLargeBoxCount & " Large"
                Exit For

'           Handle small box quantity and large box quantity <> zero.
            Else
                LookupPackaging = iSmallBoxCount & " Small, " & iLargeBoxCount & " Large"
                Exit For

            End If

'       Handle quantity specified exceeds maximum quantity.
        ElseIf piQty > iMaxQty _
         Then
            MsgBox "Quantity " & piQty & " exceeds the maximum."

            LookupPackaging = "Exceeds maximum quantity."
            Exit For
'
        End If

    Next rCell

End Function