Search:

# Calculating the no. of containers

Asked By: Daryl    Date: Feb 07    Category: MS Office    Views: 1116

I'm new here and have an excel question really
need help.

My role is responsible in calculating the no. of container used to
deliver goods every week. Large batch of different products with
different volumn & weight have to be distributed to containers.
There's max vol. & weight limit of different size of containers.

For example, there are 3 products w/ different qty, size & weight as
below.
Product Qty Size Weight
A 100 25 14
B 150 30 20
C 50 10 5

And there're large & small containers:
Large - max size = 60, max weight = 25
Small - max size = 30, max weight = 20

Is there any macro or formula that can automatically calculate
1. The qty of each product in each container?
2. The no. of container used (both large & small)?

Share:

I assume that cost is the chief issue here.
i.e. "What is the least cost to pack everything?"
This involves box cost, labor and shipping.
If this is your goal, there's not enough data here.
Otherwise, maybe your question  might be "What is the least amount of
containers?" (mix unimportant)
or similar ...
If you solve this, I'd be interested in the solution via macro or formula.
For only 3 products, it's not too bad, but I assume you want to
generalize to "n" products.

You need Solver.

From Excel tool bar, choose Tools-Add Ins - Solver Add-in. It is a little
complicated to explain in email on how to set up the Solver. But your problem is
a classic one. It should not be difficult to find examples on the internet.

Thank you very much for introducing Excel Solver to me. Will try to
explore it first.

Thank you very much for your advice. There're only 2 criteria in the
calculation: volumn & weight. It's because the factory will bear
other costs themselves.

You're right that the no. of product  is extended to "n". Sometimes
it's needed to do a forecast for the whole month. The no. of product
may be increased to thousands and an additional constrain will be the
goods finishing date.

Didn't find what you were looking for? Find more on Calculating the no. of containers Or get search suggestion and latest updates.