Pallet Calculator.

Hello all,

I was wondering if someone can help me.

I run a warehouse that stores various boxes of products that come in all shapes and sizes. Quite often I have the dimensions of boxes BEFORE they arrive at my warehouse.

I have 3 different pallet sizes that I have to order prior to stock arriving. A standard size pallet, a medium sizes pallet and a large size pallet.

To help me plan ahead and place my pallet orders before the items arrive I am trying to put together something that will help me calculate what products fit onto what pallet and how many.

The spreadsheet has my 3 pallet sizes and the max cubic at the top in meters and below that is an example SKU where I've typed in the height, length and depth. In the "suggested pallet" column I want this to look at my measurements and suggest a pallet and qty that will fit so I can place my orders for pallets in advance.

Hope this makes sense.

I'd be very thankful for any help on this, see attached.

If you include in your sample, a couple more rows of data along with the desired results (manually entered), we should be able to get you to a solution.
I would also recommend rearranging the table in B3:D6 to match the order of the headers in C9:E9 to avoid any possible confusion.

One question that I have, is if each of the values in columns C, D, and E (starting in row 10), have to be ≤ the corresponding values in B4:D6.

For example, should row 10 be "Large Pallet" since 0.14 ≤ 1.60 (Standard), 1.56 ≤ 1.60 (Medium), 1.29 ≤ 1.30 (Large) thus taking the smallest pallet size that fits all criteria?

Or, are you looking only at the cubic value?

I'm totally confused what you mean with the below. I only know very basic Excel.

Would you be so kind to put what you mean into my spread sheet and upload . . . . . its easier for me understand once I see it.

Which part of post #2 confused you? If it was the second sentence, look at the workbook that you shared in post #1.

Notice that the headers in row 3 show {Length, Width, Height} whereas the headers in row 9 show {Height, Length, Depth}. I was simply suggesting that you make the order (and wording) of these the same.

We (the contributors to this forum) need to understand the logic behind how you determine which pallet to use.
This is why I am asking for a few more rows of data (like row 10) along with the desired pallet size for each row which you can manually type into columns G and M.

Once we understand your logic by looking at a few examples and their corresponding results, we can figure out how to get this working automatically in Excel.

Hello,

See attached.

In row 10 I have inputted manually my outcome.

The height of the box (0.14) can fit on any size pallet.
The length of the box (1.56) can fit on a medium pallet and large pallet.
The width of the box (1.29) can only fit on a large pallet.

Therefore I will have to use a large pallet for this box / product.

To calculate how many I can load on this pallet I divide 5.59 (cubic of the pallet) into 0.28 (cubic of the box) and this tells me I can fit 19.96 boxes onto it.

Hope this helps. see re-attached.

Try these:

G10 =INDEX($A$4:$A$6,INDEX(MATCH(1,($B$4:$B$6>=$D10)*($C$4:$C$6>=$E10)*($D$4:$D$6>=$C10),0),0))
H10 =VLOOKUP(G10,$A$4:$E$6,5,0)/F10

Since you technically can't load partial boxes, you can edit the formula in column H if you want to return just the integer (whole-number) of the result by using the INT function.

That would be this:

H10 =INT(VLOOKUP(G10,$A$4:$E$6,5,0)/F10)

This will give you 19 instead of 19.84127...

Hello,

This is fantastic.

however I have just thought of something that I didn't notice.

I have a max weight on each pallet of 1000KG's how would I take this into consideration?

I have included box weights see attached.

Looking at the workbook shared in post #8:

=INT(VLOOKUP(H10,$A$4:$E$6,5,0)/F10) shows the maximum # of boxes that you can load due to size. (You already have this formula)
=INT(VLOOKUP(H10,$A$4:$F$6,6,0)/G10) shows the maximum # of boxes that you can load due to weight.

You want the formula in column I to show the maximum # of boxes that you can load due to weight and size. This would be the minimum value of the above two formulas.

Putting it all together, we have this:

I10 =MIN(INT(VLOOKUP(H10,$A$4:$E$6,5,0)/F10),INT(VLOOKUP(H10,$A$4:$F$6,6,0)/G10))

PERFECT!!!!!

I have over 10,000 boxes arriving in the coming months this will be vital to help me plan.

I can't thank you enough!

If you want to drag all of the formulas down the columns, so that they are automatically calculated once the data is entered into columns A,B,C,D,E, and G, you can use these:

F10 =IF(COUNT(C10:E10),C10*D10*E10,"")
H10 =IF(COUNT(C10:E10),INDEX($A$4:$A$6,INDEX(MATCH(1,($B$4:$B$6>=$D10)*($C$4:$C$6>=$E10)*($D$4:$D$6>=$C10),0),0)),"")
I10 =IFERROR(INT(MIN(VLOOKUP(H10,$A$4:$E$6,5,0)/F10,VLOOKUP(H10,$A$4:$F$6,6,0)/G10)),"")

Drag all of these formulas down through row 32 (or however far you'd like).

These formulas will ensure that the formula columns (F, H, and I) only show a result once a number is inputted into the corresponding row of columns C, D, or E.

Note that I shortened the formula in I10 slightly by only using INT once.

You're welcome. I'm glad to have been able to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks Again for your help.

I will mark as solved.

Hello All,

is it possible to have the formula amended to consider two box sizes, or even more if possible,?

kind regards
Agata

Hello alabecka. Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

You can put formula according to pallet size measurement. Perhaps problem will be solved.