Pallets, part pallets and cartons calculator

[Martin team]

What I need to work out is how many pallets & part pallets and cartons required based on amount of items.

For example if a pallet holds 90 items & a carton holds 10 items then 90 items(9 cartons) would equate to 1 pallet, 210 items would be 2 full pallets , 1 part pallet with 30 items on it... with is 3 cartons on the part pallet.

 

[HenkDePoema]

This is not as complicated as you might think. Before I help you with some code: why don't you try to write down (in human readable language) which steps you would take yourself when you have a number of items and want to know how much of each kind of packaging is required. These are the steps you'll have to automate! (And I'm happy to help you with that.)

Also think of the format in which you want to have the result returned after entering an amount of items in your formula or function. Are there any cells to be filled with the different number of cartons and pallets, or do you want a message box to show the result? etc. And: where is the amount of items entered? Is it the value of a specific cell on a worksheet, or do you want to enter it in an input box?

 

[JamesCanale]

MrExcelPlayground9-1.xlsx
	A	B	C	D
1	Items	Pallets	Cartons	Loosies
2	210	2	3	0
Sheet15

Cell Formulas
Range	Formula
B2	B2	=INT(A2/90)
C2	C2	=INT(MOD(A2,90)/10)
D2	D2	=MOD(A2,10)

 

[HenkDePoema]

MrExcelPlayground9-1.xlsx
	A	B	C	D
1	Items	Pallets	Cartons	Loosies
2	210	2	3	0
Sheet15

Cell Formulas
Range	Formula
B2	B2	=INT(A2/90)
C2	C2	=INT(MOD(A2,90)/10)
D2	D2	=MOD(A2,10)

That is neat, but now @Martin team doesn't need his own brain anymore

 

[Martin team]

Thank You very much guy's I couldn't put picture before... just now let me... I need something like this on the picture...

 

[Martin team]

That is neat, but now @Martin team doesn't need his own brain anymore

I didn't sleep till 3:30am last night... trying to figure it out... And what with part pallet if may ask?

 

[JamesCanale]

MrExcelPlayground9-1.xlsx
	A	B	C	D	E	F	G	H	I
7	Qty expected	qty received	pallet qty	full pallets	part pallets	no of ctns on full pallet	carton qty	ctns on part pallet	loosies
8	210		90	2	1	9	10	3	0
9	520		90	5	1	9	10	7	0
10	200		90	2	1	9	10	2	0
11	550		50	6	0	5	10	1	0
12	270		50	3	1	5	10	0	0
Sheet15

Cell Formulas
Range	Formula
D8:D12	D8	=INT(A8/90)
E8:E12	E8	=--(MOD(A8,C8)>0)
F8:F12	F8	=C8/G8
H8:H12	H8	=INT(MOD(A8,90)/10)
I8:I12	I8	=MOD(A8,10)

 

[Martin team]

MrExcelPlayground9-1.xlsx
	A	B	C	D	E	F	G	H	I
7	Qty expected	qty received	pallet qty	full pallets	part pallets	no of ctns on full pallet	carton qty	ctns on part pallet	loosies
8	210		90	2	1	9	10	3	0
9	520		90	5	1	9	10	7	0
10	200		90	2	1	9	10	2	0
11	550		50	6	0	5	10	1	0
12	270		50	3	1	5	10	0	0
Sheet15

Cell Formulas
Range	Formula
D8:D12	D8	=INT(A8/90)
E8:E12	E8	=--(MOD(A8,C8)>0)
F8:F12	F8	=C8/G8
H8:H12	H8	=INT(MOD(A8,90)/10)
I8:I12	I8	=MOD(A8,10)

Thank You very much pal

 

[Martin team]

I will try this when I get back to the house. I will check with other items. If everything will be ok and I no need more help I will mark as solution... and for now thanks for all answers. It help me a lot. I appreciate it

 

[HenkDePoema]

MrExcelPlayground9-1.xlsx
	A	B	C	D	E	F	G	H	I
7	Qty expected	qty received	pallet qty	full pallets	part pallets	no of ctns on full pallet	carton qty	ctns on part pallet	loosies
8	210		90	2	1	9	10	3	0
9	520		90	5	1	9	10	7	0
10	200		90	2	1	9	10	2	0
11	550		50	6	0	5	10	1	0
12	270		50	3	1	5	10	0	0
Sheet15

Cell Formulas
Range	Formula
D8:D12	D8	=INT(A8/90)
E8:E12	E8	=--(MOD(A8,C8)>0)
F8:F12	F8	=C8/G8
H8:H12	H8	=INT(MOD(A8,90)/10)
I8:I12	I8	=MOD(A8,10)

Nicely done! The formula in D8 should be "=INT(A8/C8)", considering the different quantities in column C. That's the only addition I can think of!