0

In Excel, I want to set up a Stock Picking sheet.

I have a column for items per case, and I have have a total order quantity from several different orders to go in a shipment.

So, say I have combined orders of 10,000 and a case quantity of 48.
10,000 divided by 48 gives me 208.333333 in Excel.

How can I express that as 208 Full cases and 16 units?

And the next products down the list could have different "per case" quantities (24, 12, 36, 9 etc.) so I need the formula to suit this variety - not just be set for one case quantity.

I also have a further need, in another location, to break down from a "Cases per Pallet" which is a similar thing. (I have a column for those figures.)

Ultimately in this location I need my 10,000 to be expressed in x full pallets, plus y extra full cases, plus z extra units.

0

Use modulo operation

To get the integer division result use QUOTIENT. For example

=QUOTIENT(10000, 48)

To get the remainder use MOD. For example

=MOD(10000, 48)

To divide by multiple units you need to get the new divisor after each step. The easiest way is creating separate columns for each different thing, then just hide the columns you don't need

╔═╦═══════════╤══════════╤════════╤════════════════════╤════════════╤═════════════════╤════════════╗
║ ║     A     │    B     │   C    │         D          │     E      │        F        │     G      ║
╠═╬═══════════╪══════════╪════════╪════════════════════╪════════════╪═════════════════╪════════════╣
║1║total units│  cases   │ units  │    full pallets    │   units    │   full cases    │   units    ║
║ ║           │per pallet│per case│                    │ remaining  │                 │ remaining  ║
╟─╫───────────┼──────────┼────────┼────────────────────┼────────────┼─────────────────┼────────────╢
║2║   10000   │    36    │   48   │=QUOTIENT(A2, B2*C2)│=A2-D2*C2*B2│=QUOTIENT(E2, C2)│=MOD(E2, C2)║
╟─╫───────────┼──────────┼────────┼────────────────────┼────────────┼─────────────────┼────────────╢
║3║   10000   │    36    │   48   │         5          │    1360    │       28        │     16     ║
╚═╩═══════════╧══════════╧════════╧════════════════════╧════════════╧═════════════════╧════════════╝

After all you have the needed quantities and now you can combine them into one sentence easily.

=D2 & " full pallets, plus " & F2 & " extra full cases, plus " & G2 " extra units."

If you can't create hidden columns then the following formula can tell all things in one line, but as you can see now it's very unreadable and inefficient because many things will have to be calculated again and again

=QUOTIENT(A2, B2*C2) & " full pallets, plus " &
 QUOTIENT(A2-QUOTIENT(A2, B2*C2)*C2*B2, C2) & " extra full cases, plus " &
 MOD(A2-QUOTIENT(A2, B2*C2)*C2*B2, C2) & " extra units"
  • Thanks! That is useful for a two-step process, such as Full Cases and Units. But I am finding it hard to make it work for Full Pallets plus Full extra Cases, plus extra Units. Example: I expect 10,000 units to be delivered The known quantities are 48 units per case, and 36 cases per pallet. I want to be able to check the stock from a list which tells me this answer - like this: FULL PALLETS 5 Plus FULL CASES 28 Plus Units 16 ( Where Full pallets are 5 @ (36x48) = 5 @ (1728) = 8640 Full cases are 28 @ 48 = 1344 Units are 16 ) – Paul C Nov 20 '16 at 4:51
  • @PaulC please see my edit – phuclv Nov 20 '16 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.