Simplyfying Nested IFs, Index and Match Can I improve my current workbook ?

V

Vince1199s

New Member
Joined
Mar 4, 2016
Messages
22
Recently I was asking about nested IF's on here and thanks to some great guys they helped me use IF with index and Match.
So now my Excel Sheet is complete but I am wondering whether it can be improved on ?
Here is the table where based on information entered in Cell B,C,D,G6 or B,C,D,G7 or B,C,D,G8 I get answers in E,F,H & I6 or E,F,H & I7 or E,F,H & I8
and sometimes I need to use all 3 cells

Excel 2010
BCDEFGHI
5DiameterSDR RatingPressure TierCross Sectional Area by SDR rating = p * Nominal I/DVolume m V = pdL/4 (IGE/TD/3)Length MtrsTest Period = Volume x Specific Gas Constant Test Time Length * Z Factor
663mmSDR11LP = 75mbar0.00210.2087100.0 mtrs00:03:4500:03:45
763mmSDR11MP = 2bar0.00210.2087100.0 mtrs00:11:0100:11:16
863mmSDR11IP = 7bar0.00210.2087100.0 mtrs00:25:4300:23:10
Calculator
Cell Formulas
RangeFormula
E6=INDEX(Table!H3:L35,MATCH(B6,Table!A3:A35,0),MATCH(C6,Table!H2:L2,0))
E7=INDEX(Table!H3:L35,MATCH(B7,Table!A3:A35,0),MATCH(C7,Table!H2:L2,0))
E8=INDEX(Table!H3:L35,MATCH(B8,Table!A3:A35,0),MATCH(C8,Table!H2:L2,0))
F6=INDEX(Table!P3:T35,MATCH(B6,Table!A3:A35,0),MATCH(C6,Table!P2:T2,0))
F7=INDEX(Table!P3:T35,MATCH(B7,Table!A3:A35,0),MATCH(C7,Table!H2:L2,0))
F8=INDEX(Table!P3:T35,MATCH(B8,Table!A3:A35,0),MATCH(C8,Table!H2:L2,0))
H6=IF(LEFT(D6,2)="LP",INDEX(Table!U3:Y35,MATCH(B6,Table!A3:A35,0),MATCH("*"&C6&"*",Table!U2:Y2,0)),IF(LEFT(D6,2)="MP",INDEX(Table!Z3:AD35,MATCH(B6,Table!A3:A35,0),MATCH("*"&C6&"*",Table!Z2:AD2,0)),IF(LEFT(D6,2)="IP",INDEX(Table!AG3:AG35,MATCH(B6,Table!A3:A35,0),MATCH("*"&C6&"*",Table!AG2:AG2,0)))))
H7=IF(LEFT(D7,2)="LP",INDEX(Table!U3:Y35,MATCH(B7,Table!A3:A35,0),MATCH("*"&C7&"*",Table!U2:Y2,0)),IF(LEFT(D7,2)="MP",INDEX(Table!Z3:AD35,MATCH(B7,Table!A3:A35,0),MATCH("*"&C7&"*",Table!Z2:AD2,0)),IF(LEFT(D7,2)="IP",INDEX(Table!AG3:AG35,MATCH(B7,Table!A3:A35,0),MATCH("*"&C7&"*",Table!AG2:AG2,0)))))
H8=IF(LEFT(D8,2)="LP",INDEX(Table!U3:Y35,MATCH(B8,Table!A3:A35,0),MATCH("*"&C8&"*",Table!U2:Y2,0)),IF(LEFT(D8,2)="MP",INDEX(Table!Z3:AD35,MATCH(B8,Table!A3:A35,0),MATCH("*"&C8&"*",Table!Z2:AD2,0)),IF(LEFT(D8,2)="IP",INDEX(Table!AG3:AG35,MATCH(B8,Table!A3:A35,0),MATCH("*"&C8&"*",Table!AG2:AG2,0)))))
I6=IF(LEFT(D6,2)="LP",INDEX(Table!AH3:AT26,MATCH(B6,Table!A3:A35,0),MATCH("*"&C6&"*",Table!AH2:AL2,0)),IF(LEFT(D6,2)="MP",INDEX(Table!AM3:AQ35,MATCH(B6,Table!A3:A35,0),MATCH("*"&C6&"*",Table!AM2:AQ2,0)),IF(LEFT(D6,2)="IP",INDEX(Table!AR3:AT35,MATCH(B6,Table!A3:A35,0),MATCH("*"&C6&"*",Table!AR2:AT2,0)))))*G6/23
I7=IF(LEFT(D7,2)="LP",INDEX(Table!AH3:AT26,MATCH(B7,Table!A3:A35,0),MATCH("*"&C7&"*",Table!AH2:AL2,0)),IF(LEFT(D7,2)="MP",INDEX(Table!AM3:AQ35,MATCH(B7,Table!A3:A35,0),MATCH("*"&C7&"*",Table!AM2:AQ2,0)),IF(LEFT(D7,2)="IP",INDEX(Table!AR3:AT35,MATCH(B7,Table!A3:A35,0),MATCH("*"&C7&"*",Table!AR2:AT2,0)))))*G6/23
I8=IF(LEFT(D8,2)="LP",INDEX(Table!AH3:AT26,MATCH(B8,Table!A3:A35,0),MATCH("*"&C8&"*",Table!AH2:AL2,0)),IF(LEFT(D8,2)="MP",INDEX(Table!AM3:AQ35,MATCH(B8,Table!A3:A35,0),MATCH("*"&C8&"*",Table!AM2:AQ2,0)),IF(LEFT(D8,2)="IP",INDEX(Table!AR3:AT35,MATCH(B8,Table!A3:A35,0),MATCH("*"&C8&"*",Table!AR2:AT2,0)))))*G6/23


