Excel formula with 03 sub qty

[Kalpesh123]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]SR.
[/TD]
[TD]PRODUCT
[/TD]
[TD]BAG
[/TD]
[TD]PKT
[/TD]
[TD]STRIP
[/TD]
[TD]PCS
[/TD]
[TD]QTY1
[/TD]
[TD]QTY2
[/TD]
[TD]QTY3
[/TD]
[TD]QTY4
[/TD]
[TD]QTY5
[/TD]
[TD]TOTAL QTY
[/TD]
[TD]BAG PCS
[/TD]
[TD]PKT IN BAG
[/TD]
[TD]STRIP
IN PKT
[/TD]
[TD]PCS IN STRIP
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]MD
[/TD]
[TD][/TD]
[TD]19 PKT
[/TD]
[TD]3 STRIP
[/TD]
[TD]8 PCS
[/TD]
[TD]380[/TD]
[TD]240
[/TD]
[TD]150
[/TD]
[TD]20
[/TD]
[TD]8
[/TD]
[TD]798[/TD]
[TD]960
[/TD]
[TD]24
[/TD]
[TD]4
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]FR
[/TD]
[TD]2 BAG
[/TD]
[TD]5 PKT
[/TD]
[TD][/TD]
[TD]9 PCS
[/TD]
[TD]180[/TD]
[TD]300
[/TD]
[TD]750
[/TD]
[TD]421
[/TD]
[TD]108
[/TD]
[TD]1759
[/TD]
[TD]750
[/TD]
[TD]15
[/TD]
[TD]5
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]SB[/TD]
[TD][/TD]
[TD]11 PKT
[/TD]
[TD]2 STRIP
[/TD]
[TD][/TD]
[TD]250
[/TD]
[TD]210
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]460
[/TD]
[TD]600
[/TD]
[TD]15
[/TD]
[TD]4
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want formula for C,D,E,F

 

[James006]

Hello,

Could you please explicit your expected results ... in which cells ...

 

[Kalpesh123]

required result for cell :- c,d,e,f

 

[FormR]

required result for cell :- c,d,e,f

Hi, I think you were probably being asked to be a bit more descriptive than that, but here is an option you can try.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	SR.	PRODUCT	BAG	PKT	STRIP	PCS	QTY1	QTY2	QTY3	QTY4	QTY5	TOTAL QTY	BAG PCS	PKT IN BAG	STRIP IN PKT	PCS IN STRIP
2	1	MD	0	19	3	8	380	240	150	20	8	798	960	24	4	10
3	2	FR	2	5	0	9	180	300	750	421	108	1759	750	15	5	10
4	3	SB	0	11	2	0	250	210				460	600	15	4	10
Sheet1

Cell Formulas
Range	Formula
C2	=INT(L2/M2)
D2	=INT(MOD(L2,M2)/(O2*P2))
E2	=INT(INT(MOD(INT(MOD(L2,M2)),O2*P2))/P2)
F2	=MOD(INT(MOD(INT(MOD(L2,M2)),O2*P2)),P2)

 

[Kalpesh123]

Hello sir
thanks

but there is possible where is result is is zero there show blank

 

[FormR]

but there is possible where is result is is zero there show blank

Hi, if it's only needed for display purposes you could simply format the cells that contain the formulas with a custom format of:

0;;

 

[Kalpesh123]

Dear Sir

i didn't understand

I dont know formula for cell C

I USED FORMULA IN CELL D,E,F

D=TRIM(IF(L4>=N4,INT(L4/N4)&" PKT ",""))
E=IF(MOD(L4,N4)>=P4,INT(MOD(L4,N4)/P4)& " STRIP ","")
F=IF(MOD(L4,P4),INT(MOD(L4,P4))&" Pcs","")

please guide me formula for c

 

[FormR]

Hi, you can use the formulas in post #4 and use custom formatting to change what is displayed.

For example -
- Select column C with your mouse and press CTRL+1 to open the format cells dialogue.
- On the "Number" tab choose "Custom" from the "Category" list - in the "Type:" text box enter below exactly as it is here.

0 "bag";;

- Press OK

Repeat for the other columns altering "bag" for the other suffixes.

 

[Kalpesh123]

thanks

 

[Kalpesh123]

Dear Sir i want formula for cell C
i have formula only 3 cell wich is D,E,F
i dont know formula 4 cell only 3 cell i have formula
so i want formula for 1 cell more