Not sure what formula I need to use

[chris28uk]

[h=2]

Can anyone help Thanks[/h]

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.

How can I find the highest BPP? And if they're the same the lowest area (E) then get it to return the size of the best (B) (C)
it's a little difficult to explain but I can try and explain anything you don't understand

Any help would be extremely appreciated I've actually been trying to find an answer for days on google now?? thanks again

................A .......B ......C .........D .....E

[TABLE="class: cms_table, width: 455"]
<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]

​

 

[Logit]

.
I haven't the foggish idea how to create the formula you are seeking but here are some resources to review:


https://www.mrexcel.com/forum/excel-questions/837038-container-volume-calculation.html

https://www.mrexcel.com/forum/excel...ate-number-pallets-based-item-dimensions.html

https://www.mrexcel.com/forum/excel-questions/175101-aircraft-weight-distributions-2.html