Nested IF statement with multiple criteria

FM1

Board Regular
Joined
Jan 1, 2008
Messages
61
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
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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","")))))))
 
  • Like
Reactions: FM1

FM1

Board Regular
Joined
Jan 1, 2008
Messages
61
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

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
Top