Pallet Calculation Problem

I have been trying to figure out how to set up spreadsheet where I can fill in the item number in column A, the order quantity (in pieces) in column B, and have Excel calculate the number of pallets ordered in column C and the number of cartons ordered in column D.

For instance, if a full pallet of an item is 160 pieces, and I receive an order for 160 pieces, column C would return a value of 1 and column D would return a value of 0 (since I received an order for 1 full pallet and no extra cartons).

The other case would be if I received an order for 180 pieces ( If a full pallet is 160 pieces and each carton contains 10 pieces) then column C would return a value of 1 and column D would return a value of 2 (1 full pallet plus 2 extra cartons).

The challenge is we have many different items with different pallet and carton quantities and we have more than one item on each order. I do have an excel spreadsheet with each item number, the pallet quantity of each item and the carton quantity of each item.

Anyone have an idea as to how I can accomplish this task? Any suggestions would be appreciated.

Hi duvall73
A good place to start
http://www.ozgrid.com/forum/showthread.php?t=81927
you will need to set up some tables of the pallet items