Some Calculation if some condition matched - Any Formula

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

we have the following sheets

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
1Head1Head2Head3Head4Head5Head6Head7Head8Head9Head10Head11Head12Head13Head14Head15Head16Head17Head18Head19Head20
2ABCD 156247530733.1Cutting1961415011100526541 Cut530.00 Kg6883.5516.90.01067548423
3ABCD 22270103754923.23.7MakingNSDB1722368712915625632 Cut549.80 Kg5496.5540.10.001371860.1178964581
4ABCD 3227016375221.23.43.3MakingNLBD1722394013515625633 Cut522.80 Kg5496.5
5ABCD 42267613754523.70MakingNABD1721494313115625634 Cut545.80 Kg5496.5
6BDCF 15644753411.333Cutting198168012300526545 Cut534.00 Kg6883.5
7GFDS122685537569.84.23.53.1MakingNSDB17226001012615625636 Cut569.80 Kg5496.5
Sheet1
Cell Formulas
RangeFormula
U2:U3U2=D2-E2-F2-G2
V2V2=T2*C2*S2/50000/500
W2W2=U2/V2
V3V3=28*18*S2/50000/500
W3W3=J3/2
X3X3=W3*V3
Y3Y3=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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,363
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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) ):
Excel Formula:
=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

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,363
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Then Use this:
Excel Formula:
=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

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Thanks maabadi

Find the solution
 

Watch MrExcel Video

Forum statistics

Threads
1,123,517
Messages
5,602,113
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top