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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

Pcs​

[/td][td]

Pcs​

[/td][td]

Pcs​

[/td][td]

Pcs​

[/td][td]

Pcs​

[/td][td]

Total​

[/td][td]

Box​

[/td][td]

Pkt​

[/td][td]

Strip​

[/td][td]

Pieces​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

290​

[/td][td]

100​

[/td][td]

350​

[/td][td]

132​

[/td][td]

460​

[/td][td]

1332​

[/td][td]

2​

[/td][td]

3​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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