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....

 

[OaklandJim]

Are you willing to post your workbook using a link on Box, Dropbox, 1Drive? Use the link icon at the top of the message box to do that.

Providing your workbook might help.

At least post the parts of your workbook that are relevant. Use Mr. Excel's excellent addin that enables you to post portions (ranges) of your workbook. See HERE.

Show where the formula is used and where the donuts per box data is stored now.

 

[HZRMike]

Are you willing to post your workbook using a link on Box, Dropbox, 1Drive? Use the link icon at the top of the message box to do that.

Providing your workbook might help.

At least post the parts of your workbook that are relevant. Use Mr. Excel's excellent addin that enables you to post portions (ranges) of your workbook. See HERE.

Show where the formula is used and where the donuts per box data is stored now.

Sorry, I had trouble getting that Addin to load.
I do have a table that calculates the maximum capacity of various configurations of packages.

Small Package	36​
Large Package	60​
Small	Large	Capacity	Config
1​	0​	36​	1 Small
0​	1​	60​	1 Large
2​	0​	72​	2 Small
1​	1​	96​	1 Small, 1 Large
0​	2​	120​	2 Large

What I need is a formula for y(below) that takes the minimum number from this list that would still be larger than x, then I can use an index formula to pick the matching configuration.

# of Donuts	Pkg to Use
x	y

Hope that makes sense thanks for your help.

 

[OaklandJim]

What is the largest quantity that you need to accommodate?

 

[HZRMike]

What is the largest quantity that you need to accommodate?

Good question, I'm not sure. That's why I want to keep the table with the various configurations flexible so I can add additional amounts if necessary.
Right now I'll just assume it's 120.

 

[OaklandJim]

Might you be willing to provide your workbook? You can post a link to it using Box, Dropbox, 1Drive. Use the link icon at the top of the message box. I'd like to set this up so it is consistent with your workbook.

 

[OaklandJim]

THIS WORKBOOK does what you asked for. It uses the data table that you provided to look up the right packaging. To change the packaging data update that table.

Also, I use worksheet "code names" instead of sheet/tab names so the function still works if someone changes the sheet/tab name.

See HERE for an explanation of code names.

 

[DRSteele]

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)

 

[Fluff]

@OaklandJim Please post your solution to the board as per rule#4 Thanks

 

[JamesCanale]

I had a go at a flexible one.

MrExcelPlayground16.xlsx
	A	B
1	36
2	60
3
4	1	36
5	2	36
6	3	36
7	35	36
8	36	36
9	37	60
10	38	60
11	59	60
12	60	60
13	61	36, 36
14	62	36, 36
15	71	36, 36
16	72	36, 36
17	73	60, 36
18	96	60, 36
19	97	36, 36, 36
20	98	36, 36, 36
21	119	60, 60
22	120	60, 60
23	121	60, 36, 36
Sheet19

Cell Formulas
Range	Formula
B4:B23	B4	=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),mate,TRANSPOSE(matd),diag,N(INT((SEQUENCE(maxboxes,maxboxes)-1)/maxboxes)+1>=MOD(SEQUENCE(maxboxes,maxboxes)-1,maxboxes)+1),matf,TRANSPOSE(MMULT(diag,mate)),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)