Argthtjtr

So, I been thinking how to program this...

There is product A, has two different pallet quantities (Qty 8 & qty 10).
When customer orders (ex: 72 qty of product A), I have to manually divide it as 4 pallets (10 qty/ea) and 4 pallets (8 qty/ea).

I want to make a chart on excel such that, whenever I put a ordered qty, excel will automatically calculate how many pallets it will be and how the qty on each pallet will be.

Anybody know how to do this?

It's pure mathematics actually:

10x + 8y = z where z is your total desired qty

If you want to know whether you are going to have full pallets or not, I suggest searching the greatest common factor of the above equation, which is 2 in this case.

Resulting: if z/2 = integer and it is positive then you can use full pallets.

If you want to know how much pallets to use from each you have to do follwing:

increment y by 1 as long as you don't have a positive integer for x so:

y = y + 1

resulting following equation for x:

x = (z-8y)/10 where z is your total amount and y is the auto incremented number

combined with the y quotation from above:

for (y = 0; x = positive and integer; y = y + 1) { 

    x = (z-8y)/10;

    print x;

    print y;

}

// don't start this loop if z/2 isn't integer and positive

on the other hand when you want to open pallets randomly: example

10x + 8y = 72

divide your amount through the highest amount per pallet and take divide the remainder from the smallest amount per pallet

72 = 10x => x= 7.2

the integer number here is 7, so take 7 pallets of 10 and take 2 units of a pallet from 8 units.

If the remainder of the quotient >= 8 than you can take at least 1 full pallet of 8.

Implementing this in Excel will cost you a lot of programming and like suggested in the comments, you would better search after the "Solver Excel" add on, which I also haven't used yet.

Try this. Assumptions: 1) you want to ship the fewest pallets. 2) You want to ship the smallest quantity >= to the requested amount.

First, jumping ahead to my final result:

   A   B    C

1      10   8

2 72   7    1

The requested quantity, 72 is in A2, and the pallet sizes in B1 and C1.
B2 contains: =FLOOR(A2/B1,1) (the most whole large pallets without going over request)
C2 contains: =CEILING(MOD(A2,B1)/C1,1) (small pallets needed to fill the remainder, rounding up to a whole pallet)

With some slightly nontrivial hackery, this can be extended to more than two pallet size options.