Can anyone help Thanks

[chris28uk]

Hi can anyone help me im a bit of an amateur when it comes to excel but basically ive been designing a quoting system for work and I need to know how many boxes i can get on a pallet to calculate the vehicle required. The height is irrelevant. I get the size from previous worksheets. I then need to find out how many times it will fit on a euro pallet 800 X 1200 and 1000 X 1200MM. I can have a little overhang.

Without complicating things I need to
1) divide the box size by the maximum size which is 1350 X 1350mm & 1000 X 1350mm to find the maximum boxes I can get per layer (Which I have Done)
2) Then if they are both say 6, then find the smallest which is what im having trouble with.

I need to find the largest BPP(D) so 6 on the first and 4 on the second then if they are equal like the first one then I need to find the lowest area. Which is in the far right column (E). So in B column I have =IF(P8=P10,MIN(Q8,Q10),MAX(P8,P10)) to find this but i need the the size not the area of the best layout. Additionally on the second it returned the BPP.


Any help would be extremely appreciated

A B C D E

[TABLE="width: 455"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]W[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Folded Box Size:[/TD]
[TD]515[/TD]
[TD]408[/TD]
[TD]BPP[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Pallet W[/TD]
[TD]Pallet Length[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000 X 1200[/TD]
[TD]Combinations[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]1260.72[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]1030[/TD]
[TD]1224[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]1260.72[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]1224[/TD]
[TD]1030[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Best[/TD]
[TD]1260.72[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Euro[/TD]
[TD]Combinations[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]630.36[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]515[/TD]
[TD]1224[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]840.48[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]816[/TD]
[TD]1030[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Best[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[chris28uk]

Vehical Size: W L
Folded Box Size: 515 408 BPP
W L H
1300 4000 1750
2400 4000 2700 Pallet W Pallet Lengh

1000 X 1200 Combinations 2 3 6 1260.72
1030 1224
3 2 6 1260.72
1224 1030
6
Best 1260.72


Euro Combinations 1 3 3 630.36
515 1224
2 2 4 840.48
816 1030
4
Best 4