Searching for the biggest packsize

[sebasat]

Hi,

I need some help to find the way of converting the quantity of the material into pack sizes.

So, in the warehouse you have different pack sizes for different products. Let's say our product XYZ has got 3 different pack sizes we can sell to the customer: 1 each (EA), 1 pack of 10 (PAC) and 1 box of 50 (BOX). And let's say the customer ordered the quantity 132. I need Excel to convert 132 into how many packs we need to process in the warehouse to prepare customer's order.

To pick 132 of XYZ I'd have to get 2 boxes of 50, then 3 packs of 10 and then 2 single each's so IN TOTAL I'D NEED TO PROCESS 7 PACKS (2x50, 3x10, 2x1).

So I need a formula to convert the quantity into pack sizes, could any help with that ? Thanks

[oeldere]

I find it hard to explain, so see the formula in the attached sheet.

The result is in the green cells.

[jason.b75]

To do it with 1 formula.

=INT(132/50)&" x BOX, "&INT(MOD(132,50)/10)&" x PAC, "&MOD(132,10)&" x EA"

I've used your example quantities, 132, 50 and 10 can be substituted with cell references as needed.