Truck load capacity

[PuntingJawa]

Good afternoon MrExcel board!

I'm trying to make an excel sheet to create load capacity for shipments. I have started with basics but am having trouble bringing this to the next level. The main issue I am having is figuring out how many crates need to be placed on which row and how many can fit. There are 2 items that can only be placed on the bottom row and 3 items that can be placed on any of the 3 rows. I feel like I am on the right path but don't know how to go about working it.

I'll explain what I am attempting.

I want E12-E16, G14-G16, and I14-I16 to show how many crates can fit per row with length used.

A few issues I have a problem with are, I don't know how to formulate the above mentioned to allow use of 636. Example In D15 where we go into negative there are 314 inches left. If divided by D8 (length of DO crate) it comes to 4.8. Since we can't use a fraction of a location the .8 is gone. I would need it to reflect that. Also, Each row can hold 2 crates. I haven't figured out how to do 1/2 rows yet.
The next issue is F14-F16 and I14-I16. Each "Row" has only 636 inches. So my current formula won't work once the above is figured out. I would need to remove the length of crates already used.

Any help with this would be amazing. Please let me know if a better explantion is required. I'll have the board up when I return home and still continue to work on this on my own for now.

Truck load template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4			Type / Max Stack	Length	Width	Height	Weight	QTY	Total (LBS)	Total Inch		Notes
5			Box Crate (1x Max)	42	57	43	850	6	5100	252		636" Per "Lane"
6			Car Kit (1x Max)	44	44	81	250	0	0	0		1xRow = 2x "Lane"
7			EDO Crate (3x Max)	70	37	25	450	1	450	70		1,272" Both Lanes
8			DO Crate (3x Max)	65	37	25	750	24	18000	1560		2,544" If Stacked
9	LE QTY	240	LE Pallet (3x Max)	96	48	16	960	1	960	96		32,000 (Max LBS set)
10												Actual Max 34k LBS
11			Load plan	1 Row	QTY 1	2 Row	QTY 2	3 row	QTY 3	Stackable		Based on 53'x8.2'x8'
12			Box Crate (1x Max)	384		1020		2292		No		636x98.4x96 inches
13			Car Kit (1x Max)	384		1020		2292		No
14			EDO Crate (3x Max)	314		950		2222		Yes
15			DO Crate (3x Max)	-1246		-610		662		Yes
16			LE Pallet (3x Max)	-1342		-706		566		Yes
17
18			Total Weight in LBS
19			24510
Sheet1

Cell Formulas
Range	Formula
I5:I9	I5	=H5*G5
J5:J9	J5	=D5*H5
G9	G9	=B9*4
D12	D12	=636-J5
D13:D16	D13	=D12-J6
F12	F12	=1272-J5
F13:F16	F13	=F12-J6
H12	H12	=2544-J5
H13:H16	H13	=H12-J6
C19	C19	=I9+I5+I7+I8+I6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12:I16	Cell Value	<0	text	NO
F12:G16	Cell Value	<0	text	NO
D12:E16	Cell Value	<0	text	NO
H12:I16	Cell Value	between 0 and 2544	text	NO
F12:G16	Cell Value	between 0 and 1272	text	NO
D12:E16	Cell Value	between 0 and 636	text	NO
C19	Cell Value	<32000	text	NO
C19	Cell Value	>32000	text	NO

 

[PuntingJawa]

I have managed a little more work on this. I'm using a lot of simplistic formulas that are about what my skill level is.
I've given a visual example of what a 2 tier truck would look like at columns K through M with the current data in this sheet.

I believe that I fixed the formula somewhat on the load plan area at C11/D11 to C15/D15. It's not perfect but it worked for the most part as a guideline. Some pallets have wiggle room to turn and can be forgiving.

The next part I would like to try, and I am finding it difficult, is to create a formula that reflects the example of K through N in the Capacity section starting at C19 through H19.

