Some Calculation if some condition matched - Any Formula

[Vishaal]

Hi All,

we have the following sheets

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Head1	Head2	Head3	Head4	Head5	Head6	Head7	Head8	Head9	Head10	Head11	Head12	Head13	Head14	Head15	Head16	Head17	Head18	Head19	Head20
2	ABCD 1	562	47	530	7	3	3.1	Cutting					19614	150		1110052654	1 Cut	530.00 Kg	68	83.5	516.9	0.010675	48423
3	ABCD 2	227010	37	549	2	3.2	3.7	Making	NSDB	172	2368	7				1291562563	2 Cut	549.80 Kg	54	96.5	540.1	0.001371	86	0.117896	4581
4	ABCD 3	227016	37	522	1.2	3.4	3.3	Making	NLBD	172	2394	0				1351562563	3 Cut	522.80 Kg	54	96.5
5	ABCD 4	226761	37	545	2	3.7	0	Making	NABD	172	1494	3				1311562563	4 Cut	545.80 Kg	54	96.5
6	BDCF 1	564	47	534	11.3	3	3	Cutting					19816	80		1230052654	5 Cut	534.00 Kg	68	83.5
7	GFDS1	226855	37	569.8	4.2	3.5	3.1	Making	NSDB	172	2600	10				1261562563	6 Cut	569.80 Kg	54	96.5
Sheet1

Cell Formulas
Range	Formula
U2:U3	U2	=D2-E2-F2-G2
V2	V2	=T2*C2*S2/50000/500
W2	W2	=U2/V2
V3	V3	=28*18*S2/50000/500
W3	W3	=J3/2
X3	X3	=W3*V3
Y3	Y3	=U3/X3

we are using the following steps
If H2="Cutting" and I2="" then (=D2-E2-F2-G2) will divide by (=T2*C2*S2/50000) / 500
If H2="Making" and I2="NSDB" then (=D2-E2-F2-G2) will divide by (=28*18*S2/50000) / 500 *J2/2
If H2="Making" and I2="NLBD" then (=D2-E2-F2-G2) will divide by (=38*18*S2/50000) / 500 *J2/2
If H2="Making" and I2="NABD" then (=D2-E2-F2-G2) will divide by (=48*18*S2/50000) / 500 *J2/2

We have done a calculation in U2, V2, W2
& U3, V3, W3, X3, Y3

HELP PLS

 

[maabadi]

I think this is worked without filling columns U & V ( I think you want to J2/2 multiply at 500 only not total of ((28*C2*S2/50000)/500) ):

=IF(AND(H2="Cutting",I2=""),(D2-(E2+F2+G2))/((T2*C2*S2/50000)/500),IF(AND(H2="Making",I2="NSDB"),(D2-(E2+F2+G2))/((28*18*S2/50000)/(500*J2/2)),IF(AND(H2="Making",I2="NLBD"),(D2-(E2+F2+G2))/((38*18*S2/50000)/(500*J2/2)),IF(AND(H2="Making",I2="NABD"),(D2-(E2+F2+G2))/((48*18*S2/50000)/(500*J2/2)),""))))

 

[Vishaal]

Thanks for ur reply maabadi

( I think you want to J2/2 multiply at 500 only not total of ((28*C2*S2/50000)/500) ):

No, J2/2 will multiply ((28*C2*S2/50000)/500) )

 

[maabadi]

Then Use this:

=IF(AND(H2="Cutting",I2=""),(D2-(E2+F2+G2))/((T2*C2*S2/50000)/500),IF(AND(H2="Making",I2="NSDB"),(D2-(E2+F2+G2))/((28*18*S2/50000)/500)*(J2/2),IF(AND(H2="Making",I2="NLBD"),(D2-(E2+F2+G2))/((38*18*S2/50000)/500)*(J2/2),IF(AND(H2="Making",I2="NABD"),(D2-(E2+F2+G2))/((48*18*S2/50000)/500)*(J2/2),""))))

 

[Vishaal]

Thanks maabadi

Find the solution