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

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:

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

Last edited: