Nested IF statement with multiple criteria

[FM1]

I have a spreadsheet where I'm trying to put together an IF statement which meets multiple criteria. Outline below:

Column B - Type - "Risk" or "Issue"
Column I - Probability - "1/2/3/4/5" (validated cell)
Column J - Impact - "1/2/3/4/5" (validated cell)
Column K - Rating - product of Column B and C (formula)
Column L - Severity - this is where I need the IF statement.

I need this cell to return the following where the Type (column B) = "Risk".

• If Rating >=20, then "Very High"
  If Rating >=15 and <20, then High"
  If Rating >=10 and <20, then "Medium"
  If Rating >=6 and <10 "Low"
  If Rating >= 1 and <6, then "Very Low"
  If Rating = 0, then blank

The formula I have for the above, which works is: =IF(K4>=20,"Very High",IF(K4>=15,"High",IF(K4>=10,"Medium",IF(K4>=6,"Low",IF(K4>=1,"Very Low",IF(K4=0,""))))))

If however, Type = "Issue", then it needs to return the following:

• If Rating = 5, then "Very High"
  If Rating = 4, then High"
  If Rating = 3, then "Medium"
  If Rating = 2, then "Low"
  If Rating = 1, then "Very Low"
  If Rating = 0, then blank

 

[theBardd]

How can two cells with values of 1...5 get a sum value of 20?

For Issue, what happens I the sum is greater than 5?

 

[tyija1995]

Hey,

I assume here that cell B2 takes either "Risk" or "Issue" as its value, with the rating in cell K2 and formula placed into L2 this should work:

=IF(B2="Risk",IF(K2>=20,"Very High",IF(K2>=15,"High",IF(K2>=10,"Medium",IF(K2>=6,"Low",IF(K2>=1,"Very Low",""))))),IF(B2="Issue",IF(K2=5,"Very High",IF(K2=4,"High",IF(K2=3,"Medium",IF(K2=2,"Low",IF(K2=1,"Very Low","")))))))

 

[FM1]

How can two cells with values of 1...5 get a sum value of 20?

For Issue, what happens I the sum is greater than 5?

Sorry, should have been a bit clearer. It's not a sum value, but a multiplication.

If it's an Issue then there is no sum or multiplication. I just need the single value, which corresponds to a rating, from column I.

Hey,

I assume here that cell B2 takes either "Risk" or "Issue" as its value, with the rating in cell K2 and formula placed into L2 this should work:

=IF(B2="Risk",IF(K2>=20,"Very High",IF(K2>=15,"High",IF(K2>=10,"Medium",IF(K2>=6,"Low",IF(K2>=1,"Very Low",""))))),IF(B2="Issue",IF(K2=5,"Very High",IF(K2=4,"High",IF(K2=3,"Medium",IF(K2=2,"Low",IF(K2=1,"Very Low","")))))))

Worked a treat. Thanks very much!

 

[theBardd]

Sorry, should have been a bit clearer. It's not a sum value, but a multiplication.

My bad, you did actually say that, I mis-read it.