Optimal pallet formula

[royrae]

Hello everyone,

I have an difficult problem and i do not even know if this is possible in MS Excel.

The question is as follows:
The Excel file should automatically calculate the amount of palletlocations needed for storage.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Boxes[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]Variation 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Variation 2[/TD]
[TD]140[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]Variation 3[/TD]
[TD]0[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]Variation 4[/TD]
[TD]140[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Variation 5[/TD]
[TD]280[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]Variation 6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Variation 7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Variation 8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Variation 9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Variation 10[/TD]
[TD]60[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]Variation 11[/TD]
[TD]0[/TD]
[TD]110[/TD]
[/TR]
</tbody>[/TABLE]

In the table above you can see that normally on monday there are 5 pallets of boxes produced, with 4 different varations.
These pallets should be stacked.
- Variation 2 + (20 boxes of) variation 5
- Variation 4 + variation 10

Stacked pallets have the following rules regarding capacity:
1 pallet = 260 boxes maximum
2 pallets = 240 boxes maximum
3 pallets = 220 boxes maximum
4 pallets = 200 boxes maximum

Other rules in this calculation are as follows:
- The Variations (as shown in table) need to be stacked together on one pallet!
example: 280 boxes = 260 on 1 pallet location and 20 remaining on another pallet, but these 20 may ofcourse be stacked.


I thought of multiple options (macro's, formulas) but all did not work out... I hope someone on the forum can help me.



If not completely clear, please react on this thread and i will try to explain further.

 

[CalcSux78]

not sure if I'm on track, but what about this:

Excel 2010
	A	B	C
13	Boxes	Monday	Tuesday
14	Variation 1	0	0
15	Variation 2	140	130
16	Variation 3	0	110
17	Variation 4	140	120
18	Variation 5	280	190
19	Variation 6	0	0
20	Variation 7	0	0
21	Variation 8	0	0
22	Variation 9	0	0
23	Variation 10	60	110
24	Variation 11	0	110
25	Tot Boxes	620	770
26	Pallets	3	4
Sheet1

Cell Formulas
Range	Formula
B25	=SUM(B14:B24)
B26	=IF(B25<=260,1,IF(B25<=480,2,IF(B25<=660,3,IF(B25<=800,4,"-"))))
C25	=SUM(C14:C24)
C26	=IF(C25<=260,1,IF(C25<=480,2,IF(C25<=660,3,IF(C25<=800,4,"-"))))