Pallet load calculator

Jasonmck

New Member
Joined
Nov 10, 2017
Messages
3
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:


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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
How about
Book1
ABCDEFGHIJKL
1Stock BinProduct CodePick height (cm)Pallet sizeBox height (cm)Box width (cm)Box length (cm)Items per boxBoxes per rowRowsBoxes per palletPallet total
204C01AA01AAA97Standard3020501012336360
Sheet1
Cell Formulas
RangeFormula
I2I2=MAX(INT(MAX(100/F2))*INT(MAX(120/G2)),INT(MAX(100/G2))*INT(MAX(120/F2)))
J2J2=INT(C2/E2)
K2K2=I2*J2
L2L2=K2*H2
 
Upvote 0
Cross posted Pallet calulator

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
I work in a warehouse and one of our standard pallet stacking patterns has same sized boxes not in the same direction
Pallet.jpg

The next layer is a reflection of this so that the stacked boxes interlock to strengthen the resulting block. (Otherwise you get 7 stacks of box on box on box which topples easily.)

As a theoretical exercise, an Excel stacking solver is a fun idea.
As a practical matter, you could either go down to the dock and ask them how to palletize a particular sized box.
Or, your box supplier probably has stacking patterns for all of their boxes.
 
Upvote 0
Not only should the boxes be stacked like Mike has shown, you usually have to allow a gap between the edge of the pallet & the edge of the load.
That gap can vary, depending on the warehouse it's going to & the size of the pallet, amongst other things.
 
Upvote 0
I work in a warehouse and one of our standard pallet stacking patterns has same sized boxes not in the same direction
View attachment 3159
The next layer is a reflection of this so that the stacked boxes interlock to strengthen the resulting block. (Otherwise you get 7 stacks of box on box on box which topples easily.)

As a theoretical exercise, an Excel stacking solver is a fun idea.
As a practical matter, you could either go down to the dock and ask them how to palletize a particular sized box.
Or, your box supplier probably has stacking patterns for all of their boxes.
Hi, I know is a old post, but
is exactly what I almost ask today, so my question really is
any possible VBA code for this problem instead of formula, Please.
Thank you.
 
Upvote 0
As long as its in 1 layer (so X and Y) i made a function for just this.

It gives you the number, but the downside is that it doesn't mention how its arranged.

VBA Code:
Private Function nest2(X As Integer, Y As Integer, a As Integer, b As Integer) As Long
    Dim najvec As Long 'most parts calculated
    Dim kosi As Long 'number of parts from current loop
    Dim vmesni As Long 'an intermitten value for parts to make the calculation easier
    Dim zacetni As Long 'a second intermitten value for easier calculation
    najvec = 0 'set most to 0
    If (a = 0) Then 'check if any of the values = 0. If yes, returns 0.
        Exit Function
    End If
    If (b = 0) Then
        Exit Function
    End If
    If (X = 0) Then
        Exit Function
    End If
    If (Y = 0) Then
        Exit Function
    End If
    If a <= X And b <= Y Then 'if the plate width and legth are smaller than the part, then stop the calculation as a bigger part can not be nested on to a smaller plate
        For i = 1 To (X \ a) 'loop though all possible rows of one orientation
            zacetni = i * (Y \ b) 'calculates  the number of parts for one orientation
            vmesni = ((X - (i * a)) \ b) * (Y \ a) 'calculate the number of parts for the other orientation with the width reduced by the parts from the first orientation
            kosi = zacetni + vmesni 'number of larts = number of part for one orientation and number of parts for the other orientation on the remainder
            If kosi > najvec Then 'check if new number of parts is bigger than the max number and change accordingly
                najvec = kosi
            End If
        Next i
    End If
    nest2 = najvec 'return the biggest possible outcome
End Function

Function nest(pŠirina As Integer, pDolžina As Integer, kŠirina As Integer, kDolžina As Integer) As Long 'pŠirina, pDolžina ,eans pWidth and pLength same for k. P stands for plate while k stands for kos(part in english)
    Dim kosi As Long
    Dim kosi2 As Long

    
    nest = nest2(pŠirina, pDolžina, kŠirina, kDolžina) 'check p-width and p-length with k-width and k-length
    kosi2 = nest2(pDolžina, pŠirina, kŠirina, kDolžina) 'reverse the p direction and compare to k again

    If nest > kosi2 Then 'return the bigger of the 2 possible variations
    Else
        nest = kosi2
    End If
End Function

Sorry for the jank comments. I did a quick translation, as they were written in a different language before (slovenian).

You can call the function with =nest(part width, part length, plate width, plate length)

And it doesn't really do any fancy palletisation, just a row by row principle. And while it was intended for calculation the palletisation of rectangular parts on a metal plate, it can be used as a general how many smaller rectangles can be nested in to a bigger rectangle formula. Like boxes on a palette.
 
Upvote 0

Forum statistics

Threads
1,215,035
Messages
6,122,785
Members
449,095
Latest member
m_smith_solihull

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top