Excel for counting, sum, sort by models and maxim limit

[Gabrielle_erre]

Hi, I'm facing a challenge and I am sure that there is a way to deal with some help because there are smart people around...

I have different products per day, and I must organise to store them like that: each box can store max 60 products, every model in one box (if there is a different model with <60 I must use the quantity in one box, I can not mix products in boxes). I can load maximum 24 boxes on one pallet and after that using another pallet in between, and loading maximum 20 boxes. The ´final pallet´ will have 44 boxes high, and 2 pallets. If you can help me, I would like to find the formula to calculate the number of`final pallets` daily. I can try with single models per pallet (not only in single box), but more I would like to do it combined boxes per final pallet.

Model	Products per day 1	Product per day 2
Audi	3000	850
BMW	1100	200
Ford	7020	3500
Land Rover	5203	362
Toyota	630	7200
Zetta	875	9300

<tbody>
</tbody>

Thanks! Gabrielle

 

[rmb4466]

Gabrielle,

Does this provide the data you're looking for?

Models per box:
60
Boxes per final pallet:
44
	Day 1	Day 2
Model	Products	Full boxes	Models in short-fill box	Products	Full boxes	Models in short-fill box
Audi	3000	50	0	850	14	10
BMW	1100	18	20	200	3	20
Ford	7020	117	0	3500	58	20
Land Rover	5203	86	43	362	6	2
Toyota	630	10	30	7200	120	0
Zetta	875	14	35	9300	155	0
Total boxes:		299			360
Final pallets:		6			8
Remaining boxes:		35			0

<tbody>
</tbody>

For the "Full boxes" column: =TRUNC(B8/$A$2)
For the "Models in short-fill box" column: =((B8/$A$2)-TRUNC(B8/$A$2))*$A$2
For "Total boxes": =SUM(C8:C13)+COUNTIF(D8:D13,">"&0)
For "Final pallets": =TRUNC(C15/$A$4)
For "Remaining boxes": =((C15/$A$4)-TRUNC(C15/$A$4))*A4

--Ryan

 

[Eric W]

Another take on it:

	A	B	C	D	E	F	G
1	Model	Products per day 1	#boxes needed		Product per day 2
2	Audi	3000	50		850	15
3	BMW	1100	19		200	4
4	Ford	7020	117		3500	59
5	Land Rover	5203	87		362	7
6	Toyota	630	11		7200	120
7	Zetta	875	15		9300	155
8
9	Total Boxes		299			360
10	"Final" Pallets		7			9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulase> Cell Formula C2 =ROUNDUP(B2/60,0) C9 =SUM(C2:C7) C10 =ROUNDUP(C9/44,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

These numbers vary from rmb4466 because these include a partially filled pallet on each day.

Incidentally, if you don't need the intermediate steps, you can do the whole thing in one formula:

=ROUNDUP(SUMPRODUCT(ROUNDUP(B2:B7/60,0))/44,0)