Round up an amount to nearest pallet and/or box quantity

[stillsteam]

Hi

Need help finding out how many pallets and/or boxes an amount is and round up to the nearest pallet and/or box

Example:

Amount PalletQuant BoxQuant pallets boxes New amount
1100 1000 250 1 1 1250
900 1000 250 1 0 1000
700 1000 250 0 3 750
1300 400 100 3 1 1300

The first 3 columns is known to us but I need to calculate the last 3
I use Excel 2010
Hope someone can help me.


/Jonas

 

[SkipVought]

Hi,

What is the logic for each of your 3 calculated columns.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[stillsteam]

Hi Skip

Our customer orders an amount but we don´t split any boxes.

For every amount we need to calculate how many pallets we need to send and if the amount is more than any number of full pallets we need to round up to the nearest box multipel.
As in the first example they order 1100pcs but we will send them 1 pallet and 1 box and it sums up to 1250 pcs.

In my example with 900 we need to send 1 pallet not 4 boxes even if that is the same amount.
Our customer need this information before we send them the goods.

Thanks

/Jonas

 

[SkipVought]

In my example with 900 we need to send 1 pallet not 4 boxes even if that is the same amount.

That's not good enough! What is the LOGIC for determine when to do that?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[stillsteam]

Hi

I will try to explain it better.
For every item we have a full pallet amount and on every pallet there is a number of boxes, in my first example we have 4 boxes with 250 pcs in each adding up to 1000 pcs on the pallet.

If they order 1000 pcs they will get a full pallet and if the order 900psc they will also get a full pallet with 1000 pcs because 3 boxes is 750 pcs and we can't send less than they ordered, so we have to send them a full pallet.

If they order 1900pcs we will send them 2 full pallets not 1 pallet and 4 boxes.
I know it is strange but that is how they want it.
They need to know how many pallet and boxes we will send and of course the total amount in pieces.

Hope you understand what I'm looking for.
The tricky part is to change from 4 boxes to 1 pallet.

Thanks

/Jonas

 

[SkipVought]

in row 2...
[tt]

pallets: =INT(($A2+$C2-1)/$B2)
boxes: =INT(($A2-($B2*D2)+$C2-1)/$C2)

[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]