Excel formula with 03 sub qty

[Kalpesh123]

1 BOX = 600 PCS
1 BOX = 15 PKT
1 PKT = 4 STRIP
1 PKT = 40 PCS

i.e.
A=290 PCS
B=100 PCS
C=350 PCS
D=132 PCS
E= 460 PCS
F= (A+B+C+D+E) = 1332 PCS

NOW I WANT CONVORT THIS QTY IN BOX/PKT/STRIP/PCS
i.e
G= BOX
H= PKT
I= STRIP
J= PCS

I WISH TO GET RESULT LIKE BELLOW
G=2BOX
H=3 PKT
I=1 STRIP
J=3 PCS

 

[Yongle]

Try this

With total in cell F2

Formula in G2
=INT(F2/600)

Formula in H2
=INT((F2-G2*600)/40)

Formula in I2
=(MOD(F2,40)-J2)/10

Formula in J2
=MOD($F2,10)

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Pcs​	Pcs​	Pcs​	Pcs​	Pcs​	Total​	Box​	Pkt​	Strip​	Pieces​
2​	290​	100​	350​	132​	460​	1332​	2​	3​	1​	2​

Sheet: Sheet1

 

[Kalpesh123]

Thank you very much its work

 

[Yongle]

Thanks for your feedback

 

[Kalpesh123]

Dear Sir
i need some more help

PLEASE GIVE ME YOUR EMAIL ID THEN DIRECTLY CONTACT YOU AND SEND EXCEL SHEET TO BETTER UNDERSTAND

 

[Yongle]

My email address is private

Post#2 has the solution to your question
Do you want to ask a different question?

 

[Kalpesh123]

hello sir

i want know if i want result like this i.e. qty 2 then result saw like 2 box

and where is zero there show blank

=TRIM(IF(L5>=N5,INT(L5/N5)&" Box ","")) i.e 2 box
=IF(MOD(L5,N5)>=Q5,INT(MOD(L5,N5)/Q5)& " strip ","") i.e 3 Strip
=IF(MOD(L5,Q5),INT(MOD(L5,Q5))&" Pcs","") i.e. 5 Pcs

L= my total qty in pcs

 

[mole999]

Dear Sir
i need some more help

PLEASE GIVE ME YOUR EMAIL ID THEN DIRECTLY CONTACT YOU AND SEND EXCEL SHEET TO BETTER UNDERSTAND

Please re-read (you agreed to them when you joined) and comply with site rules. We would not condone email sharing in a public forum, which could be used by spammers. We also encourage all users to share their problem and result on this forum for others to learn from