And this is where the information comes from, but what I was wondering was if there is a simpler formula I could use in columns M through to T ?

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
2DiameterDiameterSDR11SDR13.6SDR17.6SDR21SDR26SDR11SDR13.6SDR17.6SDR21SDR26LPMPIPSDR11SDR13.6SDR17.6SDR21SDR26Test Time SDR11 Hrs : mins : sec's LP = 2 barTest Time SDR13.6 Hrs : mins : sec's LP = 2 barTest Time SDR17.6 Hrs : mins : sec's LP = 2 barTest Time SDR21 Hrs : mins : sec's LP = 2 barTest Time SDR26 Hrs : mins : sec's LP = 2 barTest Time SDR11 Hrs : mins : sec's MP = 2 barTest Time SDR13.6 Hrs : mins : sec's MP = 2 barTest Time SDR17.6 Hrs : mins : sec's MP = 2 barTest Time SDR21 Hrs : mins : sec's MP = 2 barTest Time SDR26 Hrs : mins : sec's MP = 2 barTest Time SDR11 Hrs : mins : sec's IP = 7 barTest Time SDR11 Hrs : mins : sec's IP = 7 barTest Time SDR11 Hrs : mins : sec's IP = 7 barSDR11SDR13.6SDR17.6SDR21SDR26SDR11SDR13.6SDR17.6SDR21SDR26SDR11
355mm0.0550.0450000.0469120.0487500.0497620.0507690.0015910.0017290.0018670.0019450.002025FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE0:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:0000:00:0000:00:0000:00:000.00040.00050.00050.00060.00060.00140.00150.00160.00170.00180.00130.00180.0028
463mm0.0630.0515450.0537350.0558410.0570000.0581540.0020870.0022680.0024490.0025520.002656100.00 mtrs100.00 mtrs100.00 mtrs0.2087020.2268120.2449350.2552090.2656460:03:450:04:050:04:250:04:360:04:470:11:010:11:590:12:560:13:290:14:0200:10:3100:15:1500:25:430.00060.00060.00070.00080.00080.00180.00200.00220.00220.00230.00180.00230.0037
575mm0.0750.0613640.0639710.0664770.0678570.0692310.0029580.0032140.0034710.0036170.003765FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE0:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:0000:00:0000:00:0000:00:000.00090.00090.00100.00110.00110.00260.00280.00310.00320.00330.00250.00330.0052
690mm0.0900.0736360.0767650.0797730.0814290.0830770.0042590.0046290.0049990.0052080.005421FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE0:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:0000:00:0000:00:0000:00:000.00120.00140.00150.00160.00160.00370.00410.00440.00460.00480.00360.00480.0075
7110mm0.1100.0900000.0938240.0975000.0995240.1015380.0063630.0069150.0074670.0077800.008099FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE0:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:0000:00:0000:00:0000:00:000.00190.00210.00220.00230.00240.00560.00610.00660.00680.00710.00530.00710.0111
Table
Cell Formulas
RangeFormula
C3=B3-(B3/11)*2
C4=B4-(B4/11)*2
C5=B5-(B5/11)*2
C6=B6-(B6/11)*2
D3=B3-(B3/13.6)*2
D4=B4-(B4/13.6)*2
D5=B5-(B5/13.6)*2
D6=B6-(B6/13.6)*2
E3=B3-(B3/17.6)*2
E4=B4-(B4/17.6)*2
E5=B5-(B5/17.6)*2
E6=B6-(B6/17.6)*2
F3=B3-(B3/21)*2
F4=B4-(B4/21)*2
F5=B5-(B5/21)*2
F6=B6-(B6/21)*2
G3=B3-(B3/26)*2
G4=B4-(B4/26)*2
G5=B5-(B5/26)*2
G6=B6-(B6/26)*2
M3=IF((AND(Calculator!$B$6=$A3,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6,IF((AND(Calculator!$B$7=$A3,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7,IF((AND(Calculator!$B$8=$A3,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8)))
M4=IF((AND(Calculator!$B$6=$A4,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6,IF((AND(Calculator!$B$7=$A4,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7,IF((AND(Calculator!$B$8=$A4,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8)))
M5=IF((AND(Calculator!$B$6=$A5,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6,IF((AND(Calculator!$B$7=$A5,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7,IF((AND(Calculator!$B$8=$A5,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8)))
M6=IF((AND(Calculator!$B$6=$A6,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6,IF((AND(Calculator!$B$7=$A6,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7,IF((AND(Calculator!$B$8=$A6,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8)))
N3=IF((AND(Calculator!$B$6=A3,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A3,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8)))
N4=IF((AND(Calculator!$B$6=A4,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A4,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8)))
N5=IF((AND(Calculator!$B$6=A5,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A5,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8)))
N6=IF((AND(Calculator!$B$6=A6,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A6,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8)))
O3=IF((AND(Calculator!$B$6=A3,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A3,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8)))
O4=IF((AND(Calculator!$B$6=A4,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A4,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8)))
O5=IF((AND(Calculator!$B$6=A5,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A5,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8)))
O6=IF((AND(Calculator!$B$6=A6,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7,IF((AND(Calculator!$B$8=A6,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8)))
P3=IF((AND(Calculator!$B$6=A3,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*C3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*C3^2)/4,IF((AND(Calculator!$B$8=A3,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8*(3.142*C3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*C3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*C3^2)/4,IF((AND(Calculator!$B$8=A3,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8*(3.142*C3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6*(3.142*C3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7*(3.142*C3^2)/4,IF((AND(Calculator!$B$8=A3,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8*(3.142*C3^2)/4)))))))))
P4=IF((AND(Calculator!$B$6=A4,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*C4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*C4^2)/4,IF((AND(Calculator!$B$8=A4,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8*(3.142*C4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*C4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*C4^2)/4,IF((AND(Calculator!$B$8=A4,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8*(3.142*C4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6*(3.142*C4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7*(3.142*C4^2)/4,IF((AND(Calculator!$B$8=A4,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8*(3.142*C4^2)/4)))))))))
P5=IF((AND(Calculator!$B$6=A5,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*C5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*C5^2)/4,IF((AND(Calculator!$B$8=A5,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8*(3.142*C5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*C5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*C5^2)/4,IF((AND(Calculator!$B$8=A5,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8*(3.142*C5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6*(3.142*C5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7*(3.142*C5^2)/4,IF((AND(Calculator!$B$8=A5,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8*(3.142*C5^2)/4)))))))))
P6=IF((AND(Calculator!$B$6=A6,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*C6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*C6^2)/4,IF((AND(Calculator!$B$8=A6,Calculator!$D$8="LP ≤ 75mbar")),Calculator!$G$8*(3.142*C6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*C6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*C6^2)/4,IF((AND(Calculator!$B$8=A6,Calculator!$D$8="MP ≤ 2bar")),Calculator!$G$8*(3.142*C6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="IP ≤ 7bar")),Calculator!$G$6*(3.142*C6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="IP ≤ 7bar")),Calculator!$G$7*(3.142*C6^2)/4,IF((AND(Calculator!$B$8=A6,Calculator!$D$8="IP ≤ 7bar")),Calculator!$G$8*(3.142*C6^2)/4)))))))))
Q3=IF((AND(Calculator!$B$5=A3,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*D3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*D3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*D3^2)/4,IF((AND(Calculator!$B$5=A3,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*D3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*D3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*D3^2)/4))))))
Q4=IF((AND(Calculator!$B$5=A4,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*D4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*D4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*D4^2)/4,IF((AND(Calculator!$B$5=A4,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*D4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*D4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*D4^2)/4))))))
Q5=IF((AND(Calculator!$B$5=A5,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*D5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*D5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*D5^2)/4,IF((AND(Calculator!$B$5=A5,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*D5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*D5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*D5^2)/4))))))
Q6=IF((AND(Calculator!$B$5=A6,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*D6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*D6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*D6^2)/4,IF((AND(Calculator!$B$5=A6,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*D6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*D6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*D6^2)/4))))))
R3=IF((AND(Calculator!$B$5=A3,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*E3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*E3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*E3^2)/4,IF((AND(Calculator!$B$5=A3,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*E3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*E3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*E3^2)/4))))))
R4=IF((AND(Calculator!$B$5=A4,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*E4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*E4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*E4^2)/4,IF((AND(Calculator!$B$5=A4,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*E4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*E4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*E4^2)/4))))))
R5=IF((AND(Calculator!$B$5=A5,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*E5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*E5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*E5^2)/4,IF((AND(Calculator!$B$5=A5,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*E5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*E5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*E5^2)/4))))))
R6=IF((AND(Calculator!$B$5=A6,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*E6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*E6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*E6^2)/4,IF((AND(Calculator!$B$5=A6,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*E6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*E6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*E6^2)/4))))))
S3=IF((AND(Calculator!$B$5=A3,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*F3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*F3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*F3^2)/4,IF((AND(Calculator!$B$5=A3,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*F3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*F3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*F3^2)/4))))))
S4=IF((AND(Calculator!$B$5=A4,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*F4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*F4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*F4^2)/4,IF((AND(Calculator!$B$5=A4,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*F4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*F4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*F4^2)/4))))))
S5=IF((AND(Calculator!$B$5=A5,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*F5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*F5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*F5^2)/4,IF((AND(Calculator!$B$5=A5,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*F5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*F5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*F5^2)/4))))))
S6=IF((AND(Calculator!$B$5=A6,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*F6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*F6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*F6^2)/4,IF((AND(Calculator!$B$5=A6,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*F6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*F6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*F6^2)/4))))))
T3=IF((AND(Calculator!$B$5=A3,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*G3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*G3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*G3^2)/4,IF((AND(Calculator!$B$5=A3,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*G3^2)/4,IF((AND(Calculator!$B$6=A3,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*G3^2)/4,IF((AND(Calculator!$B$7=A3,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*G3^2)/4))))))
T4=IF((AND(Calculator!$B$5=A4,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*G4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*G4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*G4^2)/4,IF((AND(Calculator!$B$5=A4,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*G4^2)/4,IF((AND(Calculator!$B$6=A4,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*G4^2)/4,IF((AND(Calculator!$B$7=A4,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*G4^2)/4))))))
T5=IF((AND(Calculator!$B$5=A5,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*G5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*G5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*G5^2)/4,IF((AND(Calculator!$B$5=A5,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*G5^2)/4,IF((AND(Calculator!$B$6=A5,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*G5^2)/4,IF((AND(Calculator!$B$7=A5,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*G5^2)/4))))))
T6=IF((AND(Calculator!$B$5=A6,Calculator!$D$5="LP ≤ 75mbar")),Calculator!$G$5*(3.142*G6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="LP ≤ 75mbar")),Calculator!$G$6*(3.142*G6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="LP ≤ 75mbar")),Calculator!$G$7*(3.142*G6^2)/4,IF((AND(Calculator!$B$5=A6,Calculator!$D$5="MP ≤ 2bar")),Calculator!$G$5*(3.142*G6^2)/4,IF((AND(Calculator!$B$6=A6,Calculator!$D$6="MP ≤ 2bar")),Calculator!$G$6*(3.142*G6^2)/4,IF((AND(Calculator!$B$7=A6,Calculator!$D$7="MP ≤ 2bar")),Calculator!$G$7*(3.142*G6^2)/4))))))
U3=(P3*0.3)/24
U4=(P4*0.3)/24
U5=(P5*0.3)/24
U6=(P6*0.3)/24
V3=(Q3*0.3)/24
V4=(Q4*0.3)/24
V5=(Q5*0.3)/24
V6=(Q6*0.3)/24
W3=(R3*0.3)/24
W4=(R4*0.3)/24
W5=(R5*0.3)/24
W6=(R6*0.3)/24
X3=(S3*0.3)/24
X4=(S4*0.3)/24
X5=(S5*0.3)/24
X6=(S6*0.3)/24
Y3=(T3*0.3)/24
Y4=(T4*0.3)/24
Y5=(T5*0.3)/24
Y6=(T6*0.3)/24
Z3=(P3*0.88)/24
Z4=(P4*0.88)/24
Z5=(P5*0.88)/24
Z6=(P6*0.88)/24
AA3=(Q3*0.88)/24
AA4=(Q4*0.88)/24
AA5=(Q5*0.88)/24
AA6=(Q6*0.88)/24
AB3=(R3*0.88)/24
AB4=(R4*0.88)/24
AB5=(R5*0.88)/24
AB6=(R6*0.88)/24
AC3=(S3*0.88)/24
AC4=(S4*0.88)/24
AC5=(S5*0.88)/24
AC6=(S6*0.88)/24
AD3=(T3*0.88)/24
AD4=(T4*0.88)/24
AD5=(T5*0.88)/24
AD6=(T6*0.88)/24
AE3=(P3*0.84)/24
AE4=(P4*0.84)/24
AE5=(P5*0.84)/24
AE6=(P6*0.84)/24
AF3=(Q3*1.12)/24
AF4=(Q4*1.12)/24
AF5=(Q5*1.12)/24
AF6=(Q6*1.12)/24
AG3=(R3*1.75)/24
AG4=(R4*1.75)/24
AG5=(R5*1.75)/24
AG6=(R6*1.75)/24
 
You must log in or register to reply here.

Similar threads

D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
189
dotcanada
D
S
  • Solved
Formula in one column to calculate the sumproduct of cells between two blank cells
Replies
9
Views
284
secrestj
S
T
  • Solved
Percentage of Grand Total in Row
2
Replies
10
Views
405
RoryA
RoryA
R
  • Solved
Formula to include quantities in percentile calculation
Replies
5
Views
234
Fluff
Fluff
Sufiyan97
  • Solved
Get time More than 24 Hours correctly
Replies
7
Views
149
Sufiyan97
Sufiyan97

Share this page

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
Back
Top