Filtering by number to the right size?

Sorry, the title is a little vague, not sure how to easily summarize. There is a file attached for reference.

What I am trying to do is probably very simple. I am selling things. Lets say they are all one item. I have 4 box sizes. I can use any combination of those box sizes to ship the item but want to do it efficiently and know the weight. I made a quick little spreadsheet that shows the 4 box sizes and an empty weight. I have a cell that is a variable. It is the number of items I am shipping. I generally only want to be in the fewest boxes possible. Basically I want to be able to put in that number that is the variable. On the attached sheet A4. And have it auto fill G8:11 even if that is a 0.

Lets say in my example I put the quantity as 55. That would mean 0 of the mini box, 0 of the small box, 1 of the Medium Box, 0 of the large box. But I am hoping to get a formula that would determine what those values would be for me and update when I change the quantity. Hopefully that makes sense.

Part 2 is I want to determine the weight. This is a bit of a more complicated part, but can sort of be a guess. I guess an example would be if I had 250 not shown I would have (2) large boxes, that it would tell me the weight of them, and the overall weight but I guess it would really want to know the (1) full box. Box of 150. Then the weight of the second box, box of 100 and show that. That may be for a later date.

Either way any help on this would be greatly appreciated. I know it can be done, I just fear I don't have the advanced knowledge for it.

Thanks!

Matt

Well, if you truly want the "fewest boxes possible", then the formulas below should give you that. However, it's not the most EFFICIENT way to pack. In other words, if you are shipping 110 items, you could ship 1 Medium box and one Small Box so there would be no room left over. But that's 2 boxes. So the fewest boxes possible would be to ship the 110 in a Large Box and you only need 1 box. So in this case, in G8 you could enter this formula:
=IFS(AND(MOD($A$4,$F$11)<=F8,MOD($A$4,$F$11)>0),1,TRUE,0)
in G9
=IFS(AND(MOD($A$4,$F$11)<=F9,MOD($A$4,$F$11)>F8),1,TRUE,0)
G10
=IFS(AND(MOD($A$4,$F$11)<=F10,MOD($A$4,$F$11)>F9),1,TRUE,0)
G11
=ROUNDDOWN(A4/F11,0)+IF(AND(MOD($A$4,F11)>F10),1,0)

Attached is this updated file.

Sorry but I was doing some different things with the formulas and forgot to change them so you just need IF statements, not IFS statements. Although the above should work, the formulas could also be this:
G8:
=IF(AND(MOD($A$4,$F$11)<=F8,MOD($A$4,$F$11)>0),1,0)
G9:
=IF(AND(MOD($A$4,$F$11)<=F9,MOD($A$4,$F$11)>F8),1,0)
G10:
=IF(AND(MOD($A$4,$F$11)<=F10,MOD($A$4,$F$11)>F9),1,0)
G11:
=ROUNDDOWN(A4/F11,0)+IF(AND(MOD($A$4,F11)>F10),1,0)

Thank you for the help. I was playing around with that and I think it is just about right. Two follow up questions/statements.

1) I think that this area actually wants to be like this correct?

G8:
=IF(AND(MOD($A$4,$F$11)<=F8,MOD($A$4,$F$11)>0),1,0)
G9:
=IF(AND(MOD($A$4,$F$11)<=F9,MOD($A$4,$F$11)>F7),1,0)
G10:
=IF(AND(MOD($A$4,$F$11)<=F10,MOD($A$4,$F$11)>F8),1,0)
G11:
=ROUNDDOWN(A4/F11,0)+IF(AND(MOD($A$4,F11)>F9),1,0)

2) I took that and edited/formatted it a little to help make it simple for someone who is using it. It is reattached. That brought up the question of what happens with 200 items? 300 items etc? Is there some way to make the formula work to say 200 go in 1 large box and one Medium box?

The goal is as few boxes as possible in reference to your previous comment. I am using this for a home small business thing and have someone helping me ship. We looked at the shipping and determined that fewest boxes they take is best.

This already is tremendously helpful, it is just now I have the curiosity to see if it can be even better.

Thank you for the help!

Matt

I'll be able to respond later tonight if no one else does.

I think that this area actually wants to be like this correct?

Well, only because you removed a row in the spreadsheet. Your formulas NOW start in Row 7 where in your original file they started in Row 8. The formulas I gave were correct given where the rows were.

That brought up the question of what happens with 200 items? 300 items etc? Is there some way to make the formula work to say 200 go in 1 large box and one Medium box?

Yes, it already worked with the file I attached. You could enter any number you want and it comes up with the right answer. The issue is you changed the formula in the one you attached for the large box. If you replace it with the one I had given initially (but changing the rows now), it works (putting in 200 in my file shows 1 large and 1 medium box.

Let me know if you have any questions or see any issues.

Yes you are correct. My error. Thank you!

A follow up one maybe @gregb11 would know.

I'm reattaching that sheet showing what it has adjusted too.

Now my question is how can I list how many items are in a box.

What I mean is looking at this sheet. Lets say I have (500) qty of FS03. I would fill (3) large boxes of 160 to equal 480. So I want the Large Box to show the H column with how many units are in a box essentially maxing out at 160 for that large box. I then want the remaining 20 to autopopulate into a "mini" Box to show 20 in H32.

Not sure that totally makes sense. I'm also going to repost as a new thread.

I'm sure the formula could be written better, or more flexible, but for now, you could enter this formula in cell H9:
=IF(G9>0,$A$6-$H$12*$G$12,0)
and copy it down to H11, then in H12 you could enter:
=IF(G12>0,IF(A6>F12,F12,A6),0)
The formula in H12 you can copy/paste straight into the other "Large Box" Cells in column H. The formula in cell H9 you'll have to adjust for cell H21, and then copy it down, etc.

I tired that and it seemed to work. I also tried the response here

https://www.excelforum.com/excel-gen...ml#post5347207

I think this option produces a cleaner result.

Thank you for the quick and helpful response.