Pallet calulator

[jasonmck1987]

Hi all,

Is it possible to create something on excel that calculates how many of 1 sized box you can fit on a standard pallet 100cm x 120cm? See below as i am unsure if this uploaded as an attachment:


Stock Bin Product Code Pick height (cm) Pallet size Box height (cm) Box width (cm) Box length (cm) Items per box Boxes per row Rows Boxes per pallet Pallet total
04C01AA01 AAA 97 Standard 30 20 50 10

Thank you for your help.

[AliGW]

Welcome to the forum.

There is no Office version 10 - is that your Windows version? We need Office - so Office 2016 or Office 365, or whatever you have - please update your profile.

[AliGW]

Pleae manually mock up the results you'd expect on row one:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L
1	Stock Bin	Product Code	Pick height (cm)	Pallet size	Box height (cm)	Box width (cm)	Box length (cm)	Items per box	Boxes per row	Rows	Boxes per pallet	Pallet total
2	04C01AA01	AAA	97	Standard	30	20	50	10

Sheet: Sheet1

[jasonmck1987]

Hello,

See attached.

Thank you

[JeteMc]

Please explain how the values in the file attached to post #4 are derived.
Given the pallet dimensions in post #1 (100x120), I do not understand how 10 boxes could fit in a row. It would seem that either there would be 6 boxes (120/20) or 2 boxes (100/50) in a row and either 2 or 6 rows. If the boxes are stacked then it seems that there should be 36 boxes, i.e. =12*rounddown(97/30,0) on the pallet and 360 total items.
Understanding all of the rational behind how the numbers are derived may help in writing formulas/code to automate the process.
Let us know if you have any questions.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new, I will provide the link for you this time.)

https://www.mrexcel.com/board/thread...lator.1119307/

[MrShorty]

This would seem to me to be another variation of a bin packing (which is just a subset of the well known knapsack problem) problem. As an NP-hard problem, there are not "simple" algorithms for finding the solution -- except for a brute force try every possible combination and choose the best. (Wikipedia discussion of bin packing problems: https://en.wikipedia.org/wiki/Bin_packing_problem ).

Excel based solutions for these often involve Solver. Solver's programmers have provided a simple knapsack problem example here: https://www.solver.com/optimization-...stics-examples You should be able to find other examples by putting "knapsack problem Excel" or similar into your favorite internet search engine.

Short but useless answer to your question -- yes it is possible to program a spreadsheet to solve packing problems. A robust algorithm is more complex than it seems on the surface so that I think the "ask the guys on the dock how they have already solved the problem" (or some other variation of find someone else who has solved the problem) suggestion given by mikerickson over at mrexcel is often the easiest solution.

If you are determined to program your own algorithm into a spreadsheet, I would probably suggest you start with a simpler problem (like the very similar 1D cutting stock problem), understand how that goes into the spreadsheet, then work on expanding/adapting from there into your larger pallet stacking problem.

[Alf]

create something on excel that calculates how many of 1 sized box you can fit on a standard pallet 100cm x 120cm?

Can the boxes can be rotated in three dimensions when filling the pallet?

Alf

[Joselo9686]

Is there a way to consider different size / weight boxes into one single pallet, let's say for example large pallet?

In my case I need to place different size boxes into one pallet, it is not standardized it vary all time.

Thanks in advance for your comments

[AliGW]

Administrative Note:

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 see Forum Rule #4 about hijacking and start a new thread for your query.

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