Vince1199s
New Member
- Joined
- Mar 4, 2016
- Messages
- 22
I am struggling with a formula, I was hoping to use nested if's however I need more than seven if statements and excel cannot process over 7. What can I do ? I tried (=IF(AND(B:B="Yes",A:A>5),"High Risk") which is fine for a two answer result so I thought what if I nest these ? (=IF(AND(B:B="Yes",A:A>5),"High Risk"),IF(AND(D:D="Yes",A:A>5),"High Risk") that didn't work out so well I got "#VALUE!" so I tried the same with IF(OR and got the same results.
Column A is how many floors in the building and columns B-E is the type of installation and below is the example of how they are split between risks Low to High.
High Risk If A>5 and B=Yes, If A>5 and D=Yes
Medium Risk If A<6 and B=Yes, If A<6 and D=Yes, If A>5 and C=Yes, IF A>5 and B=No, C=No, D=No, E=No
Low Risk If A <6 and C=Yes, If E=Yes, IF A<6 and B=No, C=No, D=No, E=No below is the sheet with the formulas I have already tried and their results, I have 7000 in total to risk score.
Column A is how many floors in the building and columns B-E is the type of installation and below is the example of how they are split between risks Low to High.
High Risk If A>5 and B=Yes, If A>5 and D=Yes
Medium Risk If A<6 and B=Yes, If A<6 and D=Yes, If A>5 and C=Yes, IF A>5 and B=No, C=No, D=No, E=No
Low Risk If A <6 and C=Yes, If E=Yes, IF A<6 and B=No, C=No, D=No, E=No below is the sheet with the formulas I have already tried and their results, I have 7000 in total to risk score.
MOB Trial.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Number of Floors | Internal Riser | External Riser | Internal Manifold | External Manifold | Trial Formula 1 | Trial Formula 2 | Trial Formula 3 | Trial Formula 4 | Trial Formula 5 | Trial Formula 6 | ||
2 | 4 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
3 | 3 | No | Yes | No | No | FALSE | #VALUE! | Low Risk | FALSE | #VALUE! | Low Risk | ||
4 | 6 | Yes | No | No | No | High Risk | #VALUE! | High Risk | High Risk | #VALUE! | High Risk | ||
5 | 3 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
6 | 3 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
7 | 3 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
8 | 3 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
9 | 4 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
10 | 5 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
11 | 4 | No | No | No | No | FALSE | #VALUE! | FALSE | FALSE | #VALUE! | FALSE | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F11 | F2 | =IF(AND(B:B="Yes",A:A>5),"High Risk") |
G2:G11 | G2 | =IF(AND(B:B="Yes",A:A>5),"High Risk"),IF(AND(D:D="Yes",A:A>5),"High Risk") |
H2:H11 | H2 | =IF(AND(B:B="Yes",A:A>5),"High Risk",IF(AND(D:D="Yes",A:A>5),"High Risk",IF(AND(D:D="Yes",A:A<6),"Medium Risk",IF(AND(B:B="Yes",A:A<6),"Medium Risk",IF(AND(C:C="Yes",A:A>5),"Medium Risk",IF(AND(C:C="Yes",A:A<6),"Low Risk",IF(AND(D:D="Yes",A:A<6),"Medium Risk"))))))) |
I2:I11 | I2 | =IF(OR(B:B="Yes",D:D="Yes",A:A>5),"High Risk") |
J2:J11 | J2 | =IF(OR(B:B="Yes",D:D="Yes",A:A>5),"High Risk"),IF(OR(B:B="Yes",D:D="Yes",A:A<6),"Medium Risk") |
K2:K11 | K2 | =IF(AND(A:A>5,B:B="yes"),"High Risk",IF(AND(A:A>5,D:D="yes"),"High Risk",IF(AND(A:A>5,C:C="yes"),"Medium Risk",IF(AND(A:A<6,B:B="yes"),"Medium Risk",IF(AND(A:A<6,D:D="yes"),"Medium Risk",IF(AND(A:A<6,C:C="yes"),"Low Risk",IF(E:E="Yes","Low Risk"))))))) |