Excel solver to select optimal box sizes

Question

I'm hoping someone can help me possibly use Excel solver to solve this or give me a direction in how to go about it. I'm trying to figure out what are the best box sizes to purchase in order to suit our warehouse. I've analyzed all of my products and come up with 4 ranges for avg. quantity/order, volume/product, and weight/product. Does anyone know of a way to set this up in solver?? We ship roughly 27k units a day with 74% of orders have 11 units or more. Warehouse is 189,000 square feet. Overall weight can't exceed 50 lbs.

This table breaks out the product sizes by volume ranges. Ex. Group 1 products have a volume of 5in cubed or less, group 2 products have a box volume between 6-36 in cubed, etc.

Volume Group 1 - 5 in3 Group 2 - 6 in3 - 36 in3 Group 3 - 37 in3 - 96 in3 Group 4 - 97 in3 - 200 in3

This table breaks out product types by average weight ranges. So group 1 consists of products that have an average weight up to 0.25lbs. Group 2 has product weights between 0.25 lbs and 0.50 lbs.

Weight Group 1 - 0.25 lbs Group 2 - 0.25 - 0.50 lbs Group 3 - 0.50 - 1.00 lbs Group 4 - 1.00 - 3.00 lbs

This is essentially the daily order volumes split out by units/order. So 73.74% of orders shipped per day are over 11 units/order.

Quantities/Order Avg. Ranges % weight Group 1 - 1 unit/order 3.12% of total orders Group 2 - 2-5 units/order 11.27% of total orders Group 3 - 6-10 units/order 11.88% of total orders Group 4 - 11-20 units/order 73.74% of total orders