Rounding Question

[AFGM]

I need a formula that will adjust two numbers for shipping quantities.

Cell 1: The first formula needs to make sure the number is divisible by the quantity allowed to be shipped per palled (i.e 10 so the number would need to be divisible by 10, if the number we need to order is 29, it rounds up to 30) - Have this formula

Cell 2: The second formula is the number of pallets that can be shipped per container (i.e 8 pallets fit in one container, so if the first formula returns a number of 9, it would need to be round down to 8 or if the number is 15 it needs to be round up to 16), need this formula to adjust the formula in cell 1

Any idea how to structure this?

 

[Guitarde]

Hi,

CELL 1:
If you have 29 in A1, enter in cell B1
=CEILING(A1,10)
this will give your 30

CELL 2:
I understand that you want to bring it to multiples of 8 pallets
in you example it is not clear what you want if number is 14
at what number do you round down vs round up ?


Regards
Eric

 

[AFGM]

Hi,

CELL 1:
If you have 29 in A1, enter in cell B1
=CEILING(A1,10)
this will give your 30

CELL 2:
I understand that you want to bring it to multiples of 8 pallets
in you example it is not clear what you want if number is 14
at what number do you round down vs round up ?


Regards
Eric

can you set a range? If its within 4 (i.e 12) round down, if its over 4 round up?

 

[Guitarde]

if your pallet number is in A2
=ROUND((A2-1)/8,0)*8

 

[Bosquedeguate]

Your rounding formula will be based upon the trigger point of how many pallets over 8 will cause another container to be loaded?
Once that number is known the rounding formula is easy to figure out.
1 - pallet (Yes/No) - container
2 - pallets
3 - pallets
...
8 - pallets?

 

[Phuoc]

Try this:

Book1
	A	B	C
1	quantity	pallet	container
2		10	8
3	29	3	0
4	90	9	1
5	130	13	2
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=ROUNDUP(A3/$B$2,0)
C3:C5	C3	=INT(B3/$C$2)+(MOD(B3,$C$2)>4)