# How to build spreadsheet that will tell me how to pack shipment

Copper Contributor

# How to build spreadsheet that will tell me how to pack shipment

How can I build a spreadsheet that will tell me how to pack custom boxes with different items in them? I work at a company that makes kitchenware and I make shipping labels for customers. I want to be able to enter in the quantity of each item and excel tell me what size box all of the items will fit in. I have all of the data- just not sure how to automate it so I'm not reading it on a piece of paper trying to find the correct box size. I know this is possible, just not sure how to get started.

2 Replies

# Re: How to build spreadsheet that will tell me how to pack shipment

@MaryRothwell have all of the data- just not sure how to automate it so I'm not reading it on a piece of paper trying to find the correct box size

if possible,show your source data and expected result.

# Re: How to build spreadsheet that will tell me how to pack shipment

Building a spreadsheet that automates packing recommendations for custom boxes requires a combination of data organization, formulas, and potentially even conditional formatting. Here's a breakdown of how you can achieve this:

1. Data Setup:

• Item Data: Create a table listing all the kitchenware items you offer, including:
• Item Name: Descriptive name of the item (e.g., "Stainless Steel Mixing Bowl").
• Dimensions (L x W x H): Length, Width, and Height of the item in consistent units (e.g., centimeters, inches).
• Weight: Weight of the individual item (optional, but useful for weight-based packing decisions).
• Box Data: Create a separate table listing all the available box sizes you use, including:
• Box Name: Descriptive name or identifier for the box (e.g., "Small Box", "Medium Box").
• Dimensions (L x W x H): Length, Width, and Height of the box in the same units as the items.
• Max Weight: Maximum weight capacity of the box (optional, but useful for weight-based packing decisions).

2. Packing Logic:

• Volume Calculation: In your Item Data table, create a new column named "Volume" using the formula =L2*W2*H2 (replace L2, W2, H2 with the corresponding dimensions cells) to calculate the volume of each item.

• Packing Algorithm: In your Item Data table, create another column named "Recommended Box" where you'll use a formula to determine the best fitting box for each item. Here are two options:

Option 1: Simple Volume Comparison: This approach prioritizes fitting items into the smallest box possible.

Excel
```=IF(AND(L2 <= E4, W2 <= F4, H2 <= G4), E3,
IF(AND(L2 <= E5, W2 <= F5, H2 <= G5), E5, "...")
)```

Replace L2, W2, H2 with the dimensions of the current item, E3 with the name of the first box (from Box Data), and E4, F4, G4 with corresponding dimensions of that box. Repeat the IF statement for each box size, adding additional conditions and box names accordingly.

This formula checks if the item's dimensions are smaller than or equal to the corresponding dimensions of each box, and recommends the first fitting box.

Option 2: Volume Efficiency: This approach prioritizes minimizing wasted space by aiming for boxes with volumes closest to the item's volume.

This option involves more complex formulas and potentially iterative calculations. You can find resources online or consult an Excel expert for guidance on implementing this approach.