#### Vince1199s

##### New Member

- Joined
- Mar 4, 2016

- Messages
- 22

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"))))))) |