Each lane is 636' long.
Each crate will alternate between lane 1-lane 2 until 636 inches is exhausted but not exceeded. (Example at M15 where there are 37 inches left but nothing fits so it's wasted space). Then proceed to lane 3 and 4. Then 5 and 6.
Box crates and Car kits cannot be stacked so they take priority in lanes 1 and 2.

Is there a way for me to reflect the example I have placed into the capacity area? One major problem am having is trying to come up with a formula that accounts for both left and right sides while removing the length down the lanes with odd numbers.

Row 1

QTY

Remaining

Type / Max Stack

Length

Width

Height

Weight

QTY

Total LBS

Total Inch

BC

DO

6xBC (None left) 6xDO (18 left) 321 inch

Box Crate (1x Max)

42

57

43

850

6

5100

252

BC

Car Kit (1x Max)

44

44

81

250

0

0

0

DO

EDO Crate (3x Max)

70

37

25

450

1

450

70

BC

DO

DO Crate (3x Max)

65

37

25

750

24

18000

1560

BC

LE Pallet (3x Max)

96

48

16

960

1

960

96

DO

LE QTY (LH+RH)

240

Total LBS

24510

BC

DO

BC

Load plan (Inch Left)

1 Row

2 Row

3 Row

Stackable

Notes

DO

315 Inches left

Box Crate (1x Max)

1020​

No

636" Per "Lane"

DO

DO

8xDO (10 left) 260 inch

Car Kit (1x Max)

1020​

No

1xRow=2x"Lane"

DO

DO

EDO Crate (3x Max)

950​

Yes

1,272" Both Lanes

DO

DO

DO Crate (3x Max)

-610​

662​

1934​

Yes

2,544" If Stacked

DO

DD

55 Inches left

LE Pallet (3x Max)

-706​

-44​

1890​

Yes

32,000 (Max LBS set) Actual Max 34k LBS

LEP (SIDEWAYS)

37 inch (No LEP Left)

18 Inches left

Based on 53'x8.2'x8'

Capacity

636x98.2x96 inches

Row 2

10 DO (None Left) 325 inch

ID

Lane 1

Lane 2

Lane 3

Lane 4

Lane 5

Lane 6

DO

DO

BC|Box Crate

X

X

X

X

DO

DO

CK|Car Kit

X

X

X

X

DO

DO

ED|EDO Crate

DO

DO

DO|DO Crate

DO

DO

311 Inches left

LEP|LE Pallet

ED

ED (No ED Left) 70 Inch

241 Inches left

 

[PuntingJawa]

A little background. I work in shipping and receiving, but take on extra projects with any free time. I work with several folks who aren't exactly computer savvy, and if it's electronic based it, generally speaking, gets thrown at me as I tend to figure it out or know where I can get answers. This is the first excel based workbook that I have created to make my actual job easier.
Joe4 was a huge help and educated me on how to shorten my =IF formulas.

I've worked quite a lot on this with the little free time I've had. It is far from perfect as the crate types are fairly static and vertical space is only managed by the fact that there are limited types of crates I use, how ever it should be rather easy to modify if someone in a shipping capacity could utilize or improve it.

There are two sheets. One for the truck/input of data and another as just data that will change on the truck page based on input of the QTY of crates. I feel this could be cleaned up and am working slowly on a second version. I will repost again when I finish a second version. I am currently trying to make one that takes height and width into consideration and can handle up to 25 crates and 25 pallets of different sizes and weights, so it may take some time.

How it works.
You input the quantity of crates per type, length of the truck being used (In feet), and if applicable the quantity of 1 particular item (This is for weight on an item we ship that is 4 lbs each). This takes data from weight and length categories, and inputs them into cells.
The data sheet then uses all the formulas that I have input into a string.
Near the end of the data it rounds down and places a max quantity of crates into "Rows" as any crate that isn't a whole can't fit on the truck anyway.
Data is then used back on the "Truck" page to show what can fit. If the QTY from G5:G10 isn't less than or equal to F13:F17, then G13:G17 comes back with "Good!" or "Bad" Which in turn says if it's 1 truck loadable based on all the above criteria.

"Truck" page, which is where you input the crate information.

R211 LOAD TEMPLATE.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Truck 1
2
3
4	Type / Max Stack	Length	Width	Height	Weight	QTY	Total LBS	Total Inch		NOTES:
5	Box Crate (1x Max)	42	57	43	850	4	3400	168		CHANGEABLE LE QTY, QTY, TRUCK LENGTH FT.
6	Car Kit (1x Max)	44	44	81	250	0	0	0
7	DO Crate (3x Max)	65	37	25	750	50	37500	3250
8	EDO Crate (3x Max)	70	37	25	450	1	450	70
9	LE Pallet (3x Max)	96	48	16	0	0	0	0		If "Is QTY loadable?" is "Yes!" then single truck usable. If "No!" then mulitple needed.
10	LE QTY (LH+RH)	0				Total LBS	41350	32,000 LBS based
11						Length	53	Length in FT
12	LOAD PLAN	Row 1	Row 2	Row 3	Truck 1	G/B	Is QTY 1 truck loadable?
13	Box Crate (1x Max)	4	X	X	4	Good!	NO!
14	Car Kit (1x Max)	0	X	X	0	Good!				If "Is truck of weight?" is "Yes!" Then good. If "No!" then multi truck load.
15	DO Crate (3x Max)	16	19	0	35	Bad!	Is truck of weight?
16	EDO Crate (3x Max)	0	0	0	0	Bad!	No!
17	LE Pallet (3x Max)	0	0	0	0	Good!
18
19	Packing Slip #'s				Reference			DATE		Capacity is the QTY that can fit on the first truck.
20
Truck

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G19:G23	Cell Value	contains "Bad"	text	NO
G19:G23	Cell Value	contains "Good"	text	NO
H16	Cell Value	="No!"	text	NO
H16	Cell Value	="Yes!"	text	NO
H13	Cell Value	="YES!"	text	NO
H13	Cell Value	="NO!"	text	NO
G13:G17	Cell Value	contains "Bad"	text	NO
G13:G17	Cell Value	contains "Good"	text	NO
H10	Cell Value	<32000	text	NO
H10	Cell Value	>32000	text	NO

"String Data" page where all the calculations happen. I've done up to 3 trucks worth of data, but have not implemented on the "Truck" page as I have ideas for what I want to try with the second version.
If the shipment is not 1 truck viable based on the length that has been input, you can at least trail the data and see where you can place the extra weight and crates. This is still a work in progress.

R211 LOAD TEMPLATE.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2	TRUCK LENGTH	1272								String Data										Truck 1
3	Load plan (Inch Left)	Req Per	QTY	REQ Total	QTY	Req Length	Length Each			Row 1	Row 2	Row 3	Total			Row 1	Row 2	Row 3		Actual Load	Row 1	Row 2	Row 3	Used QTY		Truck 1 weight		LBS Per	Type
4	Box Crate (1x Max)	1104	X	X	4	168	42		Box Crate (1x Max)	4	X	X	4		Box Crate (1x Max)	4	X	X		Box Crate (1x Max)	4	X	X	4		3400		850	Box Crate (1x Max)
5	Car Kit (1x Max)	1104	X	X	0	0	44		Car Kit (1x Max)	0	X	X	0		Car Kit (1x Max)	0	X	X		Car Kit (1x Max)	0	X	X	0		0		250	Car Kit (1x Max)
6	DO Crate (3x Max)	-2146	-874	398	50	3250	65		DO Crate (3x Max)	16.9846154	19.5692308	19.5692308	54		DO Crate (3x Max)	16	19	19		DO Crate (3x Max)	16	19	0	35		26250		750	DO Crate (3x Max)
7	EDO Crate (3x Max)	-2216	-3090	-2692	1	70	70		EDO Crate (3x Max)	0	0	1	1		EDO Crate (3x Max)	0	0	1		EDO Crate (3x Max)	0	0	0	0		0		450	EDO Crate (3x Max)
8	LE Pallet (3x Max)	-2264	-5354	-8046	0	0	96		LE Pallet (3x Max)	0	0	0	0		LE Pallet (3x Max)	0	0	0		LE Pallet (3x Max)	0	0	0	0		0		0	LE Pallet (3x Max)
9																									Total LBS	29650	LBS
10	TRUCK LENGTH	1272								String Data										Truck 2					Remove	0	LBS
11	Load plan (Inch Left)	Req Per	QTY	REQ Total	QTY	Req Length	Length Each			Row 1	Row 2	Row 3	Total			Row 1	Row 2	Row 3		Actual Load	Row 1	Row 2	Row 3	Used QTY		Truck 2 weight
12	Box Crate (1x Max)	1272	X	X	0	0	42		Box Crate (1x Max)	0	X	X	0		Box Crate (1x Max)	0	X	X		Box Crate (1x Max)	0	X	X	0		0
13	Car Kit (1x Max)	1272	X	X	0	0	44		Car Kit (1x Max)	0	X	X	0		Car Kit (1x Max)	0	X	X		Car Kit (1x Max)	0	X	X	0		0
14	DO Crate (3x Max)	297	1272	1272	15	975	65		DO Crate (3x Max)	15	15	15	45		DO Crate (3x Max)	15	15	15		DO Crate (3x Max)	15	0	0	15		6750
15	EDO Crate (3x Max)	227	1272	1272	1	70	70		EDO Crate (3x Max)	1	1	1	3		EDO Crate (3x Max)	1	1	1		EDO Crate (3x Max)	1	0	0	1		0
16	LE Pallet (3x Max)	227	1272	1272	0	0	96		LE Pallet (3x Max)	0	0	0	0		LE Pallet (3x Max)	0	0	0		LE Pallet (3x Max)	0	0	0	0		0
17																									Total LBS	0	LBS
18	TRUCK LENGTH	1272								String Data										Truck 2					Remove	0	LBS
19	Load plan (Inch Left)	Req Per	QTY	REQ Total	QTY	Req Length	Length Each			Row 1	Row 2	Row 3	Total			Row 1	Row 2	Row 3		Actual Load	Row 1	Row 2	Row 3	Used QTY		Truck 3 weight
20	Box Crate (1x Max)	1272	X	X	0	0	42		Box Crate (1x Max)	0	X	X	0		Box Crate (1x Max)	0	X	X		Box Crate (1x Max)	0	X	X	0		0
21	Car Kit (1x Max)	1272	X	X	0	0	44		Car Kit (1x Max)	0	X	X	0		Car Kit (1x Max)	0	X	X		Car Kit (1x Max)	0	X	X	0		0
22	DO Crate (3x Max)	1272	1272	1272	0	0	65		DO Crate (3x Max)	0	0	0	0		DO Crate (3x Max)	0	0	0		DO Crate (3x Max)	0	0	0	0		0
23	EDO Crate (3x Max)	1272	1272	1272	0	0	70		EDO Crate (3x Max)	0	0	0	0		EDO Crate (3x Max)	0	0	0		EDO Crate (3x Max)	0	0	0	0		0
24	LE Pallet (3x Max)	1272	1272	1272	0	0	96		LE Pallet (3x Max)	0	0	0	0		LE Pallet (3x Max)	0	0	0		LE Pallet (3x Max)	0	0	0	0		0
25																									Total LBS	0	LBS
26																									Remove	0	LBS
String Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D22:E24	Cell Value	<0	text	NO
D22:E24	Cell Value	>0	text	NO
C20:C24	Cell Value	<0	text	NO
C20:C24	Cell Value	>0	text	NO
D14:E16	Cell Value	<0	text	NO
D14:E16	Cell Value	>0	text	NO
C12:C16	Cell Value	<0	text	NO
C12:C16	Cell Value	>0	text	NO
D6:E8	Cell Value	<0	text	NO
D6:E8	Cell Value	>0	text	NO
C4:C8	Cell Value	<0	text	NO
C4:C8	Cell Value	>0	text	NO

 

[tmoney2011]

A little background. I work in shipping and receiving, but take on extra projects with any free time.

Modifying this for our own needs, did you ever happen to create that version with multiple trucks?

Thanks!

 

[PuntingJawa]

Modifying this for our own needs, did you ever happen to create that version with multiple trucks?

Thanks!

I have, actually. I've included a BOL generator, Packing slip, Commercial invoice, and USMCA. I still am trying to figure out how to make the BOL expand beyond something like 6 lines, but have been so busy, I have not been able to put in more work. There are some short-comings with it as well that I am still working on. I have set it up to work with 3 tier but it does not calculate height beyond the second row.

I am still working on a stand-alone version but I have the foundation set with what I have learned. I just have to find the time to set one up that isn't specific to my place of employment and remove everything that pertains to them.
====EDIT====
If you like, we can discus what you are looking for in more detail and I can try to modify for you.

 

[tmoney2011]

Awesome, I appreciate your willingness to help!

We are just looking to use it to show truck capacity with a given project. Ideally for us, it would calculate the truck's area, and each item would have it's area calculated to see if the load would fit. It appears the way it works now, it is using length exclusively. I also don't quite understand the Row 1,2,3? We have pallets, door slabs, prehungs, and trim that we are shipping. For instance, 3 pallets may fit side by side, but if that order had trim, the trim is only 3 feet wide by 16 feet long, and can also be stacked 2 high and 2 wide if there are 4 bundles, so it is misrepresented by only using length. We also have multiple trailers, it looks like you started to incorporate more trucks in the iteration you last published, but that would be amazing if it was able to disperse to another truck if it didn't fit. Not sure how the area portion would be accomplished.

 

[PuntingJawa]

I have made a few changes since I posted this as it was confusing and a rough copy to make it work for my needs. Picture the below as if you are looking at a truck from above view. Floor, middle, top. I have this set for a max of 3 stacks. Since we are not allowed to stack on the actual crate it requires load bars. I incorporated this into the calculation for the middle. It pulls from the image to the right. If the height is over a certain amount, it will place a length in the Middle section letting you know that nothing can go above that. I have yet to work this for the top as it has not been necessary.

 

[tmoney2011]

That's very similar to what I envisioned for ours. We have Hardware Pallets (fit 3 wide), slabs (stack 60 high), prehungs (fit 16 width wise), doubles (stack on pallets 3 wide), and trim that is 16X3X4 (oddly shaped and can be stacked 2 high by 2 wide. The length gets me there if I know all these factors beforehand, but It would be sweet to be able to know the exact load like you have there.

 

[PuntingJawa]

I'm going to try and establish a few things that you want.

3x wide
Up to 2 tall
How many trucks?
Provide a list of dimensions of the items that you want. This will help me to practice and fine tune as I play around.

I would have to make several more helper cells but it's not impossible. I couldn't find anything online myself so I've been making improvements as I have time. I plan on buying 365 for my personal computer at home maybe this weekend.

As is, it's still a chaotic mess but I can at least change it about. Also, you can swap the length and width if you plan on turning pallets sideways. I have to do this with one of our items sometimes as it's 90x44.

 

[tmoney2011]

Awesome, sorry for the delayed response, I want to make sure I get all the correct information first. I'll get it to you ASAP.

Thanks!!!