Nested IF statement with multiple criteria

FM1

Board Regular
Joined
Jan 1, 2008
Messages
62
Office Version
  1. 365
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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top