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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You must log in or register to reply here.

Similar threads

C
  • Question
ComboBox and TextBox
Replies
0
Views
177
Cr4ZyPP
C
J
  • Solved
IFS
Replies
4
Views
134
Peter_SSs
Peter_SSs
D
  • Question
Rounding Negative time to the nearest minute in Excel
Replies
4
Views
132
Scott Huish
Scott Huish
J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
278
Fluff
Fluff
T
  • Question
Copy row values from one worksheet in one workbook to multiple workbooks based on file name
Replies
0
Views
181
thechad
T

Forum statistics

Threads
1,215,487
Messages
6,125,075
Members
449,205
Latest member
Healthydogs

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