Calculate number of pallets based on item dimensions

H

hanzsolo

New Member
Joined
Aug 5, 2011
Messages
13
Hi,

I work in Logistics and at my company we have a hard time estimating the number of trucks that we are going to ship out of a warehouse. We have the item dimensions for each product that we ship and we know that we can fit 26 pallets onto a truck. However, we don't know how many items we can fit onto a pallet.

All of our pallets are 40" by 48" and the height must be under 56". Items can be stacked on a pallet in any orientation with length and width but the item must be arranged vertically (the height remains constant). I want to apply this formula for 30,000 + items. After I know the total number of pallets needed for an order, I can determine the number of trucks to order based on the number of pallets.

This is a sample of the data I have:

Item NumberLength (inches)Width (inches)Height (inchesQty ordered# of pallets
A10015181615
A2001472020
B1001041940
B2001342060

<TBODY>
</TBODY>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
PietBom said:
I made a workbook to calculate the number of pallets.
There are several variations to put the product on a pallet.
I think this helps a bit to start with.
Aantal_Pallets(pcb).xls downloaden
Click to expand...

Thank you Pietbom! This is a great start. I uploaded an example of an order that we placed this morning. Using the logic that PietBom created, this order estimated 32 pallets (6 more than the truckload constraint). However, we intermix products on a pallet and the attached order ended up being only 9 pallets.

I know that the combination of product could make this infinitely impossible, but I'm looking for a way to get a more realistic estimate of the number of pallets that are going to go on a truck. If there is some way to incrementally add layers onto existing layers, then it will provide a much more accurate estimate. Please let me know if this link doesn't work and I'll try it again. Thank you

Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1Pallet length=48inchhttp://www.mrexcel.com/forum/excel-questions/698171-calculate-number-pallets-based-item-dimensions.html
2Pallet width=40inch
3Pallet height=56inch
4
5orient1orient2orient3orient4orient5orient6orient7orient8
6Item NumberLength (inches)Width (inches)Height (inchesQty ordered# of palletsCombine Layers?Ntot_maxN_LayersNpalletLayerUsageN_LN_WNtotN_LN_WNtotN_L1N_L2N_W1N_W2NtotN_L1N_L2N_W1N_W2NtotN_L1N_L2N_W1N_W2NtotN_L1N_L2N_W1N_W2NtotN_L1N_L2N_W1N_W2NtotN_L1N_L2N_W1N_W2Ntot
710027.514.57.341372162.3%12231310131111232013220122113330123
811020.413.39.8562653084.8%2363132023421135302363013312334-1131
91201916.16.7281483263.7%2242243-12243-12244-22244-122622245-2226
101301916.16.7482483263.7%2242244-22244-12265-32245-222622246-3226
1114018.916.35.65642493664.2%2242245-32245-22266-42246-322622247-4226
1215018.916.35.65682493664.2%2242246-42246-32267-42267-422622248-5226
1316013.889.921.386031222485.9%34124287-33497-22468-334128-324423489-3246
141706.3713.3713.75411847279.8%721436188-1173238-1062289-1373249-1262306731810-146232
151806.3713.3712.75311847279.8%721436189-1373249-12623010-15732510-1462326731811-166234
161901215.51221943687.2%42833910-10431010-9321211-11431111-113211343912-123212
1720012.7513.256.531987279.2%33933911-933611-833912-1033612-9339333913-10339
1821010.2597.753116711276.9%4416531512-7451312-6341213-8451213-734113451514-83410
192201.750.5172121605612096098.4%2780###9622###13192796###13232280###14182796###14232280###222796###15232280###
202302.51.51.756115123216384100.0%192649432165121471932490141016264841571932509151016265001619325121691626490
2124015.112.614.4101932789.2%33932615-10331515-1023016-11331516-1123-1233617-1223-2
2225010.83.751711401144083.3%410401233616-24124016-23102817-34123217-23103134123618-231034
2326012.63.77.9560136725287.4%310301233617-23122717-23103118-33121818-23103433123619-231037
242707.6610.528140520095.0%6636854018-9683618-8564219-10683419-956415684020-105640
2528017.9154.71216116683.9%22432619-1423-419-14221020-1523-520-152210223621-152212
2629026.510.52.49014239258.0%13341420-714-820-713-121-714-721-7130114422-7131
273007.53.5126166426490.2%611661356521-56136121-45116122-56136722-45116656136523-551160
283108.957.111.82125410082.7%5525642422-13563222-13452323-14563123-1345274562424-144526
2932010.46.954.5541201224075.3%4520631823-12462023-11351424-13461824-1235123461825-133510
3033096.59.52130515091.4%5630742824-13572924-12462425-14572725-1346224572826-144620
31340151091111267293.8%341242825-14341925-1424-626-15341826-1524-8234827-1524-6
323501210101116580100.0%4416431226-19442826-1834627-20442827-193453441228-20344
333221.2031796
34
35

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Blad1​

Worksheet Formulas
CellFormula
S7=INT(($B$2-R7*$C7)/$B7)
T7=INT($B$1/$B7)
U7=INT($B$1/$C7)
V7=R7*T7+S7*U7
S8=INT(($B$2-R8*$C8)/$B8)
T8=INT($B$1/$B8)
U8=INT($B$1/$C8)
V8=R8*T8+S8*U8
S9=INT(($B$2-R9*$C9)/$B9)
T9=INT($B$1/$B9)
U9=INT($B$1/$C9)
V9=R9*T9+S9*U9
S10=INT(($B$2-R10*$C10)/$B10)
T10=INT($B$1/$B10)
U10=INT($B$1/$C10)
V10=R10*T10+S10*U10
S11=INT(($B$2-R11*$C11)/$B11)
T11=INT($B$1/$B11)
U11=INT($B$1/$C11)
V11=R11*T11+S11*U11
S12=INT(($B$2-R12*$C12)/$B12)
T12=INT($B$1/$B12)
U12=INT($B$1/$C12)
V12=R12*T12+S12*U12
S13=INT(($B$2-R13*$C13)/$B13)
T13=INT($B$1/$B13)
U13=INT($B$1/$C13)
V13=R13*T13+S13*U13
S14=INT(($B$2-R14*$C14)/$B14)
T14=INT($B$1/$B14)
U14=INT($B$1/$C14)
V14=R14*T14+S14*U14
S15=INT(($B$2-R15*$C15)/$B15)
T15=INT($B$1/$B15)
U15=INT($B$1/$C15)
V15=R15*T15+S15*U15
S16=INT(($B$2-R16*$C16)/$B16)
T16=INT($B$1/$B16)
U16=INT($B$1/$C16)
V16=R16*T16+S16*U16
S17=INT(($B$2-R17*$C17)/$B17)
T17=INT($B$1/$B17)
U17=INT($B$1/$C17)
V17=R17*T17+S17*U17
S18=INT(($B$2-R18*$C18)/$B18)
T18=INT($B$1/$B18)
U18=INT($B$1/$C18)
V18=R18*T18+S18*U18
S19=INT(($B$2-R19*$C19)/$B19)
T19=INT($B$1/$B19)
U19=INT($B$1/$C19)
V19=R19*T19+S19*U19
S20=INT(($B$2-R20*$C20)/$B20)
T20=INT($B$1/$B20)
U20=INT($B$1/$C20)
V20=R20*T20+S20*U20
S21=INT(($B$2-R21*$C21)/$B21)
T21=INT($B$1/$B21)
U21=INT($B$1/$C21)
V21=R21*T21+S21*U21
S22=INT(($B$2-R22*$C22)/$B22)
T22=INT($B$1/$B22)
U22=INT($B$1/$C22)
V22=R22*T22+S22*U22
S23=INT(($B$2-R23*$C23)/$B23)
T23=INT($B$1/$B23)
U23=INT($B$1/$C23)
V23=R23*T23+S23*U23
S24=INT(($B$2-R24*$C24)/$B24)
T24=INT($B$1/$B24)
U24=INT($B$1/$C24)
V24=R24*T24+S24*U24
S25=INT(($B$2-R25*$C25)/$B25)
T25=INT($B$1/$B25)
U25=INT($B$1/$C25)
V25=R25*T25+S25*U25
S26=INT(($B$2-R26*$C26)/$B26)
T26=INT($B$1/$B26)
U26=INT($B$1/$C26)
V26=R26*T26+S26*U26
S27=INT(($B$2-R27*$C27)/$B27)
T27=INT($B$1/$B27)
U27=INT($B$1/$C27)
V27=R27*T27+S27*U27
S28=INT(($B$2-R28*$C28)/$B28)
T28=INT($B$1/$B28)
U28=INT($B$1/$C28)
V28=R28*T28+S28*U28
S29=INT(($B$2-R29*$C29)/$B29)
T29=INT($B$1/$B29)
U29=INT($B$1/$C29)
V29=R29*T29+S29*U29
S30=INT(($B$2-R30*$C30)/$B30)
T30=INT($B$1/$B30)
U30=INT($B$1/$C30)
V30=R30*T30+S30*U30
S31=INT(($B$2-R31*$C31)/$B31)
T31=INT($B$1/$B31)
U31=INT($B$1/$C31)
V31=R31*T31+S31*U31
S32=INT(($B$2-R32*$C32)/$B32)
T32=INT($B$1/$B32)
U32=INT($B$1/$C32)
V32=R32*T32+S32*U32
X7=INT(($B$1-W7*$C7)/$B7)
Y7=INT($B$2/$B7)
Z7=INT($B$2/$C7)
AA7=W7*Y7+X7*Z7
X8=INT(($B$1-W8*$C8)/$B8)
Y8=INT($B$2/$B8)
Z8=INT($B$2/$C8)
AA8=W8*Y8+X8*Z8
X9=INT(($B$1-W9*$C9)/$B9)
Y9=INT($B$2/$B9)
Z9=INT($B$2/$C9)
AA9=W9*Y9+X9*Z9
X10=INT(($B$1-W10*$C10)/$B10)
Y10=INT($B$2/$B10)
Z10=INT($B$2/$C10)
AA10=W10*Y10+X10*Z10
X11=INT(($B$1-W11*$C11)/$B11)
Y11=INT($B$2/$B11)
Z11=INT($B$2/$C11)
AA11=W11*Y11+X11*Z11
X12=INT(($B$1-W12*$C12)/$B12)
Y12=INT($B$2/$B12)
Z12=INT($B$2/$C12)
AA12=W12*Y12+X12*Z12
X13=INT(($B$1-W13*$C13)/$B13)
Y13=INT($B$2/$B13)
Z13=INT($B$2/$C13)
AA13=W13*Y13+X13*Z13
X14=INT(($B$1-W14*$C14)/$B14)
Y14=INT($B$2/$B14)
Z14=INT($B$2/$C14)
AA14=W14*Y14+X14*Z14
X15=INT(($B$1-W15*$C15)/$B15)
Y15=INT($B$2/$B15)
Z15=INT($B$2/$C15)
AA15=W15*Y15+X15*Z15
X16=INT(($B$1-W16*$C16)/$B16)
Y16=INT($B$2/$B16)
Z16=INT($B$2/$C16)
AA16=W16*Y16+X16*Z16
X17=INT(($B$1-W17*$C17)/$B17)
Y17=INT($B$2/$B17)
Z17=INT($B$2/$C17)
AA17=W17*Y17+X17*Z17
X18=INT(($B$1-W18*$C18)/$B18)
Y18=INT($B$2/$B18)
Z18=INT($B$2/$C18)
AA18=W18*Y18+X18*Z18
X19=INT(($B$1-W19*$C19)/$B19)
Y19=INT($B$2/$B19)
Z19=INT($B$2/$C19)
AA19=W19*Y19+X19*Z19
X20=INT(($B$1-W20*$C20)/$B20)
Y20=INT($B$2/$B20)
Z20=INT($B$2/$C20)
AA20=W20*Y20+X20*Z20
X21=INT(($B$1-W21*$C21)/$B21)
Y21=INT($B$2/$B21)
Z21=INT($B$2/$C21)
AA21=W21*Y21+X21*Z21
X22=INT(($B$1-W22*$C22)/$B22)
Y22=INT($B$2/$B22)
Z22=INT($B$2/$C22)
AA22=W22*Y22+X22*Z22
X23=INT(($B$1-W23*$C23)/$B23)
Y23=INT($B$2/$B23)
Z23=INT($B$2/$C23)
AA23=W23*Y23+X23*Z23
X24=INT(($B$1-W24*$C24)/$B24)
Y24=INT($B$2/$B24)
Z24=INT($B$2/$C24)
AA24=W24*Y24+X24*Z24
X25=INT(($B$1-W25*$C25)/$B25)
Y25=INT($B$2/$B25)
Z25=INT($B$2/$C25)
AA25=W25*Y25+X25*Z25
X26=INT(($B$1-W26*$C26)/$B26)
Y26=INT($B$2/$B26)
Z26=INT($B$2/$C26)
AA26=W26*Y26+X26*Z26
X27=INT(($B$1-W27*$C27)/$B27)
Y27=INT($B$2/$B27)
Z27=INT($B$2/$C27)
AA27=W27*Y27+X27*Z27
X28=INT(($B$1-W28*$C28)/$B28)
Y28=INT($B$2/$B28)
Z28=INT($B$2/$C28)
AA28=W28*Y28+X28*Z28
X29=INT(($B$1-W29*$C29)/$B29)
Y29=INT($B$2/$B29)
Z29=INT($B$2/$C29)
AA29=W29*Y29+X29*Z29
X30=INT(($B$1-W30*$C30)/$B30)
Y30=INT($B$2/$B30)
Z30=INT($B$2/$C30)
AA30=W30*Y30+X30*Z30
X31=INT(($B$1-W31*$C31)/$B31)
Y31=INT($B$2/$B31)
Z31=INT($B$2/$C31)
AA31=W31*Y31+X31*Z31
X32=INT(($B$1-W32*$C32)/$B32)
Y32=INT($B$2/$B32)
Z32=INT($B$2/$C32)
AA32=W32*Y32+X32*Z32
AC7=INT(($B$2-AB7*$C7)/$B7)
AD7=INT($B$1/$B7)
AE7=INT($B$1/$C7)
AF7=AB7*AD7+AC7*AE7
AC8=INT(($B$2-AB8*$C8)/$B8)
AD8=INT($B$1/$B8)
AE8=INT($B$1/$C8)
AF8=AB8*AD8+AC8*AE8
AC9=INT(($B$2-AB9*$C9)/$B9)
AD9=INT($B$1/$B9)
AE9=INT($B$1/$C9)
AF9=AB9*AD9+AC9*AE9
AC10=INT(($B$2-AB10*$C10)/$B10)
AD10=INT($B$1/$B10)
AE10=INT($B$1/$C10)
AF10=AB10*AD10+AC10*AE10
AC11=INT(($B$2-AB11*$C11)/$B11)
AD11=INT($B$1/$B11)
AE11=INT($B$1/$C11)
AF11=AB11*AD11+AC11*AE11
AC12=INT(($B$2-AB12*$C12)/$B12)
AD12=INT($B$1/$B12)
AE12=INT($B$1/$C12)
AF12=AB12*AD12+AC12*AE12
AC13=INT(($B$2-AB13*$C13)/$B13)
AD13=INT($B$1/$B13)
AE13=INT($B$1/$C13)
AF13=AB13*AD13+AC13*AE13
AC14=INT(($B$2-AB14*$C14)/$B14)
AD14=INT($B$1/$B14)
AE14=INT($B$1/$C14)
AF14=AB14*AD14+AC14*AE14
AC15=INT(($B$2-AB15*$C15)/$B15)
AD15=INT($B$1/$B15)
AE15=INT($B$1/$C15)
AF15=AB15*AD15+AC15*AE15
AC16=INT(($B$2-AB16*$C16)/$B16)
AD16=INT($B$1/$B16)
AE16=INT($B$1/$C16)
AF16=AB16*AD16+AC16*AE16
AC17=INT(($B$2-AB17*$C17)/$B17)
AD17=INT($B$1/$B17)
AE17=INT($B$1/$C17)
AF17=AB17*AD17+AC17*AE17
AC18=INT(($B$2-AB18*$C18)/$B18)
AD18=INT($B$1/$B18)
AE18=INT($B$1/$C18)
AF18=AB18*AD18+AC18*AE18
AC19=INT(($B$2-AB19*$C19)/$B19)
AD19=INT($B$1/$B19)
AE19=INT($B$1/$C19)
AF19=AB19*AD19+AC19*AE19
AC20=INT(($B$2-AB20*$C20)/$B20)
AD20=INT($B$1/$B20)
AE20=INT($B$1/$C20)
AF20=AB20*AD20+AC20*AE20
AC21=INT(($B$2-AB21*$C21)/$B21)
AD21=INT($B$1/$B21)
AE21=INT($B$1/$C21)
AF21=AB21*AD21+AC21*AE21
AC22=INT(($B$2-AB22*$C22)/$B22)
AD22=INT($B$1/$B22)
AE22=INT($B$1/$C22)
AF22=AB22*AD22+AC22*AE22
AC23=INT(($B$2-AB23*$C23)/$B23)
AD23=INT($B$1/$B23)
AE23=INT($B$1/$C23)
AF23=AB23*AD23+AC23*AE23
AC24=INT(($B$2-AB24*$C24)/$B24)
AD24=INT($B$1/$B24)
AE24=INT($B$1/$C24)
AF24=AB24*AD24+AC24*AE24
AC25=INT(($B$2-AB25*$C25)/$B25)
AD25=INT($B$1/$B25)
AE25=INT($B$1/$C25)
AF25=AB25*AD25+AC25*AE25
AC26=INT(($B$2-AB26*$C26)/$B26)
AD26=INT($B$1/$B26)
AE26=INT($B$1/$C26)
AF26=AB26*AD26+AC26*AE26
AC27=INT(($B$2-AB27*$C27)/$B27)
AD27=INT($B$1/$B27)
AE27=INT($B$1/$C27)
AF27=AB27*AD27+AC27*AE27
AC28=INT(($B$2-AB28*$C28)/$B28)
AD28=INT($B$1/$B28)
AE28=INT($B$1/$C28)
AF28=AB28*AD28+AC28*AE28
AC29=INT(($B$2-AB29*$C29)/$B29)
AD29=INT($B$1/$B29)
AE29=INT($B$1/$C29)
AF29=AB29*AD29+AC29*AE29
AC30=INT(($B$2-AB30*$C30)/$B30)
AD30=INT($B$1/$B30)
AE30=INT($B$1/$C30)
AF30=AB30*AD30+AC30*AE30
AC31=INT(($B$2-AB31*$C31)/$B31)
AD31=INT($B$1/$B31)
AE31=INT($B$1/$C31)
AF31=AB31*AD31+AC31*AE31
AC32=INT(($B$2-AB32*$C32)/$B32)
AD32=INT($B$1/$B32)
AE32=INT($B$1/$C32)
AF32=AB32*AD32+AC32*AE32
AH7=INT(($B$1-AG7*$C7)/$B7)
AI7=INT($B$2/$B7)
AJ7=INT($B$2/$C7)
AK7=AG7*AI7+AH7*AJ7
AH8=INT(($B$1-AG8*$C8)/$B8)
AI8=INT($B$2/$B8)
AJ8=INT($B$2/$C8)
AK8=AG8*AI8+AH8*AJ8
AH9=INT(($B$1-AG9*$C9)/$B9)
AI9=INT($B$2/$B9)
AJ9=INT($B$2/$C9)
AK9=AG9*AI9+AH9*AJ9
AH10=INT(($B$1-AG10*$C10)/$B10)
AI10=INT($B$2/$B10)
AJ10=INT($B$2/$C10)
AK10=AG10*AI10+AH10*AJ10
AH11=INT(($B$1-AG11*$C11)/$B11)
AI11=INT($B$2/$B11)
AJ11=INT($B$2/$C11)
AK11=AG11*AI11+AH11*AJ11
AH12=INT(($B$1-AG12*$C12)/$B12)
AI12=INT($B$2/$B12)
AJ12=INT($B$2/$C12)
AK12=AG12*AI12+AH12*AJ12
AH13=INT(($B$1-AG13*$C13)/$B13)
AI13=INT($B$2/$B13)
AJ13=INT($B$2/$C13)
AK13=AG13*AI13+AH13*AJ13
AH14=INT(($B$1-AG14*$C14)/$B14)
AI14=INT($B$2/$B14)
AJ14=INT($B$2/$C14)
AK14=AG14*AI14+AH14*AJ14
AH15=INT(($B$1-AG15*$C15)/$B15)
AI15=INT($B$2/$B15)
AJ15=INT($B$2/$C15)
AK15=AG15*AI15+AH15*AJ15
AH16=INT(($B$1-AG16*$C16)/$B16)
AI16=INT($B$2/$B16)
AJ16=INT($B$2/$C16)
AK16=AG16*AI16+AH16*AJ16
AH17=INT(($B$1-AG17*$C17)/$B17)
AI17=INT($B$2/$B17)
AJ17=INT($B$2/$C17)
AK17=AG17*AI17+AH17*AJ17
AH18=INT(($B$1-AG18*$C18)/$B18)
AI18=INT($B$2/$B18)
AJ18=INT($B$2/$C18)
AK18=AG18*AI18+AH18*AJ18
AH19=INT(($B$1-AG19*$C19)/$B19)
AI19=INT($B$2/$B19)
AJ19=INT($B$2/$C19)
AK19=AG19*AI19+AH19*AJ19
AH20=INT(($B$1-AG20*$C20)/$B20)
AI20=INT($B$2/$B20)
AJ20=INT($B$2/$C20)
AK20=AG20*AI20+AH20*AJ20
AH21=INT(($B$1-AG21*$C21)/$B21)
AI21=INT($B$2/$B21)
AJ21=INT($B$2/$C21)
AK21=AG21*AI21+AH21*AJ21
AH22=INT(($B$1-AG22*$C22)/$B22)
AI22=INT($B$2/$B22)
AJ22=INT($B$2/$C22)
AK22=AG22*AI22+AH22*AJ22
AH23=INT(($B$1-AG23*$C23)/$B23)
AI23=INT($B$2/$B23)
AJ23=INT($B$2/$C23)
AK23=AG23*AI23+AH23*AJ23
AH24=INT(($B$1-AG24*$C24)/$B24)
AI24=INT($B$2/$B24)
AJ24=INT($B$2/$C24)
AK24=AG24*AI24+AH24*AJ24
AH25=INT(($B$1-AG25*$C25)/$B25)
AI25=INT($B$2/$B25)
AJ25=INT($B$2/$C25)
AK25=AG25*AI25+AH25*AJ25
AH26=INT(($B$1-AG26*$C26)/$B26)
AI26=INT($B$2/$B26)
AJ26=INT($B$2/$C26)
AK26=AG26*AI26+AH26*AJ26
AH27=INT(($B$1-AG27*$C27)/$B27)
AI27=INT($B$2/$B27)
AJ27=INT($B$2/$C27)
AK27=AG27*AI27+AH27*AJ27
AH28=INT(($B$1-AG28*$C28)/$B28)
AI28=INT($B$2/$B28)
AJ28=INT($B$2/$C28)
AK28=AG28*AI28+AH28*AJ28
AH29=INT(($B$1-AG29*$C29)/$B29)
AI29=INT($B$2/$B29)
AJ29=INT($B$2/$C29)
AK29=AG29*AI29+AH29*AJ29
AH30=INT(($B$1-AG30*$C30)/$B30)
AI30=INT($B$2/$B30)
AJ30=INT($B$2/$C30)
AK30=AG30*AI30+AH30*AJ30
AH31=INT(($B$1-AG31*$C31)/$B31)
AI31=INT($B$2/$B31)
AJ31=INT($B$2/$C31)
AK31=AG31*AI31+AH31*AJ31
AH32=INT(($B$1-AG32*$C32)/$B32)
AI32=INT($B$2/$B32)
AJ32=INT($B$2/$C32)
AK32=AG32*AI32+AH32*AJ32
AM7=INT(($B$2-AL7*$C7)/$B7)
AN7=INT($B$1/$B7)
AO7=INT($B$1/$C7)
AP7=AL7*AN7+AM7*AO7
AM8=INT(($B$2-AL8*$C8)/$B8)
AN8=INT($B$1/$B8)
AO8=INT($B$1/$C8)
AP8=AL8*AN8+AM8*AO8
AM9=INT(($B$2-AL9*$C9)/$B9)
AN9=INT($B$1/$B9)
AO9=INT($B$1/$C9)
AP9=AL9*AN9+AM9*AO9
AM10=INT(($B$2-AL10*$C10)/$B10)
AN10=INT($B$1/$B10)
AO10=INT($B$1/$C10)
AP10=AL10*AN10+AM10*AO10
AM11=INT(($B$2-AL11*$C11)/$B11)
AN11=INT($B$1/$B11)
AO11=INT($B$1/$C11)
AP11=AL11*AN11+AM11*AO11
AM12=INT(($B$2-AL12*$C12)/$B12)
AN12=INT($B$1/$B12)
AO12=INT($B$1/$C12)
AP12=AL12*AN12+AM12*AO12
AM13=INT(($B$2-AL13*$C13)/$B13)
AN13=INT($B$1/$B13)
AO13=INT($B$1/$C13)
AP13=AL13*AN13+AM13*AO13
AM14=INT(($B$2-AL14*$C14)/$B14)
AN14=INT($B$1/$B14)
AO14=INT($B$1/$C14)
AP14=AL14*AN14+AM14*AO14
AM15=INT(($B$2-AL15*$C15)/$B15)
AN15=INT($B$1/$B15)
AO15=INT($B$1/$C15)
AP15=AL15*AN15+AM15*AO15
AM16=INT(($B$2-AL16*$C16)/$B16)
AN16=INT($B$1/$B16)
AO16=INT($B$1/$C16)
AP16=AL16*AN16+AM16*AO16
AM17=INT(($B$2-AL17*$C17)/$B17)
AN17=INT($B$1/$B17)
AO17=INT($B$1/$C17)
AP17=AL17*AN17+AM17*AO17
AM18=INT(($B$2-AL18*$C18)/$B18)
AN18=INT($B$1/$B18)
AO18=INT($B$1/$C18)
AP18=AL18*AN18+AM18*AO18
AM19=INT(($B$2-AL19*$C19)/$B19)
AN19=INT($B$1/$B19)
AO19=INT($B$1/$C19)
AP19=AL19*AN19+AM19*AO19
AM20=INT(($B$2-AL20*$C20)/$B20)
AN20=INT($B$1/$B20)
AO20=INT($B$1/$C20)
AP20=AL20*AN20+AM20*AO20
AM21=INT(($B$2-AL21*$C21)/$B21)
AN21=INT($B$1/$B21)
AO21=INT($B$1/$C21)
AP21=AL21*AN21+AM21*AO21
AM22=INT(($B$2-AL22*$C22)/$B22)
AN22=INT($B$1/$B22)
AO22=INT($B$1/$C22)
AP22=AL22*AN22+AM22*AO22
AM23=INT(($B$2-AL23*$C23)/$B23)
AN23=INT($B$1/$B23)
AO23=INT($B$1/$C23)
AP23=AL23*AN23+AM23*AO23
AM24=INT(($B$2-AL24*$C24)/$B24)
AN24=INT($B$1/$B24)
AO24=INT($B$1/$C24)
AP24=AL24*AN24+AM24*AO24
AM25=INT(($B$2-AL25*$C25)/$B25)
AN25=INT($B$1/$B25)
AO25=INT($B$1/$C25)
AP25=AL25*AN25+AM25*AO25
AM26=INT(($B$2-AL26*$C26)/$B26)
AN26=INT($B$1/$B26)
AO26=INT($B$1/$C26)
AP26=AL26*AN26+AM26*AO26
AM27=INT(($B$2-AL27*$C27)/$B27)
AN27=INT($B$1/$B27)
AO27=INT($B$1/$C27)
AP27=AL27*AN27+AM27*AO27
AM28=INT(($B$2-AL28*$C28)/$B28)
AN28=INT($B$1/$B28)
AO28=INT($B$1/$C28)
AP28=AL28*AN28+AM28*AO28
AM29=INT(($B$2-AL29*$C29)/$B29)
AN29=INT($B$1/$B29)
AO29=INT($B$1/$C29)
AP29=AL29*AN29+AM29*AO29
AM30=INT(($B$2-AL30*$C30)/$B30)
AN30=INT($B$1/$B30)
AO30=INT($B$1/$C30)
AP30=AL30*AN30+AM30*AO30
AM31=INT(($B$2-AL31*$C31)/$B31)
AN31=INT($B$1/$B31)
AO31=INT($B$1/$C31)
AP31=AL31*AN31+AM31*AO31
AM32=INT(($B$2-AL32*$C32)/$B32)
AN32=INT($B$1/$B32)
AO32=INT($B$1/$C32)
AP32=AL32*AN32+AM32*AO32
AR7=INT(($B$1-AQ7*$C7)/$B7)
AS7=INT($B$2/$B7)
AT7=INT($B$2/$C7)
AU7=AQ7*AS7+AR7*AT7
AR8=INT(($B$1-AQ8*$C8)/$B8)
AS8=INT($B$2/$B8)
AT8=INT($B$2/$C8)
AU8=AQ8*AS8+AR8*AT8
AR9=INT(($B$1-AQ9*$C9)/$B9)
AS9=INT($B$2/$B9)
AT9=INT($B$2/$C9)
AU9=AQ9*AS9+AR9*AT9
AR10=INT(($B$1-AQ10*$C10)/$B10)
AS10=INT($B$2/$B10)
AT10=INT($B$2/$C10)
AU10=AQ10*AS10+AR10*AT10
AR11=INT(($B$1-AQ11*$C11)/$B11)
AS11=INT($B$2/$B11)
AT11=INT($B$2/$C11)
AU11=AQ11*AS11+AR11*AT11
AR12=INT(($B$1-AQ12*$C12)/$B12)
AS12=INT($B$2/$B12)
AT12=INT($B$2/$C12)
AU12=AQ12*AS12+AR12*AT12
AR13=INT(($B$1-AQ13*$C13)/$B13)
AS13=INT($B$2/$B13)
AT13=INT($B$2/$C13)
AU13=AQ13*AS13+AR13*AT13
AR14=INT(($B$1-AQ14*$C14)/$B14)
AS14=INT($B$2/$B14)
AT14=INT($B$2/$C14)
AU14=AQ14*AS14+AR14*AT14
AR15=INT(($B$1-AQ15*$C15)/$B15)
AS15=INT($B$2/$B15)
AT15=INT($B$2/$C15)
AU15=AQ15*AS15+AR15*AT15
AR16=INT(($B$1-AQ16*$C16)/$B16)
AS16=INT($B$2/$B16)
AT16=INT($B$2/$C16)
AU16=AQ16*AS16+AR16*AT16
AR17=INT(($B$1-AQ17*$C17)/$B17)
AS17=INT($B$2/$B17)
AT17=INT($B$2/$C17)
AU17=AQ17*AS17+AR17*AT17
AR18=INT(($B$1-AQ18*$C18)/$B18)
AS18=INT($B$2/$B18)
AT18=INT($B$2/$C18)
AU18=AQ18*AS18+AR18*AT18
AR19=INT(($B$1-AQ19*$C19)/$B19)
AS19=INT($B$2/$B19)
AT19=INT($B$2/$C19)
AU19=AQ19*AS19+AR19*AT19
AR20=INT(($B$1-AQ20*$C20)/$B20)
AS20=INT($B$2/$B20)
AT20=INT($B$2/$C20)
AU20=AQ20*AS20+AR20*AT20
AR21=INT(($B$1-AQ21*$C21)/$B21)
AS21=INT($B$2/$B21)
AT21=INT($B$2/$C21)
AU21=AQ21*AS21+AR21*AT21
AR22=INT(($B$1-AQ22*$C22)/$B22)
AS22=INT($B$2/$B22)
AT22=INT($B$2/$C22)
AU22=AQ22*AS22+AR22*AT22
AR23=INT(($B$1-AQ23*$C23)/$B23)
AS23=INT($B$2/$B23)
AT23=INT($B$2/$C23)
AU23=AQ23*AS23+AR23*AT23
AR24=INT(($B$1-AQ24*$C24)/$B24)
AS24=INT($B$2/$B24)
AT24=INT($B$2/$C24)
AU24=AQ24*AS24+AR24*AT24
AR25=INT(($B$1-AQ25*$C25)/$B25)
AS25=INT($B$2/$B25)
AT25=INT($B$2/$C25)
AU25=AQ25*AS25+AR25*AT25
AR26=INT(($B$1-AQ26*$C26)/$B26)
AS26=INT($B$2/$B26)
AT26=INT($B$2/$C26)
AU26=AQ26*AS26+AR26*AT26
AR27=INT(($B$1-AQ27*$C27)/$B27)
AS27=INT($B$2/$B27)
AT27=INT($B$2/$C27)
AU27=AQ27*AS27+AR27*AT27
AR28=INT(($B$1-AQ28*$C28)/$B28)
AS28=INT($B$2/$B28)
AT28=INT($B$2/$C28)
AU28=AQ28*AS28+AR28*AT28
AR29=INT(($B$1-AQ29*$C29)/$B29)
AS29=INT($B$2/$B29)
AT29=INT($B$2/$C29)
AU29=AQ29*AS29+AR29*AT29
AR30=INT(($B$1-AQ30*$C30)/$B30)
AS30=INT($B$2/$B30)
AT30=INT($B$2/$C30)
AU30=AQ30*AS30+AR30*AT30
AR31=INT(($B$1-AQ31*$C31)/$B31)
AS31=INT($B$2/$B31)
AT31=INT($B$2/$C31)
AU31=AQ31*AS31+AR31*AT31
AR32=INT(($B$1-AQ32*$C32)/$B32)
AS32=INT($B$2/$B32)
AT32=INT($B$2/$C32)
AU32=AQ32*AS32+AR32*AT32
A8=A7+10
A9=A8+10
A10=A9+10
A11=A10+10
A12=A11+10
A13=A12+10
A14=A13+10
A15=A14+10
A16=A15+10
A17=A16+10
A18=A17+10
A19=A18+10
A20=A19+10
A21=A20+10
A22=A21+10
A23=A22+10
A24=A23+10
A25=A24+10
A26=A25+10
A27=A26+10
A28=A27+10
A29=A28+10
A30=A29+10
A31=A30+10
A32=A31+10
F7=INT(E7/J7+1)
F8=INT(E8/J8+1)
F9=INT(E9/J9+1)
F10=INT(E10/J10+1)
F11=INT(E11/J11+1)
F12=INT(E12/J12+1)
F13=INT(E13/J13+1)
F14=INT(E14/J14+1)
F15=INT(E15/J15+1)
F16=INT(E16/J16+1)
F17=INT(E17/J17+1)
F18=INT(E18/J18+1)
F19=INT(E19/J19+1)
F20=INT(E20/J20+1)
F21=INT(E21/J21+1)
F22=INT(E22/J22+1)
F23=INT(E23/J23+1)
F24=INT(E24/J24+1)
F25=INT(E25/J25+1)
F26=INT(E26/J26+1)
F27=INT(E27/J27+1)
F28=INT(E28/J28+1)
F29=INT(E29/J29+1)
F30=INT(E30/J30+1)
F31=INT(E31/J31+1)
F32=INT(E32/J32+1)
F33=SUM(F7:F32)
H7=MAX(N7,Q7)
I7=INT($B$3/D7)
J7=I7*H7
K7=(B7*C7*H7)/($B$1*$B$2)
L7=INT($B$1/B7)
M7=INT($B$2/C7)
N7=L7*M7
O7=INT($B$1/C7)
P7=INT($B$2/B7)
Q7=O7*P7
H8=MAX(N8,Q8)
I8=INT($B$3/D8)
J8=I8*H8
K8=(B8*C8*H8)/($B$1*$B$2)
L8=INT($B$1/B8)
M8=INT($B$2/C8)
N8=L8*M8
O8=INT($B$1/C8)
P8=INT($B$2/B8)
Q8=O8*P8
H9=MAX(N9,Q9)
I9=INT($B$3/D9)
J9=I9*H9
K9=(B9*C9*H9)/($B$1*$B$2)
L9=INT($B$1/B9)
M9=INT($B$2/C9)
N9=L9*M9
O9=INT($B$1/C9)
P9=INT($B$2/B9)
Q9=O9*P9
H10=MAX(N10,Q10)
I10=INT($B$3/D10)
J10=I10*H10
K10=(B10*C10*H10)/($B$1*$B$2)
L10=INT($B$1/B10)
M10=INT($B$2/C10)
N10=L10*M10
O10=INT($B$1/C10)
P10=INT($B$2/B10)
Q10=O10*P10
H11=MAX(N11,Q11)
I11=INT($B$3/D11)
J11=I11*H11
K11=(B11*C11*H11)/($B$1*$B$2)
L11=INT($B$1/B11)
M11=INT($B$2/C11)
N11=L11*M11
O11=INT($B$1/C11)
P11=INT($B$2/B11)
Q11=O11*P11
H12=MAX(N12,Q12)
I12=INT($B$3/D12)
J12=I12*H12
K12=(B12*C12*H12)/($B$1*$B$2)
L12=INT($B$1/B12)
M12=INT($B$2/C12)
N12=L12*M12
O12=INT($B$1/C12)
P12=INT($B$2/B12)
Q12=O12*P12
H13=MAX(N13,Q13)
I13=INT($B$3/D13)
J13=I13*H13
K13=(B13*C13*H13)/($B$1*$B$2)
L13=INT($B$1/B13)
M13=INT($B$2/C13)
N13=L13*M13
O13=INT($B$1/C13)
P13=INT($B$2/B13)
Q13=O13*P13
H14=MAX(N14,Q14)
I14=INT($B$3/D14)
J14=I14*H14
K14=(B14*C14*H14)/($B$1*$B$2)
L14=INT($B$1/B14)
M14=INT($B$2/C14)
N14=L14*M14
O14=INT($B$1/C14)
P14=INT($B$2/B14)
Q14=O14*P14
H15=MAX(N15,Q15)
I15=INT($B$3/D15)
J15=I15*H15
K15=(B15*C15*H15)/($B$1*$B$2)
L15=INT($B$1/B15)
M15=INT($B$2/C15)
N15=L15*M15
O15=INT($B$1/C15)
P15=INT($B$2/B15)
Q15=O15*P15
H16=MAX(N16,Q16)
I16=INT($B$3/D16)
J16=I16*H16
K16=(B16*C16*H16)/($B$1*$B$2)
L16=INT($B$1/B16)
M16=INT($B$2/C16)
N16=L16*M16
O16=INT($B$1/C16)
P16=INT($B$2/B16)
Q16=O16*P16
H17=MAX(N17,Q17)
I17=INT($B$3/D17)
J17=I17*H17
K17=(B17*C17*H17)/($B$1*$B$2)
L17=INT($B$1/B17)
M17=INT($B$2/C17)
N17=L17*M17
O17=INT($B$1/C17)
P17=INT($B$2/B17)
Q17=O17*P17
H18=MAX(N18,Q18)
I18=INT($B$3/D18)
J18=I18*H18
K18=(B18*C18*H18)/($B$1*$B$2)
L18=INT($B$1/B18)
M18=INT($B$2/C18)
N18=L18*M18
O18=INT($B$1/C18)
P18=INT($B$2/B18)
Q18=O18*P18
H19=MAX(N19,Q19)
I19=INT($B$3/D19)
J19=I19*H19
K19=(B19*C19*H19)/($B$1*$B$2)
L19=INT($B$1/B19)
M19=INT($B$2/C19)
N19=L19*M19
O19=INT($B$1/C19)
P19=INT($B$2/B19)
Q19=O19*P19
H20=MAX(N20,Q20)
I20=INT($B$3/D20)
J20=I20*H20
K20=(B20*C20*H20)/($B$1*$B$2)
L20=INT($B$1/B20)
M20=INT($B$2/C20)
N20=L20*M20
O20=INT($B$1/C20)
P20=INT($B$2/B20)
Q20=O20*P20
H21=MAX(N21,Q21)
I21=INT($B$3/D21)
J21=I21*H21
K21=(B21*C21*H21)/($B$1*$B$2)
L21=INT($B$1/B21)
M21=INT($B$2/C21)
N21=L21*M21
O21=INT($B$1/C21)
P21=INT($B$2/B21)
Q21=O21*P21
H22=MAX(N22,Q22)
I22=INT($B$3/D22)
J22=I22*H22
K22=(B22*C22*H22)/($B$1*$B$2)
L22=INT($B$1/B22)
M22=INT($B$2/C22)
N22=L22*M22
O22=INT($B$1/C22)
P22=INT($B$2/B22)
Q22=O22*P22
H23=MAX(N23,Q23)
I23=INT($B$3/D23)
J23=I23*H23
K23=(B23*C23*H23)/($B$1*$B$2)
L23=INT($B$1/B23)
M23=INT($B$2/C23)
N23=L23*M23
O23=INT($B$1/C23)
P23=INT($B$2/B23)
Q23=O23*P23
H24=MAX(N24,Q24)
I24=INT($B$3/D24)
J24=I24*H24
K24=(B24*C24*H24)/($B$1*$B$2)
L24=INT($B$1/B24)
M24=INT($B$2/C24)
N24=L24*M24
O24=INT($B$1/C24)
P24=INT($B$2/B24)
Q24=O24*P24
H25=MAX(N25,Q25)
I25=INT($B$3/D25)
J25=I25*H25
K25=(B25*C25*H25)/($B$1*$B$2)
L25=INT($B$1/B25)
M25=INT($B$2/C25)
N25=L25*M25
O25=INT($B$1/C25)
P25=INT($B$2/B25)
Q25=O25*P25
H26=MAX(N26,Q26)
I26=INT($B$3/D26)
J26=I26*H26
K26=(B26*C26*H26)/($B$1*$B$2)
L26=INT($B$1/B26)
M26=INT($B$2/C26)
N26=L26*M26
O26=INT($B$1/C26)
P26=INT($B$2/B26)
Q26=O26*P26
H27=MAX(N27,Q27)
I27=INT($B$3/D27)
J27=I27*H27
K27=(B27*C27*H27)/($B$1*$B$2)
L27=INT($B$1/B27)
M27=INT($B$2/C27)
N27=L27*M27
O27=INT($B$1/C27)
P27=INT($B$2/B27)
Q27=O27*P27
H28=MAX(N28,Q28)
I28=INT($B$3/D28)
J28=I28*H28
K28=(B28*C28*H28)/($B$1*$B$2)
L28=INT($B$1/B28)
M28=INT($B$2/C28)
N28=L28*M28
O28=INT($B$1/C28)
P28=INT($B$2/B28)
Q28=O28*P28
H29=MAX(N29,Q29)
I29=INT($B$3/D29)
J29=I29*H29
K29=(B29*C29*H29)/($B$1*$B$2)
L29=INT($B$1/B29)
M29=INT($B$2/C29)
N29=L29*M29
O29=INT($B$1/C29)
P29=INT($B$2/B29)
Q29=O29*P29
H30=MAX(N30,Q30)
I30=INT($B$3/D30)
J30=I30*H30
K30=(B30*C30*H30)/($B$1*$B$2)
L30=INT($B$1/B30)
M30=INT($B$2/C30)
N30=L30*M30
O30=INT($B$1/C30)
P30=INT($B$2/B30)
Q30=O30*P30
H31=MAX(N31,Q31)
I31=INT($B$3/D31)
J31=I31*H31
K31=(B31*C31*H31)/($B$1*$B$2)
L31=INT($B$1/B31)
M31=INT($B$2/C31)
N31=L31*M31
O31=INT($B$1/C31)
P31=INT($B$2/B31)
Q31=O31*P31
H32=MAX(N32,Q32)
I32=INT($B$3/D32)
J32=I32*H32
K32=(B32*C32*H32)/($B$1*$B$2)
L32=INT($B$1/B32)
M32=INT($B$2/C32)
N32=L32*M32
O32=INT($B$1/C32)
P32=INT($B$2/B32)
Q32=O32*P32
K33=SUM(K7:K32)

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>
 
Upvote 0
When you start mixing products on 1 pallet then there will be infinite variants.
I have not enough time to make a program for this.
Sorry.
 
Upvote 0
I understand that it may produce infinite possibilities, but what if we looked at it by keeping the same products on the same layer. For example, starting from the bottom; adding 1.5 layers of a product, then rounding up to 2 layers, adding another layer to that layer until it built to a full pallet. Does this make sense?
 
Upvote 0
How about total volume of order divided by total volume of a pallet:

Excel Workbook
ABCDEFGHIJ
1Item NumberLength (inches)Width (inches)Height (inchesQty orderedVolumeTotal VolumePallet VolumePallets
210027.514.57.3411643.5934966.61075209
311020.413.39.856148900.4
41201916.16.72857386.84
51301916.16.74898377.44
614018.916.35.6564111398.1
715018.916.35.6568118360.5
816013.889.921.3860176272.1
91706.3713.3713.7544684.18
101806.3713.3712.7533257.634
111901215.51224464
1220012.7513.256.533294.281
1321010.2597.7532144.813
142201.750.517263
152302.51.51.7561400.3125
1624015.112.614.41027397.44
1725010.83.7517134165.8
1826012.63.77.956022237.74
192707.6610.52813406.4
2028017.9154.71215143.4
2129026.510.52.49060102
223007.53.51261890
233108.957.111.821499.662
2432010.46.954.5541315.496
2533096.59.521111.5
26340151091114850
2735012101011200
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Pallet calculations
Replies
3
Views
2K
Bill Rockenbach
B
J
  • Question
Calculate Which Size Box to use when packing multiple items with differing sizes
Replies
4
Views
1K
Jonozee
J
N
  • Question
How to calculate a number of boxes on pallet
Replies
7
Views
5K
johnnyL
J
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
118
Nick Loughton
N
PuntingJawa
  • Question
Volume visualization for a truck load planner
Replies
8
Views
308
PuntingJawa
PuntingJawa

Forum statistics

Threads
1,214,403
Messages
6,119,308
Members
448,886
Latest member
GBCTeacher

Share this page

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